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.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:
RESTORE DATABASE <database name> WITH RECOVERY;