Simple Steps to Prepare Mirror Database for Mirroring in SQL Server

By:   |   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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley 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


Comments For This Article




Thursday, June 4, 2020 - 8:24:52 AM - Ray Barley Back To Top (85838)

There is no answer I can provide to the question of how to figure out a budget for mirroring. However, here is a link to a Microsoft support article that walks throught the things you need to consider: https://support.microsoft.com/en-us/help/2001270/things-to-consider-when-setting-up-database-mirroring-in-sql-server. 

If you are on a verion of SQL Server that supports Availability Groups, you should decide if that's a better fit. Here are some details on the considerations regarding Availability Groups: https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver15

Wednesday, June 3, 2020 - 9:56:09 PM - laxmi Back To Top (85835)

how can i plan for budget for data mirrioring server

Wednesday, June 20, 2012 - 2:19:41 PM - Ray Barley Back To Top (18136)

You are correct about the number of files except if it takes 3 days to get the full database backup onto the mirror

Wednesday, June 20, 2012 - 1:11:26 PM - Arni Petursson Back To Top (18134)

Generally you should only have to restore from three to four  files.  A full backup, a differential, and log backup (or two), the diff adn the log backups taken right before you set up the mirror.

However the script is nice.  A small improvement would be to get the max backup_finish_date of the full backup and assign it to a variable.  That way you do not need to hard code it when you get the log backups.  Also providing examples with top 1 and order by teaches newbies bad habits, because these can do a lot of scanning and expensive sorts.

Wednesday, June 20, 2012 - 10:44:30 AM - Robert L Davis Back To Top (18130)

You should always use robocopy or some 3rd party file copy app. The standard Windows copy will thrash memory and can cause memory pressure on the server. Robocopy still uses memory a fair amount, but in a much smarter way and doesn't cause severe memory issues. Still, I would recommend running the copy on the mirror server (unless it has live databases as well) to move teh effect on memory to the mirror server.

This is a good script even if you're not doing mirroring, though I would like to see it also script the differential backup as part of the restore.

Wednesday, June 20, 2012 - 7:51:04 AM - Vinicio Aizpurua Back To Top (18119)

Nicely explained. Amazingly I also like to use ROBOCOPY and It seems is not used or known by some Sys Admin/Net Admin.

Thanks for the posting.














get free sql tips
agree to terms