Thursday, December 4, 2008

SQL Server Backup Fundamentals - Mirrored Backup vs Striped Backup

SQL Server Backup isn't necessarily the easiest thing in the world to do.  I've seen a few posts recently where the poster is performing a restore operation and encounters the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'ServerName'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What is really sad is that the person encountering this error is often in the midst of a disaster recovery, and unfortunately, they are learning the hard way why testing your backups early, and testing your backups often is a recommended best practice for a reason.  Unfortunately, if you find yourself with this error and this is the only backup of the database you need to restore, you are in serious trouble, because just like a RAID 0 disk, there is no way to rebuild your database without all the backup files in the media set present.  At this point, you might not believe me, and you certainly have that right, but I am presenting the cold hard truth and you can validate this by doing a Google Search or a Windows Live Search

So how exactly does this particular error come about?  For more than a few people it has been caused by SQL Server Management Studio, and confusion about the UI for backing up a database.

image

To someone new to SQL Server, this looks like it might be performing a backup to c:\Sandbox.bak and making a duplicate or mirrored copy to d:\Sandbox.bak.  In reality, this is not the case.  Instead this is performing a striped backup similar to a RAID 0 disk which will write the data round robin to all of the files listed.  Striping backups can be used to improve performance of the backup operation, especially for VLDB's using multiple drives with dedicated I/O channels to each of the drives.  The backup TSQL command from the above scripted out would be:

BACKUP DATABASE [Sandbox] 
TO DISK = N'c:\Sandbox.bak',
DISK = N'd:\Sandbox.bak'
WITH FORMAT,
NAME = N'Sandbox-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


To create a mirrored backup, you can't use the UI in SQL Server Management Studio, you actually have to use TSQL Scripts following the Book Online entry for BACKUP DATABASE.  The MIRROR TO option is used to create the mirrored backup as follows:



BACKUP DATABASE [Sandbox] 
TO DISK = N'c:\Sandbox.bak'
MIRROR TO DISK = N'd:\Sandbox.bak'
WITH FORMAT,
NAME = N'Sandbox-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


The bad thing as I stated previously in this post is that someone doesn't realize the mistake until it is to late.  When was the last time that you tested your backups?  Testing would have caught this problem well ahead of it actually being a problem. 

No comments:

Post a Comment