Database Snapshots–SQL Server

Applies to: Microsoft SQL Server 2005 Enterprise and later.
Introduction.
This article provides a summary of the information available on Microsoft SQL Server Books Online about Database Snapshots.
Definition.
A snapshot is read-only, static view of a database.
Useful scenarios.

	Snapshots can be used in conjunction with database mirroring for reporting purposes. You can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot.
	Maintaining historical data for report generation. You can create a database snapshot at the end of a given time period (such as a financial period) for later reporting.
	Safeguarding data against administrative and user error. A database can be reverted back to the state it was in when the snapshot was created. Reverting is potentially much faster for this purpose than restoring from a backup.
	In a test environment, it can be useful to quickly return a database back to the state it was prior a test run.

When not to use database snapshots.

	Database Snapshots should not be seen as high availability option.
	Database Snapshots should not be seen as an alternative to regular backups of your data. Database snapshots can not protect your databases against disk errors or database corruption.

Some important considerations about database snapshots.

	Snapshots can be created very quickly.
	Available in SQL Server 2005 Enterprise Edition and later.
	You cannot used SQL Server Management Studio for creating a database snapshots. Snapshots have to be created using T-SQL.
	You cannot make a backup of a Snapshot.
	All recovery models support database snapshots.
	Snapshots are read only, static-view of a database.
	Only users existing in the source database at the time a snapshot is created can have access to the data in the snapshot.
	Performance is degraded because Snapshot add I/O overhead. Updates to the source database imply updates to the sparse files of every snapshot the source database has.
	Snapshots depend of the source database. Snapshots must reside on the same instance as the source database, because they share data pages as well as the same buffer cache.
	A database can have multiple snapshots.
	Every database snapshot persist until explicitly dropped.
	Every time you defragment the source database all pages involved in the defragment operation should be sent to the snapshot, and that will make it grow in size.
	Full text indexes are not available in snapshots.
	Snapshots tend to fragmentation at file system level.
	Snaphots can be marked suspect if the drive hosting the snapshot runs out of space while updating the snapshot.
	Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.
	If the source database becomes unavailable, all of its database snapshots also become unavailable.
	Snapshots of the model, master, and tempdb databases are prohibited.
	You cannot drop files from a database snapshot.
	You cannot attach or detach database snapshots.
	You cannot create database snapshots on FAT32 file system or RAW partitions.

Planning for Database Snapshots.

	Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. Therefore, it is useful to know the typical update patterns for a database when planning how much space is required during the planned lifespan of a snapshot. The maximum size to which a snapshot' sparse file can grow is the size of the corresponding source database file at the time of the snapshot creation
	Create a naming convention for creating snapshots that includes the creation date and time of the snapshot. Books Online suggest that a good naming convention should also include the name of the source database and an indication that the new name is for a snapshot.
	Database snapshots can be created only on a NTFS file system.
	To use a database snapshot, clients need to know where to find it. When you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot, and that should be done with some programming.
	Reverting breaks the log backup chain. For that reason, Microsoft recommends a full database backup after finishing the revert operation.

Creating a Database Snapshot.
Here is a sample database snapshot create script on the AdventureWorks database.
CREATE DATABASE AWDB_Snapshot_20080522 ON (
NAME = AdventureWorks_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF AdventureWorks;
Open SQL Server Management Studio, and then open the Query Editor, copy/paste the above script in the Query Editor, and execute it pressing F5.


Make a right click over "Database Snapshots", and select "Refresh", to see the newly created database snapshot.



Considerations for reverting to a Database Snapshot.

	Reverting does not work in an offline or corrupted database
	Reverting does not work when the source database contains any read-only or compressed filegroups.
	Reverting does not work when any files are offline that were online when the snapshot was created.
	Only the snapshot that you are reverting can exist.
	During a revert operation, both the snapshot and the source database are unavailable.
	Before you can take log backups of the reverted database, you must first take a full database backup.
	Reverting to a snapshot drops all the full-text catalogs on the source database.

Procedure for reverting to a Database Snapshot.
1. Identify the database snapshot to which you want to revert the database using SQL Server Management Studio
2. Drop any other database snapshots.
3. Perform the revert operation. To revert the database, use the following Transact-SQL statement:
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
4. If the database owner changed since creation of the database snapshot, you may want to update the database owner of the reverted database.
5. Start the database.
6. Optionally, back up the reverted database, especially if it uses the full (or bulk-logged) recovery model.
Reverting to a Database Snapshot.
To revert the AdventureWorks database to the database snapshot we created before, use the following T-SQL command:
USE master;
-- Reverting AdventureWorks
-- to AWDB_Snapshot_20080522
RESTORE DATABASE AdventureWorks
FROM
     DATABASE_SNAPSHOT = 'AWDB_Snapshot_20080522';
GO

References.
Database Snapshots
SQL Server 2008 Books Online (October 2007)
Database Mirroring and Database Snapshots
SQL Server 2008 Books Online (October 2007)
http://www.sqlcoffee.com/Tips0004.htm
http://technet.microsoft.com/en-us/library/ms189940%28v=sql.105%29.aspx