Mirrored backup and restore commands in SQL Server 2005

Mirrored backups are made to an existing media set, or media "family," that has already been mirrored. Mirrored media must also be consistent. If you have a tape backup device, for instance, you can only mirror it to another instance of the exact same make and model of that tape backup device. This insures consistency between the mirrors right down to the sequence of bytes written to the tape; and each mirror in the backup is interchangeable with every other mirror. Up to four mirrored backup sets can be created for any one backup.

When you run the BACKUP DATABASE command, the mirrors are specified using the TO and MIRROR TO clauses. The TO clause contains the first mirror, and the MIRROR TO clauses contain each subsequent mirror. The backup operation must be able to find and write to all of these media sets and families successfully, or it will return an error, since the mirror will be essentially incomplete.

The restore operation is much more forgiving, however, and this is the reason why backup mirroring is useful. When you want to restore from a mirrored media set, you only need a single instance of a given media set to restore everything. That single instance can be culled from multiple media families if needed.

For example, let's say you have two media families, A and B, and each has two tape drives. You've created a mirrored backup across all four tape drives, and now you want to restore them. Unfo

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery The keys to database backup protection for SQL Server Choosing a SQL Server disaster recovery solution Licensing a standby server for SQL Server replication Can I encrypt and restore a database backup in SQL Server 2005? SQL Server errors, failures and other problems fixed from the trenches Get SQL Server log shipping functionality without Enterprise Edition SQL Server 2008 backup compression pros and cons SQL Server backups using SAN database snapshots Tips for scheduling and testing SQL Server backups Code to restore SQL Server databases in VB.NET SQL Server Backup and Recovery Research Microsoft SQL Server 2005 (Yukon) SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 How to configure Database Mail in SQL Server 2005 to send mail Microsoft SQL Server 2005 (Yukon) Research SQL Server Management A first look at Microsoft SQL Server 2008 R2 Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Using Microsoft Hyper-V for SQL Server consolidation Scaling up vs. scaling out with SQL Server 2008 Migrating to SQL Server 2008 and leveraging new features Testing a SQL Server environment before an upgrade Does upgrading to SQL Server 2008 fit your business? Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary rollback  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

rtunately, you've discovered that one of the tapes has gone missing -- the first tape in family A was left in a briefcase on a train somewhere. Thank goodness for SQL Server encryption! Since this is a mirrored backup set, you can use the tapes from family B to restore everything. You can even mix and match them: If you lost one tape from each family, you could reassemble a composite backup and restore from that as long as you're not missing all of the tapes from any given family.

Don't forget the backup hardware. For the devices you're backing up to support firmware upgrades, always make sure they are all running the same level or revision of firmware. Inconsistencies between firmware revisions could create problems that might not be immediately obvious, but could surface later during a backup or recovery operation. This is generally more important for tape than disk. If SQL Server detects a mismatch between the devices you're using to perform the mirroring, it'll warn you, so you can always try staging a test backup on the hardware you have in question and see what happens. (Also be sure to stage a restore operation or at least run RESTORE VERIFYONLY to determine if the backup did indeed work when you first set this whole thing up.)

Mirrored tape and disk backups have many of the same limitations and behaviors as their non-mirrored counterparts. For one, you can't use continuation media on disks, only tapes. This means, if you fill a given disk with a backup, then you can't point to another disk during the backup operation and tell SQL Server to continue the backup there. Obviously, your choice of tape or disk when performing mirrored backups is going to depend on what sort of backup infrastructure you have. But, if you want to start using mirroring as part of your backup strategy, you'll have the excuse you need to reassess what you have and how it's being used.

[TABLE]

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.