Snapshot Backups

SQL Server 2005 supports snapshot backup and restore technologies (SQL Server snapshot backup) together with independent hardware and software vendors. Snapshot backups minimize or eliminate the use of SQL Server 2005 resources to accomplish the backup. This is especially useful for moderate to very large databases in which availability is very important.

Snapshot backups have the following primary benefits:

  • A backup can be created quickly, typically measured in seconds, with little or no affect on the server.
  • A restore operation can be accomplished from a disk backup just as quickly.
  • Backup to tape can be accomplished by another host without an effect on the production system.
  • A copy of a production database can be created instantly for reporting or testing.

SQL Server snapshot backup is accomplished in cooperation with third-party hardware or software vendors, or both. These vendors use SQL Server 2005 features that are designed for this purpose. The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

SQL Server snapshot backup is also used by Microsoft Windows Server 2003 Volume Shadow Copy Service (VSS) and by all the backup software and storage software that uses this framework.

Only the following types of backups can be snapshot backups:

  • Full backups
  • Partial backups
  • File backups
  • Differential database backups. These are supported only when the vendor uses the VSS interface.

Except as noted earlier in this topic, snapshot backups are functionally equivalent to the corresponding conventional backups. You can use snapshot backups in restore sequences with non-snapshot full backups, differential backups, and log backups. Like other backups, snapshot backups are tracked in the msdb database, where snapshot backups are identified by backupset.is_snapshot = 1. For more information about msdb, see msdb Database.

SQL Server does not support online restore from a snapshot backup. Restoring a snapshot backup automatically takes the database offline. A piecemeal restore can incorporate snapshot backups, but all the restore sequences are offline restores. For more information about piecemeal restores, see Performing Piecemeal Restores.

For more information, see the SQL Server Web site. Also, you can contact your enterprise storage or backup software vendor.

Community Additions