3

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?

Dylan Beattie
  • 576
  • 2
  • 9
  • 23
  • See http://msdn.microsoft.com/en-us/library/ms179314(v=sql.100).aspx for instructions on getting your tlog tail – jl. Jan 04 '11 at 17:06

3 Answers3

3

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.

yrushka
  • 600
  • 2
  • 9
2

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.

imtiaz
  • 21
  • 1
0

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

BACKUP LOG <database_name> TO <backup_device> 
WITH NORECOVERY, NO_TRUNCATE;

Then restore by running below command 

RESTORE DATABASE <database_name> FROM <backup_device> 
WITH NORECOVERY;
Jason Clark
  • 103
  • 5