Simple Steps to Prepare Mirror Database for Mirroring in SQL Server

By: Ray Barley   |   Updated: 2012-06-06   |   Comments (6)   |   Related: > Database Mirroring

Problem

I am starting to use database mirroring to provide high availability for our critical databases.  I'm familiar with setting up database mirroring, but I'm having an issue with the initial full database backup restore and transaction log restores on the mirror server.  My problem is that it takes hours to perform a full database backup and copy the backup to the mirror server.  In addition the network guys are giving me grief because the full database backups that I'm copying are several hundred gigabytes in size.  While I'm copying the full database backup, transaction log backups are running every 15 minutes so by the time I get the full database backup restored on the mirror server, there are a whole bunch of transaction log backups that also need to be restored.  I'm looking for a simple T-SQL script that I can run to get this done.  Do you have any ideas?

Solution

To prepare a database for mirroring, you need to perform the following steps:

1. Script the restore of the latest full database backup
2. Script the restore of every transaction log backup that has been made after that full database backup 
3. Copy the full database backup and transaction log backups to the mirror server
4. Run the restore scripts on the mirror server

In this tip I will walk through these steps and provide sample scripts to prepare a database for mirroring. 

Script the Restore of Latest Full Database Backup

Use the following query to script the restore database command for the latest full database backup (replace your database name where it says DUMMY).

select
top 1 'restore database DUMMY from disk = ''' + physical_device_name + '''' + 
  ' with norecovery'
from msdb.dbo.backupset a
join msdb.dbo.backupmediafamily b
on b.media_set_id = a.media_set_id
where a.database_name = 'DUMMY'
and [type] = 'D'
order by a.backup_start_date desc

The output of the above query is:

restore database DUMMY from disk = 
'{FULL PATH}\DUMMY_backup_2012_05_28_175634_5029296.bak' with norecovery

{FULL PATH} is used as an abbreviation simply to shorten the output.

Run the above query on the principal server; i.e. the server where the database currently exists and the server that will become the principal in the initial database mirroring setup.

Script the Restore of the Transaction Log Backups

Use the following query to script the restore log commands for every transaction log backup that was done after the full database backup:

select
'restore log DUMMY from disk = ''' + physical_device_name + '''' + ' with norecovery'
from
msdb.dbo.backupset a
join msdb.dbo.backupmediafamily b
on b.media_set_id = a.media_set_id
where a.database_name = 'DUMMY'
and backup_start_date > '2012-05-28 17:56:34.000'
and [type] = 'L'
order by a.backup_finish_date

The output from the above query is:

restore log DUMMY from disk = 
  '{FULL PATH}\DUMMY_backup_2012_05_28_181236_8955078.trn' with norecovery
restore log DUMMY from disk = 
  '{FULL PATH}\DUMMY\DUMMY_backup_2012_05_28_181330_9111328.trn' with norecovery
restore log DUMMY from disk = 
  '{FULL PATH}\DUMMY_backup_2012_05_28_181955_9277343.trn' with norecovery

Given the amount of time that it takes to copy the full database backup to the mirror server, restore it, and copy / restore the transaction log backups, you may have to restore additional transaction log backups.  Simply run the above query and change the backup_start_date in the where clause as necessary. 

Copy Backups to the Mirror Server

For the sake of simplicity, I'm going to make the following assumptions:

• The database backups and the transaction log backups are in the same folder on the principal server
• The default backup folder for SQL Server is used on both the principal and the mirror and each database has its own folder
• A file share named backup exists on the mirror and points to the default backup folder; the share / folder have the necessary permissions
• Full database backups have an extension of .bak
• Transaction log backups have an extension of .trn

For copying files I like to use ROBOCOPY.  Open a command prompt on the principal server and run the following command to copy the database backups and transaction log backups to the mirror server (make sure that only the files you want to copy exist on the principal otherwise you will be copying files that you do not need):

robocopy "{FULL PATH}\Backup\DUMMY" "\\{MIRROR SERVER}\backup\DUMMY" *.bak
robocopy "{FULL PATH}\Backup\DUMMY" "\\{MIRROR SERVER}\backup\DUMMY" *.trn

Run the Restore Scripts on the Mirror Server

Run the restore scripts that were generated above to restore the full database backup and the transaction log backups.  When the restores are complete, you are ready to setup database mirroring.  Remember that due to the time elapsed you may need to restore more transaction log backups.

Next Steps

• Keep in mind that the easiest way to do something may be a query that generates the T-SQL command(s) that you need.
• Take a look at the tips on MSSQLTips.com in the Database Mirroring category for additional details.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips