SQL Database Snapshots for Reporting, Auditing and Data Recovery

By:   |   Updated: 2006-06-30   |   Comments (1)   |   Related: More > Database Administration

Sometimes there is need to have a static set of data from your database.  The need may be for reporting, auditing or even recovery of data.  This can be pretty easily done using SQL Server backups and restores, but the time that it takes to perform the backup and restore task can be quite long depending on the size of the database.  If the need is to only periodically create a static set of data, then this approach will probably work for you.  But what if you need to create a static set of data every day or if you need to do it multiple times a day for some type of reporting or auditing.  To handle this approach you could do full and transaction log backups and restores, but there is still a lot of overhead to run these processes.

With SQL Server 2005 a new feature has been introduced called database snapshots.  A database snapshot is a read only copy of your database, but not exactly a copy. If it were a complete copy, then it would be the same as doing a backup and restore and offer no advantage.  The way database snapshots work is that a shell of your database file is created instead of a duplicate set of data. When data is read from the snapshot, it is actually being read from the primary database files. 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, so this way the data stays static. 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. Since the initial snapshot is reading from your primary database, the creation of a database snapshot is immediate.

The image below shows when data changes in the primary file a copy of the original data is moved to a sparse file. So when a SELECT is issued against the snapshot database part of the data is read from the primary data file and the changed data is read from the sparse file, so a static set of data is retrieved based on when the snapshot was taken.

Source: SQL Server 2005 Books Online

As time goes by, the more the data changes in the primary database, the more data there will be in the sparse files.  So you can see if the data is changing quite frequently in your primary database the data in the sparse files can get very large.  So understanding how your data changes in your database is extremely helpful as well as how long you keep snapshots of your database.

Source: SQL Server 2005 Books Online

Creating and using database snapshots is very straightforward. To create a database snapshot you use a T-SQL command such as the following:

ON (NAME = Northwind_Data,
FILENAME = 'C:\MSSQL\Snapshot\Northwind_Snap_data.ss' )

Using the snapshot is just like using any other database. You connect to the database and execute your SELECT queries. The thing to remember is that this is a read-only copy, so you can not do updates.  Getting rid of a snapshot is just as easy, you can issue a DROP DATABASE command or delete the database snapshot using SQL Server Management Studio.

In addition, you can have multiple snapshots of your database.  Each time a snapshot is taken you create a static set of data as it looked at that point in time. One big disadvantage of database snapshots is that this feature is only available in the Enterprise and Developer Editions of SQL Server 2005.

Next Steps

  • Download the trial edition of SQL Server 2005 and get familiar with this new feature
  • If you have a need for creating a static set of data for reporting, auditing or data recovery take a look at using database snapshots
  • See if it makes sense to purchase the Enterprise Edition of SQL Server in order to use database snapshots
  • Run some tests to make sure that performance does not suffer by using database snapshots

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Article Last Updated: 2006-06-30

Comments For This Article

Tuesday, February 10, 2009 - 11:38:53 PM - ashok Back To Top (2742)

Can't the process be dynamic?

Mean to say  that can't the snapshot of data taken be automatically updated when data in the originol database changes(in meagre quantity).

And if not is it wise to change the snapshot every 1 hour to aproach (Near dynamism) of data?

Kindly Help...



get free sql tips
agree to terms