• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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

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
bouleIT
Asked:
bouleIT
1 Solution
 
Anthony PerkinsCommented:
>>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
 
bouleITAuthor Commented:
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
 
ccsenetCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
bouleITAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
ccsenetCommented:
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
 
bouleITAuthor Commented:
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
 
ee_ai_constructCommented:
Question answered by asker or dialog valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now