THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Database snapshots -- are you using them?

Database snapshots were introduced in SQL Server 2005, and have been around for several years now. One of the main motivations is that you can create database snapshots to protect your database from user or application errors. It's one more weapon in your DR arsenal.

But looking at the environments I have worked in for the past few years, I don't recall seeing the feature being used for this purpose at all. In fact, I have not seen it being used for any purpose, period (other than internally by DBCC, etc). Perhaps, people should be using it, but for whatever reason they don't. Perhaps, I'm just hanging around with a wrong crowd.

So are you using database snapshots? If so, for what purpose?

Published Wednesday, March 25, 2009 7:09 PM by Linchi Shea



Alexander Kuznetsov said:

March 25, 2009 6:25 PM

noeldr said:

We use them in QA and Dev to try several approaches to data modifications/settings and roll it all back to the initial state very quickly.

In addition some applications I can not disclose, need stability for reporting rapid changing data as if it was a point in time. Those reports run off the snapshot while still allowing the db to accept the new changes.

March 25, 2009 6:30 PM

CK said:

Testing a deployment that I might want to rollback.  Or testing ETL functionality that I want to rollback.  I am in love with them actually, incredibly useful.


March 25, 2009 6:57 PM

Linchi Shea said:

These are all excellent for testing. Anybody uses the feature in prod at all?

March 25, 2009 8:31 PM

John Welch said:

I've used it for testing as well (it's great for reverting to a known state after running unit tests). I've also used it in production for ETL processes. Take a snapshot prior to starting the ETL, and if there is a failure, you can revert to the snapshot. However, because of the performance overhead, I don't typically use it if the ETL process is moving a large amount of data.

March 25, 2009 9:39 PM

Uri Dimant said:

Hi Linchi

At my workplace we do not use them at all. Actually, I do not see any reason ( I might be wrong) to use it on production as if you want test the script or run something  you will do that on the test server. Just my two cents.

March 26, 2009 1:47 AM

Sankar Reddy said:

We don't use them in production either. One of the drawbacks in SQL 2K5, anytime a snapshot is dropped entire procedure cache on the whole instance is flushed. This may not be suitable on high performance systems.!1F1B61765691B5CD!319.entry

March 26, 2009 2:50 AM

Hugo Kornelis said:

I'm building a code generator for SQL Server. When the metadata is changed, the code generator will generate a script to implement just the changes, without affecting the existing data. When SQL 2005 came around, I decided to use database snapshot as a safe way to ensure that on failuer of any part of the modification script, the database can be easily brought back to its prior state.

Until I realised database snapshots are an enterprise only feature. I don't want to limit my code generator to enterprise, so I had to take it out again and use the tried ans trusted BEGIN TRAN and COMMIT or ROLLBACK instead.

March 26, 2009 5:15 AM

Uri Dimant said:

Hi Hugo

Do you use snapshot in production? I work for the company where OLTP the data changed very frequently and even if you create a snapshot and try to bring back to its prior state we are about to lose the data...

March 26, 2009 6:12 AM

Bill Graziano said:

We use snapshots in conjunction with datbase mirroring to populate the data warehouse during the day.  We make a snapshot off the mirror and run our SSIS packages against that.  Obviously we're all Enterprise Edition.

March 26, 2009 11:11 AM

Catalin Adler said:

I'm using it in a production environment. Reporting on a mirrored database -- the secondary instance.

March 26, 2009 12:56 PM

jerryhung said:

Not here, cannot justify Enterprise licenses

March 26, 2009 2:35 PM

Hugo Kornelis said:

Hi Uri,

>>Do you use snapshot in production?<<

No. As I said, I wanted to use it for my schema upgrade scripts, but had to revert to using transactions because I don't want to force the cost of enterprise edition on my customers.

>>I work for the company where OLTP the data changed very frequently and even if you create a snapshot and try to bring back to its prior state we are about to lose the data...<<

Yes, of course you would. Everything that happens after making the snapshot will be lost. Heavy OLTP is not a good scenario for restoring from a database snapshot.

March 27, 2009 3:43 AM

Tommy Bollhofer said:

We use them for BI/Reporting – i.e. generating snapshots post ETL for SSRS/SSAS. Gives us the flexibility to run our ETL processes in a loop; continuously refreshing data.

March 27, 2009 8:46 AM

Dave Jermy said:

We use a snapshot as the data source for our reports as the nightly ETL run often encroaches upon the following business day and that way the previous day's position can be used until the ETL is finished.

Having said that, we've had some odd issues with query results and performance against the snapshot that we're finding it very difficult to track down.

March 27, 2009 10:09 AM

Ranga Narasimhan said:

We tried to use database snapshot on the mirror db for reporting purpose, as mentioned by SR13, dropping snapshots frequently flushes the cache and also, had some difficulties trying to implement mirroring monitoring. See this

March 27, 2009 1:55 PM

hsmoot said:

I mirror 3 of my main production databases and have snapshots of various types.

First, I have some very large databases and many of the reporting services and ETL processes can have a severe impact on performance in production.  Instead, I point all these heavy processes to the snapshots and production is not affected.

Second, I have instant access to a previous state of the data without restoring a backup.  These restores can be time consuming but I can respond instantly to "lost data" issues.  Depending on the purpose of the production database, I have various types of snapshots.  On my largest, there is an hourly snapshot and 3 daily snapshots overwritten daily.  On another db I have an hourly snapshot and 3 daily snapshots going back 5 business days.  I have found them useful for many reasons and appreciate the flexibility they offer.

March 27, 2009 11:35 PM

jeff_yao said:

Snapshot has its biggest (imho) drawback, i.e. snapshot file size limitation. We did not know this until we used it for our production upgrade, we just wanted to rollback safely if our upgrade went wrong and we intended to drop the snapshot once the upgrade succeeded, however this upgrade involved huge data change (including index rebuild) and finally we got lots of weird errors, and extensive research found a blog entry in MS team said this was limitation of snapshot (restriction of spare file). After this, we never bothered to use it again.

March 28, 2009 1:05 AM

Greg Low said:

Hi Linchi,

We use them in unit testing for databases. The ability to quickly take databases back to a known state before running each test is excellent.



March 28, 2009 4:24 PM

Linchi Shea said:


Can you post the referenced MS team blog entry? What is the file size limit?

March 29, 2009 12:20 AM

bogdanblg said:

Hey guys!

Don't forget about the extra I/O you get for each snapshot that you take. Whenever a page is modified for the first time in the actual database, that page has to be copied to each of the snapshots. If you have an OLTP database that takes a lot of changes, you'll be seeing a severe performance hit.

You can check this by setting custom performance counters to monitor the I/O of each snapshot database.

All the best!


March 29, 2009 6:43 AM

Elcanadian said:

We are using them for reporting. This save the production system from any potential slowdowns due to the complexity of the reports.

March 30, 2009 3:14 AM

Robert said:

Database snapshots would enjoy more popularity if Standard Edition had them.  We can't justify the 3x expense of Enterprise Edition, and therefore only use them in dev/testing scenarios.  What a shame!

April 6, 2009 11:51 AM

Susan said:

We're thinking of using snapshots as supporting evidence for reports we write to the feds.  We have a C# web application with a SQL Server 2005 back end.  Most of our colleagues in other states are still using Access databases that are backed up to CD every year.  So that's what our federal project officers expect us to use too.  I wonder if snapshots would give everyone what they need.

April 6, 2009 3:44 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement