Restoring Transaction Log Backups

I had a situation at work recently where I had to restore a database. There had been a full backup at 7pm the previous night and since then a transaction log backup had been taken every 15 minutes. By the time I had been alerted to the situation there were quite a few transaction log backups.

While the full was restoring I got to thinking about how to automate the transaction log backup restores.

My first thought was to use DOS to iterate through the files and send the output to a txt file.

dir /b | find "<database name>" >> outputfile.txt

Ideally I would then use SSIS to import this list into SQL Server to work with – or I could cheat and just do some cut & paste editing in notepad to create the restore statements. Another idea that occurred was to use the dir statement from within SQL Server and direct the output into a temporary table…

Then along came an idea – use the backup history. Here is the query:

select bs.database_name
	, bs.type
	, bs.backup_start_date,'Restore log <database name> from disk =''' 
		+ bmf.physical_device_name + ''' with NORECOVERY, stats=1;' as Script
from msdb.dbo.backupset bs
join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where bs.database_name = '<database name>'
and bs.backup_start_date > 'as appropriate'
and bs.backup_start_date < 'as appropriate'
order by bs.backup_start_date

This outputs a series of restore statements that can be cut and pasted into another query window. Filter as appropriate and if you are performing a simple restore follow up with:

This entry was posted in Disaster Recovery, SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s