Complete Guide on Database Snapshot (SQL 2005/2008)

Most of us heard about this new cool feature (database snapshot) from SQL 2005 Enterprise Edition onwards and some of us also use it also in daily life. Here I am going to discuss about some important dimensions of this technology.


Snapshot is actually a read-only static copy of a source database as it existed at snapshot creation, minus any uncommitted transactions. You may take multiple snapshot of a database and provide them to your customers for their easy access. Creating a snapshot is extremely fast and handy but it will take almost the same amount of space taken by data file of the source database. Couple of points you should be aware of (and possibly let your customers know) about using this technology are as follows:

1. Snapshot is a read only copy of the source database. So no one (even a DBA) can not do any changes on a database snapshot. Also snapshot must reside on the same server where source database resides.
2. Snapshot itself can not be backed up or restored. So if you delete a snapshot, it is a permanent deletion.
3. Snapshot can be used to restore the source database to the point when snapshot was taken. However there are limitations using this technology. (For example, you have to delete all other snapshot/s of the database before restoring). Also restoring from a snapshot breaks the backup sequence/replication etc of source database.
4. If the source database becomes unavailable for any reason then the snapshots will also become unavailable. So never consider a snapshot as a “Backup” of source database.
5. Though initial disc taken by a database snapshot is very low (128KB by default), it can eventually grow up to the size of source database. So it is best practice to refresh database snapshot.

From SQL Server Architecture point of view snapshot technology actually uses the concept of one or more sparse file. When you take a database snapshot, under the hood SQL Server performs below steps:

1. SQL Server creates sparse files for the snapshot and allocates minimum space for them. However SQL Server notes the actual size of corresponding data file of source and notes it in the file so that provision of growth is maintained.
2. SQL Server rollbacks any uncommitted transaction in the newly created snapshot and then brings snapshot online.

At this point customers start using snapshot. When ever they run a query on the snapshot, SQL Server actually refers to the original source database’s data page only if there is no change on the source database’s page. If there is a change in the data page of the source databases after the snapshot creation time, SQL Server copies the original data file to corresponding sparse file during the data modification time. So if a customer runs a query on the snapshot where they refer to a data page which is changed at source after the snapshot is created, they actually read the data from sparse file. If you want to know about this internal architecture in more details, please refer to http://msdn.microsoft.com/en-us/library/ms187054.aspx

However this architecture concludes the need to refresh database snapshot periodically for a database which changes it’s data often. Otherwise the snapshot file’s size (sparse file) can grow up to the size of source database’s data file size (as it was while taking the snapshot).

Snapshot technology is recommended to be considered to be used in following scenarios:

1. Snapshot can be used for reporting purpose. We can create snapshot of a database at a particular time and then reports can be generated from that read-only snapshot with out directly accessing the source database. Also snapshot may be considered as a mirror of the source when a static data up to a certain point of time is acceptable for use. In this way, customers who does not need to access live data can refer to snapshot and offload the overhead on source.
2. Snapshot can be used as a “Point of recovery” before doing any critical operations on data/definition on the database. For example, before a bulk insert of data, you may consider to take a snapshot of the database. If anything goes wrong during the bulk insert and you need to revert back the database to the point just before the bulk insert started, you can easily perform this operation by running a restore from snapshot. Best of all, restoring database from a snapshot will not be only easy but also considerably fast compared to conventional resotre from backups.

I strongly recommend using snapshot before doing anything critical on a database where you suspect the database may get corrupted and you may end up undoing the change by rolling back/reverting the database. So in other words, snapshot can be considered a very powerful new generation “Rollback Procedure”
However, you need to know below facts about reverting a database from its snapshot:

1. Reverting from the snapshot will not work under following condition:
a. If source database contains any read-only or compressed file group
b. If any file of the source is offline which was online while the snapshot was created
c. More than one snapshot of the source exists. (In this case you have to delete all other snapshots before starting the restore)
2. Both source and snapshot will be inaccessible during the restore operation. They will be in recovery mode.
3. A successful restore will rebuild the log of source. So consider taking a final log backup of source before starting the restore from snapshot.
4. After the restore, backup chain will be broken for the source database. So take a full backup to ensure that you have good backup of source after the reverting.
Lastly please be aware that snapshot technology is no way meant to replace traditional backup/restore and must not be considered as a disaster recovery solution. In case of a failure which makes the source database unavailable or not recoverable, all the snapshots of that database will also be unusable.

Now couple of direct answers to implement this technology:

How to create a database snapshot?
How to restore a database from a snapshot?
How to drop a database snapshot?
How to create a script to create database snapshot automatically using TSQL?