This month I want to explain a Database Snapshot and how using it can save you time. Here is the scenario. Say you have a development environment. Lets also say you are testing new modifications. You make updates(Inserts,Updates, Deletes)to the database. After you have completed your test, you want to revert the database back to the point in time before you made the update. You can certainly use a conventional backup file and restore it. Depending on the size the database, it could take 30 minutes to an hour or more.  When you revert the database back to its original state using a Database Snapshot, it is very fast. How fast? I have restored an 80 gig database from a snapshot in less than 10 seconds!

What the heck is a database snapshot anyway?  SQL Books OnLine(BOL) has a lot more detail about Database Snapshots than I intend to cover. So if this topic is of interest to you, BOL is a great first place to start. By definition a snapshot is a readonly static or 'snapshot' of a source database. Once a Database Snapshot is in place  when you make changes in the source database and before the change completes, the original data is copied to the snapshot and is stored there in what are called sparse files.  When you revert the Database Snapshot back to your source database, it restores the original bits of data only. (Much less data than the conventional database restore!)

Pros and Cons

• The Database Snapshot must reside on the same server\instance as the source database. 
• The restore time from snapshot is significantly less than doing an actual database restore from a backup file
• Performance might be increased slightly on the server instance as changes made to source affect the snapshot
• The Database Snapshot feature is not available in all editions of SQL Server. Check Microsoft's website for the SQL Server version you are running to make sure it's capable to use this feature.

 How is a Database Snapshot set up?

Database Snapshots are created using Transact SQL(T-SQL) commands. I will show you a simple example of how to create the Database Snapshot and how to revert it back to the source database.

How to create the Database Snapshot

1. Login to SQL Server Management Studio using sa or equivalent user and switch the context to the source database from which you will create a Database Snapshot.

The syntax is as follows:

CREATE DATABASE <Snapshotname> ON (NAME= logical file name of the source database. Right click the source database, select Properties, select Files, locate the logical file name of the data file type)

FILENAME= physical file name and location of the Database Snapshot you are creating

AS SNAPSHOT OF <Source Databasename> This is the source database name from SQL Server Management Studio, after expanding the Databases folder

Example:

CREATE DATABASE Sage2013_snap ON ( NAME = mas500_app,  FILENAME = 'D:\databases\Sage500_2014_snap.ss' )  AS SNAPSHOT OF sage500_app

This will create a new Database Snapshot named Sage2013_snap copied from source database named Sage500_app, with logical file named mas500_app.  The file name associated to my new snapshot is 'D:\databases\Sage500_2014_snap.ss'.  From SQL Server Management Studio, if you refresh the Database Snapshots node, you will see your new snapshot

 How to revert the source database from the Database Snapshot

1. While still in SQL Server Management Studio, use the following query:(Note the USE MASTER statement). First the syntax which is much simpler than the CREATE DATABASE code 

RESTORE DATABASE <databasename> Name of the database to which you are restoring(the source)

FROM DATABASE_SNAPSHOT ='snapshotname'  Name of the snapshot from which you are restoring.

Example:

USE MASTER GO

RESTORE DATABASE Sage500_app FROM DATABASE_SNAPSHOT='Sage2013_snap'

 This will restore the Database Snapshot named Sage2013_snap back to Sage500_app.  Thats it!! Thats all there is to it! When you execute the RESTORE command take note of how long it takes to complete, you will be surprised.

Other Notes

• Running a Sage 500 upgrade against a database that has a Database Snapshot has not gained any significant time savings for me in my experience. It may be because of all the writes to the Database Snapshot coupled with the size of the source database. The upgrade time to completion increased significantly. Even though the restore from Database Snapshot time was still good, I felt it a trade off. If you have had a better experience, let us know!

• Do not depend on snapshots to be your production backup solution! After getting comfortable with snapshots you might think why not use it as your backup plan. Snapshots are not meant to be your backup solution. They are excellent to use when testing 'what if' scenarios. Or even a T-SQL update that affects small to medium amount of rows across several tables. Snapshots cannot be moved off the SQL Server and stored elsewhere. Also you typically would not create a snapshot against a production database and leave the snapshot running. Eventually the snapshot will fill up could cause you to run out of disk space. Also could affect your production performance.

• One thing I noticed after reverting my source Sage 500 Database from the snapshot is that I did not need to re-register it!! 

• When you are done your database testing, do not forget to delete the Database Snapshot. From SQL Server Management Studio, locate the Database Snapshots folder and right click delete the Database Snapshots created.
• Multiple Database Snapshots can also be created. You can create different snapshots capturing points in time. This may be helpful in certain situations that require more complex testing
• The Databse Snapshot itself can also be queried from SQL Server Management Studio. Because its a read-only object, its limited in what can be done. I had tried pointing the Sage 500 client to the snapshot thinking it could be used for lookups only. Unfortunately, there are some write calls that caused the 500 app to fail to login so that didn't work out for me.

I hope you take the time to try using a Database Snapshot as a way to test 'what ifs'. Even if you don't use this with Sage 500 databases, you will find this feature to be a huge timesaver any SQL Server database!

-Tony