Sunday, 6 February 2011

Salvage The Tail End Of The Log

In the event of disaster, the first thing that any experienced DBA will do, is try to back-up the tail-end of the log, in an attempt to salvage DML operations that have been made since the last log backup, but what if the server is inaccessible?
Well, the fix is "dirty" but effective. You basically need to "fudge" the log onto another server, in order to perform the backup.

First, on a second instance or server, create a shell database with the same name as the original. In this case...


...Now, set this database to be offline...


...Next, delete the data files and the log file that exists for the database on the second server. Now, Copy the log file from the original server, to the folder that the new database's file were in (before you deleted them).

If you now try to bring the database back on-line, you will receieve an error (obviously, considering you have no data files and an incorrect log file!), but you will be able to perform a tail-log backup, with the following syntax...

BACKUP LOG FudgeLog TO DISK = 'D:\SQLServerDownAndDirty\FudgeLog\FudgeLog_Log.bck' WITH INIT, NO_TRUNCATE

...You can now restore this backup at the end of your log chain, on your original server. You will loose no data, keep your job, and sleep happily at night! ;-)

Find my book, Pro SQL Server Administration on Amazon -


United Kingdom

No comments:

Post a Comment