Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008

By: Ashish Kumar Mehta   |   Updated: 2009-06-24   |   Comments (3)   |   Related: > Backup

Problem

Last week, when I was performing the quarterly disaster recovery exercise, I realized that the latest Full database backup turned out to be corrupted. I wanted to know if there was a way in which I could avoid such a scenario in the future. In this tip we will look at the usage of the Mirrored Database Backup feature and RESTORE VERIFYONLY command which will help us avoid such an issue to a great extent in the future.

Solution

In SQL Server 2005 and later version you can create a mirrored backup for a database. A mirrored backup at a given time will allow you to create 2 - 4 identical copies of a database backup. The biggest advantage of using Mirrored Backup is that in case one of the backup sets or the backup file is damaged, then you can use the backup files which are available in another backup set to restore the database.

In this tip you will see how to take a Full, Differential and a Transactional Log Mirrored backup for a database. Once the database backups have completed successfully, we will be using backup files which are available in different mirrored backup sets to restore the database.

Mirrored Backup feature is available only in SQL Server 2005 Enterprise Edition and later versions. However, it is always a better option to execute RESTORE VERIFYONLY command once the backups have completed, as this command will help to verify whether the database backup set is complete and the backup is completely readable.

For example, let's assume that you have taken a Full, a Differential and a Transaction Log backups on three different mirrored sets namely Mirror Set 1, Mirror Set 2 & Mirror Set 3. Let's suppose by any chance the Full database backup in Mirror Set 1 becomes corrupted, during such a scenario you can use the Full database backup which is available either Mirror Set 2 or Mirror Set 3 to restore the Full Database backup using the NORECOVERY mode. Next, you can pick the differential database backup which is available in any of the three Mirror Sets to restore the differential database backup with the NORECOVERY mode. Finally, you can pick the transactional log backup which is available in any of the three Mirror Sets to restore the transaction log backup in RECOVERY mode.

In the BACKUP DATABASE command, the first mirror is specified using the TO clause and you can specify the rest of the mirrors using the MIRROR TO clause. However, you should be using the WITH FORMAT clause to create a new mirrored backup set, else your Full Database Backup command will fail.

Create Mirrored Database Backup

Let's go through the below example to understand in detail how to use the Mirrored Backup Set feature. In these examples I am creating three backup files and writing each type of backup to the same file.  This does not need to be done this way, but it just keeps it simpler by creating one file per mirror backup set instead of having a lot of files.  So based on this the backup files will each have three backups as follows:

• File 1 = full backup

• File 2 = differential backup

• File 3 = transaction log backup
  

The first step is to create the full backup.  Here we are creating the full backup with two mirror copies.

-- Take a Full Backup of Products Database Using WITH FORMAT clause
BACKUP DATABASE Products 
TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'
MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'
MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'
WITH FORMAT

Next we create the differential backup again with two mirror copies and this is written to the same backup files create above.

-- Take a Differential Backup of Products Database 
BACKUP DATABASE Products 
TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'
MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'
MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'
WITH DIFFERENTIAL

Lastly, we create the transaction log backup with two mirror copies and this is written to the same backup files create above.

-- Take Transactional Log Backup of Products Database 
BACKUP LOG Products 
TO DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'
MIRROR TO DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'
MIRROR TO DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

We can then run the RESTORE VERIFYONLY command to see if the backup files are readable.

-- Verify all the Mirrored database backup sets
RESTORE VERIFYONLY 
FROM DISK ='C:\DBBackup\Products_Mirror_Set_1.bak'
RESTORE VERIFYONLY 
FROM DISK ='D:\DBBackup\Products_Mirror_Set_2.bak'
RESTORE VERIFYONLY 
FROM DISK ='E:\DBBackup\Products_Mirror_Set_3.bak'

To show that you can mix and match the backups to use for the restore, I will restore the backups as follows:

• Full Backup - from Products_Mirror_Set_2
• Differential Backup - from Products_Mirror_Set_1
• Transaction Backup - from Products_Mirror_Set_3

-- Restore Full Backup of Products database using NORECOVERY Mode, 
-- the backup file used is from Mirror Set 2
RESTORE DATABASE [Products] 
FROM DISK = N'D:\DBBackup\Products_Mirror_Set_2.bak' 
WITH FILE = 1, NORECOVERY, STATS = 10
GO
-- Restore Differential Backup of Products database using NORECOVERY Mode, 
-- the backup file used is from Mirror Set 1
RESTORE DATABASE [Products] 
FROM DISK = N'C:\DBBackup\Products_Mirror_Set_1.bak' 
WITH FILE = 2, NORECOVERY, STATS = 10
GO
-- Restore Transactional Log Backup of Products database using RECOVERY Mode,
-- the backup file used is from Mirror Set 3
RESTORE LOG [Products] 
FROM DISK = N'E:\DBBackup\Products_Mirror_Set_3.bak' 
WITH FILE = 3, RECOVERY, STATS = 10
GO

As you can see you can create mirrored backup copies and then use the files from any set in order to complete the restore.

Next Steps

• Be sure to understand the database recovery models as you perform your backups, which can be accomplished by reviewing this tip Select the SQL Server database recovery model to ensure proper backups
• Learn how to take full and transaction log backups in between your regularly scheduled backups without affecting the sequence of files that would need to be restored, with the COPY_ONLY option in SQL 2005.
• If you have purchased the Enterprise Edition of SQL Server make sure you take advantage of features like this.
  

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

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

View all my tips