Three weeks ago I kicked off a survey about using database snapshots – see here - because I don’t really know how much they’re used or not out in the wild. Here are the results:

 snapshots Using database snapshots

The “Other” values are:

  • 13 x “No – not at the moment.”
  • 10 x “In our test environment for reverting to the original database state after a test. Much faster than restoring every time.”
  • 6 x “For development, to be able to go back to a previous known state after trying out new scripts and stuff.”
  • 5 x “Haven’t really looked at using them.”
  • 5 x “No – just read about them recently and have yet to determine usefulness.”
  • 3 x “Yes – every time I do a DBCC CHECKDB (okay, it’s hidden, but still a snapshot).”
  • 2 x “During application installation in order to do an easy rollback if smoke testing fails.”
  • 2 x “For rollback of code/data releases.”
  • 2 x “Never used, but did recommend once as a pre-deployment option so that there was a faster rollback in case a developer’s script broke once in Prod. Recommendation was not implemented.”
  • 1 x “As failsafe for monthly production deployments.”
  • 1 x “For EOM reporting copies of the production database.”
  • 1 x “No. Had performance issues so we turned them off.”
  • 1 x “Not supported in SQL2000.”
  • 1 x “When adding articles in replication.”
  • 1 x “Yes – For quick rollback during production changes.”
  • 1 x “Yes for ETL, and via a linked server connection since snapshot isolation isn’t an option.”
  • 1 x “Yes, for instantaneous restore of development environment.”
  • 1 x “Yes, rarely – for investigating issues on copy of production DB, way faster than restore.”

Very interesting! More than 60% of respondents don’t or can’t use database snapshots. I’ve always thought that database snapshots are an under-utilized feature, I’m not entirely sure why though (apart from the obvious Enterprise Edition restriction). It could be some of the limitations of them, such as:

  • You can’t refresh one without all connections to it being dropped (and then dropping and recreating it)
  • You can’t back up a snapshot (would require some interesting plumbing in the Storage Engine, as currently backups don’t use the buffer pool, and it’s the buffer pool that manages what’s in a database snapshot)
  • You can’t modify a snapshot (i.e. like having a base VM with various diff disks)
  • They can have a performance impact on the production/mirror workload depending on how heavily they’re used

I’m really glad that no-one confessed to using database snapshots as an alternative to log backups! See this TechNet Magazine Q&A column I wrote last year that explains why not.

Rather than repeat what’s already been said about why database snapshots can be useful, I’m going to provide you with some links to further reading:

  • Top-level of Books Online on database snapshots
  • Books Online on typical uses of a database snapshot
  • Whitepaper on performance implications of using database snapshots
  • Blog post on what happens to a database snapshot when a transaction rolls back
  • Blog post on some of the problems you can see from DBCC CHECKDB using database snapshots under the covers
  • Blog post on a bunch of potential problems when a database snapshot fills up or becomes corrupt
  • Blog post about memory usage from a database snapshot

Enjoy!