Solved

Transaction log(ldf) - apply it to db in any way?

Posted on 2004-09-20
10
217 Views
Last Modified: 2012-05-05
I have full backups at 23:00 and transaction log backups every second hour during the day. All the backups are automatically restored on a second server. If the main server breaks down(the motherboard for example) I will be able to get out the ldf files but so far I can´t use them.

I cannot accept the answers I have found so far on this subject(that its is impossible to use the ldf files). Since the ldf contains all the transactions yet not written to the mdf WHY is it not possible to use that information, isn´t there any tools for this??
0
Comment
Question by:bouleIT
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I will be able to get out the ldf files but so far I can´t use them.<<
Can you translate what this means?  Is there an error, if so what is it?

What is your Recovery model?
0
 

Author Comment

by:bouleIT
Comment Utility
I use the full recovery model. On the mainserver I have a RAID 5 set for the mdf files and a mirror set with two separate disks for the ldf files.
For example, at 15:00 there is a backup of the transaction log. The log is dumped to another server and restored at that server. The next backup is at 18:00. If the main server breakes down at 17:55(in my example the motherboard or the raidset)  I will not be able to get access to the mdf files but I can remove the disk containing the ldf files, put it in another server and get access to those files. The problem is that you can´t attach a ldf file to a database on the backup server. You need a backup of the same file which I find stupid.  
0
 
LVL 1

Expert Comment

by:ccsenet
Comment Utility
Think of using transactional replication to your backup server.

Without mdf files how do you plan to attach the database. Can you please elaborate
0
 

Author Comment

by:bouleIT
Comment Utility
Transactional replication in sql 2000 makes the mainserver very slow so that is not an option.

The whole point of this solution is to both have a high security level by having the data both on backuptapes and all the "backupfiles" restored on another server, and win time if the mainserver completely brakes down. I have a backup of the mdf files once every night and backups ot the transaction logs every second hour. The same goes for the restore process meaning that the databases on the "standby" or backup server are never more than two hours old.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you need up-to-the-minute recovery, you need to use some form of replication (or perhaps clustering).

Naturally you can reduce the amount of lost data by doing more frequent log backups, so perhaps you can do them every hour or even every half hour.
0
 
LVL 1

Expert Comment

by:ccsenet
Comment Utility
Think of using Log Shipping. Once your primary server goes down, the standby server can be made as a primary server. You may need to have a third  server working as a monitor.

For more details on log shipping see SQL Books Online
0
 

Author Comment

by:bouleIT
Comment Utility
I am at the moment evaluation "Log Explorer" and so far it works. I have been able to apply the logs to the DB at the second server without having access to the primary server.
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
Comment Utility
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now