When and how to use Database Snapshots

SQL Server 2005 offers many features that did not exist in previous versions of the product. One such feature is Database Snapshots. Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.

How does this new feature work?
The initial thought is that the snapshot needs to make a complete copy of your database so the data stays static. If this were the case, the snapshot would offer no advantage over a backup and restore.

Here's what actually happens: When you create the snapshot, a shell of your database file is created. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.

If you look at the picture directly below, 90% of the data has not been changed in the primary database, so 90% of the data is still retrieved from here when a query is issued. On the reverse, 10% of the data has changed to satisfy the results for a query that uses the data it would read from the sparse file.

What are the advantages?

• Since SQL Server only needs to deal with the data that has changed for Database Snapshots to work, the initial creation of the snapshot or subsequent snapshots is extremely fast.
• Data is only written to the sparse file as data changes, so your overall disk space needs don't double each time you create a new snapshot -- as they would if you were using backup and restore for the secondary copy.
• You can create several snapshots for the same database, so you could have rolling snapshots occurring every hour for auditing purposes.
• It is considerably easier to work with snapshots and much faster than backup and restore operations.
• you can create a static reporting environment quickly with minimal effort.
• Corrupt or deleted data can be retrieved from the snapshot to repair the primary database.
• Database changes can be audited with the use of snapshots.
• You have the ability to restore your database using a snapshot.

What are the disadvantages?

• Your user community is still hitting your primary database. If you already experience performance issues because of reporting requirements, using snapshots for reporting will not resolve that problem.
• The snapshot is tied to your primary database, so you cannot take the primary database offline. If the primary database goes offline, you won't be able to access any of your snapshots.
• The Database Snapshots feature is only available in the Enterprise Edition.
• If data changes rapidly, this option may not offer benefits for keeping snapshots around for a long period of time.
• Full-text indexing is not supported in the snapshot.

How is it used?

The following guidelines show you how simple it is to create and use database snapshots.

Creating: To create a database snapshot you must use a T-SQL command; managing snapshots is not currently supported through Management Studio. The syntax to create the snapshot is as follows:

CREATE DATABASE Northwind_Snapshot1000 ON
(
   NAME = Northwind_Data,
   FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss'
) AS SNAPSHOT OF  Northwind

If you wanted to create a snapshot every hour, you could issue the above statement again and just change the name of the database and the file. Now you will have two snapshots, but different static data.

CREATE DATABASE Northwind_Snapshot1100 ON
 (
 NAME = Northwind_Data,
 FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1100.ss'
 )
AS SNAPSHOT OF Northwind

Using: Using the snapshot is just like using any other database. You attach to the database and execute your queries. The thing to remember though is that this is a read-only copy, so you can't issue UPDATE, DELETE or INSERT statements.

Dropping: To get rid of a database snapshot, drop the snapshot just like you would drop any other database.

DROP DATABASE Northwind_Snapshot1000

Restoring: One nice thing about the snapshots feature is that you can restore your entire database back to the point in time when the snapshot was taken. If there were issues with your database and you needed to revert back to the exact time the snapshot was taken, you could use a restore command. Another option is to use snapshots to restore a test environment back to its state when the testing began. So you can take a snapshot, let your users or developers do their testing and then restore the snapshot so the database is back to the point in time prior to when testing began. To restore a database using a snapshot you can use the following command:

RESTORE DATABASE Northwind FROM DATABASE_SNAPSHOT ='Northwind_Snapshot1100'