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?


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.


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.


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.


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.


first you need to create a tail log backup (to prevent data loss) by running below command

BACKUP LOG <database_name> TO <backup_device> 

Then restore by running below command

RESTORE DATABASE <database_name> FROM <backup_device> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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