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, for 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.

Multiple backup mirrors can be created by using T-SQL scripts or with the help of a 3rd party software like ApexSQL Backup. Mirrored backups cannot be made within SQL Server Management Studio..

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'

    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 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'

    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:

  5. 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'

    Mirroring with ApexSQL Backup

    ApexSQL Backup is a 3rd party, easy to use job manager. Among many other features, it supports backup mirroring. Mirroring with ApexSQL Backup does not require the knowledge of T-SQL script – all operations are set using graphical user interface, and can be executed with the click of a button. Furthermore, the entire backup/restore process could be automated by defining the policies or schedules. To create backup mirrors with ApexSQL Backup:

    1. Start the backup wizard by clicking the Backup button on upper left.

    2. In main tab of the wizard, select the SQL Server instance from the dropdown menu, and click browse (…) button in databases box.

    3. Check the box in front of the database that need to be backed up, and click OK button to get back to backup wizard.

    4. Specify the backup type (full, differential or transaction login) in Type drop menu.

    5. In Job name and Job description fields, automatic naming rules for the backup jobs and backup descriptions are defined. Parameters like database name, backup type, server name, date, or time may be included in the job name and description of the backup. Add more parameters for automatic naming by clicking on the browse button to the right (…), and select one or more parameters from the grid. To use custom database name/description, delete the contents from name/description fields, and enter the custom name. Click OK to return to Backup wizard

    6. Select to back up the database to disk from the drop menu. Click on Add destination button to set the first destination for backup operation.

    7. Type the file path that will be used for the original backup in Folder field. Alternatively, browse for the path with folder explorer by clicking on browse button (). Enter the custom filename for the backup in Filename field, or set the rules for automatic naming. To add the specified path to the list, click OK. To create the split backup files (striped backup), simply add multiple paths to the list.

    8. To specify the path for the mirror, select Mirror tab. Click Add mirror button to add the path to the list. Configure the filename like in the step 5. If multiple backup paths were listed in step 5 (striped backup files) under Destination tab, be sure that the number of destination paths match the number of mirror paths. Proceed to the next step when done.

    9. To automate the backup process, tick the Schedule radio button, and set the preferred backup schedule. If there is no need for the automated backups, leave the Execute immediately radio button checked, and proceed to the next tab.

    10. In Advanced tab of the backup wizard, check the Format option (It only formats media header, not the disks). If needed, set the additional options regarding backup set expiration, verification, compression and encryption.

    11. To complete the job configutration, click OK. Depending on the setting chosen in step 9, the job will either be scheduled or executed immediately. Info message is generated in both cases.

    12. All scheduled jobs can be found in Schedules tab of the main application window. Each of these jobs can be enabled, disabled, edited or deleted at any time. They can be run immediately on demand if needed. Details are available for each job. To run the created job, check the box in front of it, and click Run now.

    13. Job progress can be tracked in Progress (%) column. As soon as all operations are completed, the final result will be displayed in the nearby column

    See also


    May 6, 2016