Mirrored Database Backups vs. Striped Database Backups

I have seen a few people get confused about the difference between a mirrored database backup (which is only available in SQL Server 2005 Enterprise Edition and above) and a striped database backup.

A Mirrored Backup is simply a database backup where two copies of the database backup file are simultaneously written to two different locations. This gives you an extra measure of safety and redundancy in case one of your database backup files is deleted or is otherwise unusable. It is different from a striped backup, where you split a single backup file into multiple files that are simultaneously written to different locations, usually using different backup paths, in order to increase backup performance. A mirrored backup is conceptually similar to using RAID 1 (since you have two complete copies of the file in two different locations) to improve redundancy, while a striped backup is conceptually similar to using RAID 0, where a single backup file is spread across multiple locations to improve performance. The difference between the two backup methods is shown in Listing 1.

-- Glenn Berry
-- https://sqlserverperformance.wordpress.com
-- Twitter: GlennAlanBerry

-- Mirrored full backup with compression (Enterprise Edition)
BACKUP DATABASE [TestDB] 
TO DISK = N'C:\SQLBackups\TestDBFull.bak' 
MIRROR TO DISK = N'D:\SQLBackups\TestDBFull.bak' WITH FORMAT, INIT,  
NAME = N'TestDB-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,COMPRESSION, STATS = 1;

-- Striped full backup with compression
BACKUP DATABASE [TestDB] 
TO  DISK = N'C:\SQLBackups\TestDBFullA.bak',  
DISK = N'D:\SQLBackups\TestDBFullB.bak' WITH NOFORMAT, INIT,  
NAME = N'TestDB-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 1;

Listing 1: Mirrored and Striped Full, Compressed Backup commands

You cannot use the Back Up Database dialog to do a mirrored backup. What you see below is how you do a striped database backup, not a mirrored backup. The striped backup that is showing in the dialog below will create two backup files, both of which are needed to restore the database.

image

A mirrored database backup will create two copies of the same database backup file, which could save you if something happens to one of them. Don’t confuse the two!

Note: the code in Listing 1 will only work on SQL Server 2008 and above (because I am using backup compression). The mirrored backup command will only work on SQL Server 2008 or above Enterprise Edition (or Developer or Evaluation Edition).

This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and tagged . Bookmark the permalink.

3 Responses to Mirrored Database Backups vs. Striped Database Backups

  1. John Danley says:

    I’ve tried the striped backup in the past and it dramatically shortened backup time. However it concerned me having two files that had to be available for recovery so I stopped using it.

    I like the idea of a mirrored backup. It seems to me to be best if the two destinations can be written at around the same speed. However if one is significantly slower than the other the faster backup must wait for pages to be written so that both backups are identical.

    In the case of the mirrored backups what happens if one backup fails? Particularly, will backup A complete if the destination file for backup B goes offline? If the destinations have disparity in write speed – say SAN vs NAS – Would it be better to complete the backup to faster disk first then copy that to the slower destination?

    • Glenn Berry says:

      There is some risk involved with striped backups. There is the risk of human error, and the risk that something happens to one of the backup files. You can actually have up to three mirror sets in a backup. It is also fairly common to just do a regular backup, and then copy it somewhere else for greater protection.

  2. Pingback: Something for the Weekend – SQL Server Links 18/03/11 | John Sansom - SQL Server DBA in the UK

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s