How to create multiple SQL Server backup mirrors

One of the most important tasks for any database administrator is to create a foolproof disaster recovery plan. This plan usually includes multiple backup and restore operations. Most of the time, opting for conventional backups should suffice, but in some cases, storing all backups on a single backup device may prove to be a bad idea. As the databases grow with time, the backups become larger, and backup devices less stable due to frequent read/write operations. If the backup device fails, all of the backed up data might be lost. To avoid this scenario, some administrators take multiple copies of their backup files, and store them on different backup devices. There are a few ways to do this:

1. Create a conventional backup file and use any file manager to copy the file to preferred locations

2. Perform multiple backup operations, but change the destination path for each operation

3. Use backup mirroring – process where a database backup file is written to two or more (up to 3) different locations simultaneously.

Mirrored backups are actually identical copies of the same backup. Administrators usually store them on multiple backup devices, thus increasing the reliability of the backups, and greatly reducing the probability of data loss. Mirrored database backup feature is supported in SQL Server 2005 Enterprise Edition and all later versions. This feature supports all types of backups: full, differential, and transaction log backups. It can even be used for split backup files.

There are also some drawbacks, to using mirrored backups. Every mirrored backup requires additional disk space. This is not very convenient for large databases. Backup Compression can be used in this case (for SQL Server 2008 Enterprise Edition and later), but the use of this feature also brings its complications.

Mirroring with T-SQL

There are several scenarios when using scripts to create backup mirrors, depending on the backup type that needs to be used. The scripts that are used are similar to the conventional backup scripts. BACKUP DATABASE command is used, TO clause specifies the path for the first mirror, and MIRROR TO clause defines paths for the rest of the mirrors. When creating the first mirrored backup set, WITH FORMAT clause has to be used. The last clause should not be confused with operating system FORMAT command since it does not format the entire drive. The WITH FORMAT clause only formats the folders that are specified after TO and MIRROR TO clauses. Backup mirroring supports full, differential, transaction log, and split file (striped) backups. Before running any of the queries, be sure to create all necessary folders that are mentioned in the backup path.

1. Mirroring single file backup:

   To create the single file backup, execute the following script:

   BACKUP DATABASE AdventureWorks2014 
          TO DISK = 'C:\Mirror_01\AdventureWorks2014_Mirror_01.bak' 
   MIRROR TO DISK = 'D:\Mirror_02\AdventureWorks2014_Mirror_02.bak' 
   MIRROR TO DISK = 'E:\Mirror_03\AdventureWorks2014_Mirror_03.bak'
   WITH FORMAT
   

   Executing the query creates 3 identical backups on C: D: and E: drive in the specified folders. To restore the database from the created backup, use any of the mirrors created in the previous query with the RESTORE clause.

2. Mirroring differential backup files:

   BACKUP DATABASE AdventureWorks2014 
          TO DISK = 'C:\Mirror_01\AdventureWorks2014_Mirror_01.bak' 
   MIRROR TO DISK = 'D:\Mirror_02\AdventureWorks2014_Mirror_02.bak' 
   MIRROR TO DISK = 'E:\Mirror_03\AdventureWorks2014_Mirror_03.bak'
   WITH DIFFERENTIAL
   

   Make sure that at least one full database backup exists before executing the script.

3. Mirroring transaction log backup files:

   BACKUP LOG AdventureWorks2014
        TO DISK = 'C:\Mirror_01\AdventureWorks2014_Mirror_01.bak' 
   MIRROR TO DISK = 'D:\Mirror_02\AdventureWorks2014_Mirror_02.bak' 
   MIRROR TO DISK = 'E:\Mirror_03\AdventureWorks2014_Mirror_03.bak'
   

   Make sure that the used database is in full recovery mode before running transaction log backup query.

4. Mirroring split backup files:

To successfully create mirrors for the split backup files, multiple paths with backup filenames need to be provided. In this case, the AdventureWorks2014 database backup file gets split in 3 equal parts, that are in the same folder on the C: drive, but have different filenames. Each of the split files is then mirrored to the D: and E: drive. It is important that the number of the original backup files from the original set match the number of mirrored backup files for each mirror set. Otherwise, the query fails with the error: “Invalid backup mirror specification. All mirrors must have the same number of members.”

BACKUP DATABASE AdventureWorks2014 
TO      DISK = 'C:\Mirror_01\AdventureWorks2014_striped_Mirror_01.bak',
	DISK = 'C:\Mirror_01\AdventureWorks2014_striped_Mirror_02.bak',
	DISK = 'C:\Mirror_01\AdventureWorks2014_striped_Mirror_03.bak' 
MIRROR TO  DISK = 'D:\Mirror_02\AdventureWorks2014_striped_Mirror_01.bak',
	DISK = 'D:\Mirror_02\AdventureWorks2014_striped_Mirror_02.bak',
	DISK = 'D:\Mirror_02\AdventureWorks2014_striped_Mirror_03.bak' 
MIRROR TO  DISK = 'E:\Mirror_03\AdventureWorks2014_striped_Mirror_01.bak',
	DISK = 'E:\Mirror_03\AdventureWorks2014_striped_Mirror_02.bak',
	DISK = 'E:\Mirror_03\AdventureWorks2014_striped_Mirror_03.bak'
WITH FORMAT
GO

See also

• Database Mirroring (SQL Server)
• What is SQL Server database mirroring?
• How to create a Database Mirroring
• Mirrored Backup and Restore and Split File Backup – Introduction

 

Related posts:

1. How to create a simple database recovery solution using SQL Server log shipping
2. Manage multiple database backups across different SQL Server instances
3. How to perform a SQL backup and restore history cleanup
4. How to automate DBCC CHECKDB after a database restore
5. Manage and monitor SQL Server backups from a central location
May 6, 2016