|Snapshots can be created very quickly.
|Available in SQL Server 2005 Enterprise Edition
|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
|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
|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 tend to fragmentation at file system
|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
|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.
|You cannot restore a backup on a database that
has a snapshot created.
|A SQL Server instance that has a snapshot
created, cannot be upgraded to a new version of SQL Server.
|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
|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
|Reverting breaks the log backup chain. For that reason,
Microsoft recommends a full database backup after finishing the revert
Creating a Database Snapshot.
Here is a sample database snapshot create script on the AdventureWorks database.
NAME = AdventureWorks_Data,
FILENAME = 'C:\Program Files\Microsoft SQL
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
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:
-- Reverting AdventureWorks
-- to AWDB_Snapshot_20080522
SQL Server 2008 Books Online (October 2007)
Database Mirroring and Database Snapshots
SQL Server 2008 Books Online (October 2007)