Data Centers

Using Mirrored Media Sets in SQL Server 2005

Mirrored Media Sets is a new feature in the Enterprise Edition of SQL Server 2005. It enables the creation of redundant copies of a backup file to reduce the possibility of a backup device malfunction. Here's how you make it work.

Mirrored Media Sets is a new feature in the Enterprise Edition of SQL Server 2005. It enables the creation of redundant copies of a backup file to reduce the possibility of a backup device malfunction. Here's how you make it work.

—————————————————————————-

Normal0falsefalsefalseEN-USX-NONEX-NONEMicrosoftInternetExplorer4

A media set is a set of one or more database backups to one or more disk files or tape-units.  A media set uses either tape or disk backups, but not both.  In the Enterprise Edition of SQL Server 2005, Mirrored Media Sets can be used to protect against damaged media during the backup process.  With Mirrored Sets, you have the option of placing your backups on a disk file on the operating system along with placing a mirror copy of the backup on tape in the same operation. 

 

Creating a Mirrored Media Set

Let's take a look at how mirrored media sets can be used.  First, I'll need to create a database to which to backup.  The script below creates a database named MirrorMediaTest on the C drive of your workstation.

 

CREATE DATABASE MirrorMediaTest 

ON

(

NAME = MirrorMediaTest_Data, 

FILENAME = 'C:\MirrorMediaTest_Data.mdf', 

SIZE = 10, 

MAXSIZE = 50, 

FILEGROWTH = 5 

) 

LOG ON 

(

NAME = MirrorMediaTest_Log, 

FILENAME = 'C:\MirrorMediaTest_Log.mdf', 

SIZE = 5MB, 

MAXSIZE = 25MB, 

FILEGROWTH = 5MB 

) 

GO

 

Once you have the database created, you are free to make a backup of the database.  In the code example below, we'll use the MIRROR TO option with the FORMAT option to create a redundant copy of the database backup to G drive (previously mapped to a network location) along with the initial backup being place on the C drive.  The FORMAT option indicates that a new media set is created in the Backup operation. 

 

In this example, we'll place both backups to local disk, but you can swap out the MIRROR TO option to place the backup file on a TAPE backup just as easily.  The ability to place one backup on disk and one on a tape backup makes this a very valuable backup option to have. 

 

BACKUP DATABASE MirrorMediaTest 

TO DISK = 'C:\MirrorMediaTest.bak' 

MIRROR TO DISK = 'G:\MirrorMediaTest.bak' 

WITH

INIT, FORMAT, STATS = 5

 

As you can see from the short code snippet above, it is easy to take advantage of the new Mirrored Media Set feature in SQL Server 2005.  The only drawback of this feature is that you must have the Enterprise version of SQL Server 2005.  The ability to create multiple copies of a backup file to separate locations in the same operation not only adds some peace of mind to the database administrator, but more importantly may save you some day. 

About Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox