SQL Server 2005: 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.

Here I will explain how exactly this new feature works, why you may or may not consider using it, and how to get started using it.

TABLE OF CONTENTS    How does the Database Snapshots feature work?    What are the advantages?    What are the disadvantages?    How is it used?

How does this new feature work?

Return to Table of Contents

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.

Source: SQL Server 2005 Books Online

As with any new tool or feature, there are always advantages and disadvantages. Here are a couple of key points.

What are the advantages?

Return to Table of Contents

• 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?

Return to Table of Contents

• 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?

Return to Table of Contents

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'
  

As you can see, Database Snapshots is simple to implement and use. The first time I did I was able to create a snapshot, run some queries and drop the snapshot in a couple of minutes. This is not one of those high-tech features that will take you weeks to figure out or even determine if it is something you should be using. If you haven't already tried creating and using Database Snapshots, I think you will be surprised by how easy it is. I have listed a few advantages and uses for Database Snapshots, but I am sure there are many other reasons and uses that you will find for yourself.

About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

More information from SearchSQLServer.com