Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Database Snapshots

  • 18 May 2013
  • Author: Kathi Kellenberger
  • Number of views: 9654

I have been writing a lot of T-SQL scripts lately for regression testing. I identify test cases and write scripts to modify the data. Then the data can be verified as the rows go through ETL. As I tweak the scripts, I need to continually restore the source databases to get the data back to the starting point. Instead of doing restores, I am using the Database Snapshot feature. Database Snapshots were introduced in 2005 in Enterprise and Developer editions. They allow you to quickly revert a database to a previous state – perfect for what I am doing.

Database Snapshots should not be confused with database backups. The snapshot depends on the original database. If the original database is not online, the snapshot cannot be used to restore the database. You can query the snapshots to view the data as it looked when the snapshot was created. You can also use the snapshot to restore the database back to that state. Database Snapshots are often used right before a large load of data or a risky operation. If something goes wrong, the DBA can revert back using the snapshot.

Database Snapshots are sparse files that contain pointers to the original data pages. As the database pages are updated, the snapshot copies the originals. The snapshot file starts out very small, but grows larger and larger as the data changes. If the snapshot is left in place long enough, it could equal the size of the database files.

To view the snapshots, there is a folder in SQL Server Management Studio. You can view all of the database object just like a regular database. The difference is that you can’t modify anything. The only thing you can do besides view objects and data is to delete the snapshot. You can do this by right-clicking the snapshot and selecting Delete.

Except for deleting, there is no GUI provided for managing snapshots. Luckily, the T-SQL commands are pretty simple. Here are sample commands:

For creating a snapshot of a database with one data file

ON (NAME = AdventureWorks2012_Data,
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
AS SNAPSHOT OF [AdventureWorks2012];

If the database has multiple data files, you will have to include a clause for each file.

ON (NAME = AdventureWorks2012_Data,
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
(NAME = AdventureWorks2012_Data_2,
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
To restore the database with the snaphot:

RESTORE DATABASE AdventureWorks2012
FROM Database_Snapshot = ‘MySnapshot’

To drop the database snapshot:


You can create multiple snapshots with views of the database at different times. When restoring from a snapshot, any additional snapshots must be deleted first. You cannot delete or detach the database unless all snapshots are removed first.

Database snapshots are easy to use and very handy, especially for dev work. It is a quick and easy way to create a read only view of the database at a point in time. Enjoy!


Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.