This is a followup to my Whats more important than a backup plan article posted a couple of months ago.
When you do a database restore, including log backups, it is very important to get the log chain in the right order, or it won’t work. To restore a full backup, then log backups, the chain of logs could be hard to sort out if you have dozens or hundreds of log backups to restore. Your sure don’t want to be hand coding these.
The script below shows how to find the last full backup, then get a list of all of the log backups that have occurred since that last full backup. To do the restore, you should just follow the order of the output of this script.
Sample Code
DECLARE @dbName VARCHAR(1024);
-- be sure to put your database name on the following line.
SET @dbName = 'YourDatabaseName';DECLARE @bfd DATETIME;
DECLARE @fullbackup_file VARCHAR(2048);SELECT TOP 1
@fullbackup_file = m.physical_device_name,
@bfd = b.backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND TYPE = ‘D’ — type D = full database backup
ORDER BY backup_set_id DESC;
SELECT @fullbackup_file AS backup_file_name,
@bfd AS backup_finish_date
UNION
SELECT m.physical_device_name AS backup_file_name,
b.backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m
ON b.media_set_id = m.media_set_id
WHERE database_name = @dbName
AND backup_finish_date > @bfd
AND TYPE = ‘L’ — type L = log backup
ORDER BY backup_finish_date ASC;
Donwload the sample code ShowRestoreChain.
The output will look something like this…
Once you have the list of files that need to be restored you can then just format it into a sql script to do the full restore.
Keep in mind that if your database has just failed, you are not going to have access to this information, so I suggest that you script this and save it out as a sql script in the same directory as your backup files so that it can be used for the restore. This process will be covered in a later posting.
You can also easily get this through the backup report in the new Database Health Reports project.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!