Database snapshot is new in SQL Server 2005, and is available only in Enterprise Edition. Database snapshot is a static, read-only view of a source database at the time the snapshot is created, excluding all uncommitted transactions.
Snapshots should be placed on the same server instance as the source database, and that multiple snapshots can be created on a database. Though snapshots can be created and dropped without affecting the data of the source database and other snapshots, I do not recommend keeping the snapshot(s) for a long time as the performance of the system will be affected, especially on databases that require frequent updates. Also, back up and restore of snapshots are not supported, and the snapshots are gone when the source database and/or the snapshot are having problems. Therefore, when a persistent and reliable read-only copy of a source database is required, do *not* use database snapshots.
Following are some of the practical uses of database snapshots.
– To maintain historical data for report generation.
– To offload reporting when combined with mirror database.
– To safeguard data against administrative errors.
– To safeguard data against user errors.
– To manage a test database.
Creation of a database snapshot only requires a few seconds, as SQL Server uses a copy-on-write mechanism that does *not* copy the data from the source database immediately. All data is kept in the original database (source database), while a sparse file on the NTFS file system is created for holding the *old data* that have been *changed since* the creation of the snapshot. Before writing any committed transactions to the source database, the original data is copied to all snapshots, and is stored in the sparse files (and thus the term 'copy-on-write'). Upon accessing the data of a snapshot, unchanged data will be extracted from the source database, while changed data will be extracted from the sparse file.
Note that database snapshots are *not* related to snapshot backups, snapshot isolation of transactions, or snapshot replication.
===== to create a database snapshot =====
Creation of database snapshot is *not* supported in SQL Server Management Studio! To create a database snapshot, execute the following Transact-SQL.
CREATE DATABASE AdventureWorks_dbss200611022200 ON
(NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data200611022200.snapshot')
AS SNAPSHOT OF AdventureWorks;
Following are some recommendations for managing the snapshots.
– Name of the database snapshot is AdventureWorks_dbss200611022200. It is a good practice to specify the name of the source database (AdventureWorks), an indication for database snapshot (dbss), the create date (20061102) and time (2200).
– Limit the number of database snapshots as all database snapshots continue to grow when the source pages are updated.
– Give a meaningful file extension name for the physical snapshot file (e.g., *.snapshot).
===== to drop a database snapshot =====
The following Transact-SQL can be used to drop a database snapshot. All user connections to the database snapshot will be terminated. All sparse files that are used by the database snapshot will also be removed.
DROP DATABASE AdventureWorks_dbss200611022200
===== to revert a database snapshot =====
Before reverting a database snapshot, all other database snapshots of the same source database should be dropped. The following Transact-SQL can be used to revert the AdventureWorks database to the database snapshot AdventureWorks_dbss200611022200.
USE master;
— Drop AdventureWorks_dbss200611022345 if it exists.
IF EXISTS (SELECT dbid FROM sys.databases
WHERE NAME = 'AdventureWorks_dbss200611022345')
DROP DATABASE AdventureWorks_dbss200611022345;
— Reverting AdventureWorks to AdventureWorks_dbss200611022200
RESTORE DATABASE AdventureWorks
FROM DATABASE_SNAPSHOT = 'AdventureWorks_dbss200611022200';
===== limitations on the source database =====
– The source database cannot be dropped, detached, or restored.
– I/O is increased resulting from the copy-on-write operation.
– Files cannot be dropped from the source database or from any snapshots.
– The source database cannot be configured as a scalable shared database.
– The source database should be online, unless the database is a mirror database within a database mirroring session.
– To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.
===== limitations on the database snapshots =====
– Back up, restore, attach, detach, full-text indexing are not supported for database snapshots.
– Security constraints of a database snapshot inherits from the source database at the time of snapshot creation. The inherited permissions cannot be changed and permission changes made to the source database will *not* be reflected in the database snapshots.
– Database snapshots must be placed on the same server instance as the source database.
– Database snapshots works on an entire database.
– When a snapshot runs out of disk space, the snapshot becomes suspect and must be deleted.
– Snapshots are read-only.
– Snapshots cannot be created on the model, master, and tempdb databases.
– Files cannot be dropped from a database snapshot.
– Database snapshots can only be created on NTFS file systems.
– A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, while offline filegroups remain offline.
– Database snapshots depend on source database. When the source database becomes RECOVERY_PENDING, the database snapshots may become inaccessible.
– Reverting is unsupported for read-only filegroups or compressed filegroups.
– In a log shipping configuration, database snapshots can be created only on the primary database, and *not* on a secondary database. Database snapshots will need to be dropped when the roles of the primary and secondary server instances need to be switched.
– The database snapshot cannot be configured as a scalable shared database.
===== restrictions on reverting =====
– The source database should not contain any read-only or compressed filegroups.
– The online status of the files should be the same as when the snapshot was created.
– Only one snapshot can exist.
===== for more information =====
– Database Snapshots (SQL Server 2005 Books Online)
http://msdn2.microsoft.com/en-us/library/ms175158.aspx
![Martin Poon - Microsoft MVP blog [SQL Server]](https://blogs.msmvps.com/martinpoon/wp-content/themes/twentyseventeen/assets/images/header.jpg)
