Backup and Restore in Large Mission-Critical Environments
.gif "Icon showing a blue database disk")
This topic is relevant only for databases that contain multiple filegroups under the full or bulk-load recovery model.
Mission-critical environments often require databases to be available continuously or for extended periods of time with minimal down-time for maintenance tasks. Therefore, the duration of situations that require databases to be restored must be kept as short as possible. Additionally, mission-critical databases are often large, requiring longer periods of time to back up and restore. SQL Server offers several methods for increasing the speed of backup and restore operations to minimize the effect on users during both operations.
Note
Restore is a repair feature, not an availability feature. Mission-critical databases require an availability plan. For information about high availability features, see Configuring High Availability.
The following practices will help you:
- Use multiple backup devices simultaneously to allow backups to be written to all devices at the same time. Similarly, the backup can be restored from multiple devices at the same time. For information about using backup devices in a striped media set (a stripe set) see "Remarks" in BACKUP (Transact-SQL).
- Consider using a mirrored media set. A total of four mirrors is possible per media set. For a mirrored media set, the backup operation writes to multiple groups of backup devices. Each group of backup devices makes up a single mirror in the mirrored media set. Every mirror must use the same quantity and type of physical backup devices, which must all have the same properties. For information about working with a mirrored media set, see Using Mirrored Backup Media Sets and "Remarks" in BACKUP (Transact-SQL).
- Under the full recovery model, use a combination of database, differential database, and transaction log backups to minimize the number of backups that need to be applied to bring the database to the point of failure.
- Use file and filegroup backups and transaction log backups. These allow for only those files that contain the relevant data, instead of the whole database, to be backed up or restored.
- Use snapshot backups to minimize backup and restore time. Snapshot backups are supported by third-party vendors. For more information, see Snapshot Backups.
See Also
Concepts
Performing Piecemeal Restores
Full File Backups
Differential File Backups
Optimizing Backup and Restore Performance in SQL Server
Performing File Restores (Full Recovery Model)
Performing File Restores (Simple Recovery Model)
Performing Online Restores
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|