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.
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
NAME = MirrorMediaTest_Data,
FILENAME = 'C:\MirrorMediaTest_Data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
NAME = MirrorMediaTest_Log,
FILENAME = 'C:\MirrorMediaTest_Log.mdf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
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'
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
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.