Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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
-- http://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).


Comments

Posted by Alexander on 12 April 2011

It sure would be nice to have an option in the GUI where you could specify either mirrored or striped. When I first used this feature, I assumed it was creating a mirrored backup. I went to do a restore (fortunately not in a DR scenario) & found out that I had a striped backup!

Thanks for clarifying the difference!

Leave a Comment

Please register or log in to leave a comment.