Thursday, 4 December 2008

Determine Which Log To Restore

If your logshipping fails, you can usually fix it with a full database restore or even a differential, though if your database is a couple of terrabytes and not in the same building it could be problematic transferring that much data.

Rather than recreating the logshipping chain this method will demonstrate how to rectify a chain thats gone a bit bojiggity. Providing the database on the standby hasnt been recovered or corrupted, and that you still have all the log backups created - this should work.


-- get last restored log or backup for the poker database
select top 1 * from
msdb..restorehistory
where destination_database_name = 'poker'
order by 2 desc


Pay attention to the backup_set_id field, this is our reference to the actual log restore and LSN information that we need to determine the next log to restore.

-- backup_set_id is the value from the above query
select * from msdb..backupset
where backup_set_Id = 2


Here you need to look out for backup_start_date and backup_finish_date; then locate a a log backup that would have occurred after these dates. If you name the backups with the current date and time it makes locating them alot easier, otherwise you need to get the Windows file information.

Once you located it, you need to manually restore that log

restore log Poker
from disk = '\\server1\d$\Logs\LOG_Poker_20081117112135.bak'
with norecovery, stats =5


Now, if you're using the LogShipRestorer procedure mention previously, you can simply run that and it will apply any subsquent logs, otherwise you nee to apply them manually.

And there you go.