SNAPSHOT Support

We recommend the use of the VSS framework for managing snapshot backup and restore. The following information describes how snapshot backup and restore is implemented using VDI. Some of the information is also relevant to snapshot backup and restore implemented using the VSS sqlwriter component. The sqlwriter supports database differential snapshot backup. No such support is planned for the VDI/snapshot interface. However, the sqlwriter does not yet support file and filegroup granularity restore. Refer to the article "SQL Writer in SQL Server 2005: A Guide for SQL Server Backup Application Vendors" for more information.

A snapshot of a Microsoft® SQL Server™ database is a copy of data and log files at a single point in time. The data is not transactionally consistent, but can be made so by applying the log as is done by regular RESTORE operations. A full backup will make a copy of all data and log files. 'File' backups are also supported, in which case only a subset of the data files are copied.

Snapshots may be hardware-assisted or done by software.

Motivations for snapshots, particularly hardware assisted ones, include:

• Extremely fast restores from disk backups.
  
  
• Fast creation of database copies for reporting, DBCC, or testing.
  
  
• Extremely fast backup of highly available VLDB systems with "no impact".
  
  
• Fast initialization of warm standby servers prior to log shipping.

Both full database and file snapshots are supported. Differential snapshots are not supported.

A full database snapshot captures all data and log files, and is equivalent to a full database backup. In particular, one can perform a file, file group, or partial database restore from a full database snapshot.

A file snapshot captures a subset of the data files, but does not capture the log files. The list of files captured must match the list of files and filegroups provided to the BACKUP DATABASE command. File snapshots are subject to the same operational model as conventional file backups; a complete set of file backups and a full set of conventional log backups are required for recovery.

Conventional differential database backups will be based on the most recent full database snapshot or full database backup. That is, only those extents modified since the most recent full database snapshot or conventional backup will be backed up.

Developers of backup applications will write a snapshot provider application to issue the backup and restore commands and interact with the server through VDI. This is similar to, yet simpler than, the agents developed by backup application vendors that support VDI. With snapshots, only a single "device" is allowed and vendors are only required to save the backup set meta data.

A volume is the minimum unit of which a snapshot can be taken. The snapshot may be created by any technique that makes a "near-instant" copy of the files being captured. Typically, this will involve split-mirror or copy-on-write technology.

The duration of the snapshot is the length of time between SQL Server's issuance of the snapshot command to the snapshot provider, and the return of a successful completion indication. Writes to the database files being captured are suspended for the duration of the snapshot operation. Hence, the snapshot must be completed as quickly as possible in order to avoid impact on SQL Server users.

Note   Microsoft recommends that the duration of the snapshot be limited to 10 seconds or less.

Holding a database frozen for long periods of time may result in server-wide effects. This is particularly true for SQL Server 2000. In SQL Server 2005, background operations such as the lazy writer and checkpoint processes have been improved to avoid some of this "freeze spillover" effect.

The snapshot provider should return indication of completion as soon as it can allow writes to the database while protecting the snapshot from modification. The snapshot operation must appear to SQL Server as if it completed before the provider returned success. For split-mirror snapshots, it may be that the cache can be marked appropriately without waiting for the flush of the cache to disk or the actual split. For copy-on-write technology, it is normally necessary only to set up the copy, and then materialize the snapshot on separate media later.

We use the term mount to refer to the restoration of snapshot volume(s) to the operating system. In the case of a copy-on-write snapshot, this may involve copying the volume content into place from other media. For split-mirror snapshots, this involves a reconciliation of the two mirrors, where the snapshot is the correct copy. This reconciliation is typically done in the background, resulting in an apparent "near-instant" restore.

A volume is the smallest unit that can be captured with a snapshot. Typically, a volume contains files from only one database because in SQL Server 2000 Service Pack 1 (SP1) and earlier, there is no way to freeze and back up more than a single database at one time. The freeze is done in the context of a backup command, which backs up a single database at a time. There is no way to specify that more than one database is being backed up.

However, SQL Server 2000 Service Pack 2 (SP2) includes a PrepareToFreeze capability that allows multiple databases to be frozen and captured in a single snapshot.

For more information, see Creation of a Snapshot.