Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been told that it's good practise to keep your SQL Server data files and log files on physically separate disks, because it'll allow you to recover your data to the point of failure if the data drive fails.

So... let's say that mydata.mdf is on drive D:, and my mydata_log.ldf is on drive E:, and it's 16:45, and drive D: has just died horribly.

So - I have last night's full backup (mydata.bak). I have hourly transaction-log backups that will bring the data back up to 16:00... but that means I'll lose 45 minutes worth of updates.

I still have mydata_log.ldf on the E: drive, which should contain EVERY transaction that was committed right up to the point where the drive failed.

How do I go about recreating the database and restoring data from the backup file and the live transaction log, so I don't lose any updates? Is this possible?

share|improve this question
    
See msdn.microsoft.com/en-us/library/ms179314(v=sql.100).aspx for instructions on getting your tlog tail –  jl. Jan 4 '11 at 17:06

2 Answers 2

up vote 3 down vote accepted

To complete imtiaz's answer:

First thing to do is to immediately try to backup the tail-of-log. Even with a corrupted (suspect status) database you will be able to backup the tail of the log.

Example:

BACKUP LOG db_name TO DISK = 'C:\TESTS\db_name_log.trn' WITH INIT, NO_TRUNCATE;

Restore Full backup (NO RECOVERY option) + restore 16:00 Log backup (NO RECOVERY option) + restore tail backup (RECOVERY option) -> you are able to restore the database to the last point of failure.

share|improve this answer

You need to have a tail backup of the log file (depending on your version of sql). If you can backup the tail then you are set! If you can't then you will have to deal with the data loss and increase the frequency of log backups. I back mine up every 5 mins due to the business requirements.

From BooksOnline: BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.