In a follow-up to my previous post about using the system tables to find out information about your backups we look at how to script out restores of a large number of databases for database mirroring on a SQL Server 2008 instance. Actually, this could be used in a lot of instances where you need to restore a lot of databases.
So in this instance, I am having to restore a large number of databases for a database mirroring setup. There are full backups as well as some transaction log backups that will need to be restored. I don’t want to set up backup devices for everything because the database system is using a basic set of maintenance plans and I don’t want to set up backup devices for 50+ databases…so the backups are all datetime stamped like so
Fortunately, we have a couple of things going for us:
- The principal and mirror both have the same drive setup.
- We know that the restore for the database and log are very similar.
- All files will use NORECOVERY so we don’t have to track if the log file is the last
log file in the set or not.
So we will use the system tables to put together some code to restore our databases for us. 10 minutes of coding = > 1 Hour of manual labor. The script I used is shown below.
SELECT CASE WHEN bckset.type='D' THEN 'RESTORE DATABASE '+ bckset.database_name +' FROM DISK = ''' + physical_device_name + ''' WITH NORECOVERY GO' ELSE 'RESTORE LOG '+ bckset.database_name +' FROM DISK = ''' + physical_device_name + ''' WITH NORECOVERY GO' END FROM msdb..backupset bckset INNER JOIN msdb..backupmediafamily bckfmly ON bckset.media_set_id = bckfmly.media_set_id WHERE bckset.backup_finish_date>='7/16/2009' and bckset.database_name not in ('master','model','msdb') order by bckset.database_name,bckset.backup_finish_date
A couple of parts of the code above are important to remember for other projects you may have.
WHEN bckset.type='D' THEN
The table msdb..backupset contains a column ‘type’ that contains a character denoting for us a ‘D’ for database or an ‘L’ for log backup.
FROM DISK = ''' + physical_device_name + '''
The physical_device_name column returns the full path name of the file that was created during the backup process. So in some instances you may need to mangle it a little to get the file in the right location.
and bckset.database_name not in ('master','model','msdb')
Remember to strip out the system databases….no database mirroring on system databases!