After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small DDL and DML changes. Additionally they are useful for large scale dev/test environments.

My intended use will be in our test environment where we need to test DDL/DML scripts and want to revert to the previous state without requiring lengthy restore times. Are there other methods besides snapshots here (excluding VM solutions)? Has this been widely adopted and are there any issues to be aware of? I'm trying to get a sense of the community outside of MS documentation and the few blogs I have read. If your impression that the documentation available online is representative, that's a fine answer for me.

Thanks again, gurus!

  • 3
    Don't forget that using database snapshots is an Enterprise Edition feature (other than DBCC using them internally).
    – mrdenny
    Jan 22, 2013 at 22:13

1 Answer 1


Database snapshots are used in SQL Server 2005 or later every time you do a DBCC CHECKDB - so they are widely used, even if it is behind the scenes. Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support.

It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs. The only downsides, apart from no tools support, is to watch disk capacity where you put the snapshot file, and ensuring you don't have too many snapshot databases running on the server at the same time.

  • I used to use them in dev environment, but performance was spotty: usually very fast, but sometimes freezing for a long time. 2005 Dev Edition.
    – A-K
    Jan 23, 2013 at 2:58
  • 1
    Yes, they are entirely dependent on the performance of the underlying storage. Jan 23, 2013 at 21:09
  • the sandbox test databses were on the same storage, yet did not intermittently freeze.
    – A-K
    Jan 23, 2013 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.