Restoring Databases for Database Mirroring

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

ProgrammersEdge_backup_2009_07_16_000001_8350803.bak

Fortunately, we have a couple of things going for us:

1. The principal and mirror both have the same drive setup.
2. We know that the restore for the database and log are very similar.
3. 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!

Cheers,

AJ