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 m...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery SQL Server Mailbag: Data restoration and DB property management Achieving high availability and disaster recovery with SharePoint databases How to 'do' SQL Server disaster 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 Backup and Recovery Research Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 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 Microsoft SQL Server 2005 Research Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V

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

irrored backup across all four tape drives, and now you want to restore them. Unfortunately, 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.

ABOUT THE AUTHOR:   

Serdar Yegulalp has been writing about Windows and related technologies for more than 10 years and is a frequent contributor to various sections of TechTarget as well as other publications. He hosts the Web site Windows Insight, where he posts regularly about Windows and has an ongoing feature guide to Windows Vista for emigrants from Windows XP.
Copyright 2007 TechTarget

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.