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 |
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 |
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 |
We can then run the RESTORE VERIFYONLY command to see if the backup files are readable.
— Verify all the Mirrored database backup sets |
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, |
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.
