Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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!

share|improve this question
3  
Don't forget that using database snapshots is an Enterprise Edition feature (other than DBCC using them internally). – mrdenny♦ Jan 22 at 22:13

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
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. – AlexKuznetsov Jan 23 at 2:58
1  
Yes, they are entirely dependent on the performance of the underlying storage. – Peter Schofield Jan 23 at 21:09
the sandbox test databses were on the same storage, yet did not intermittently freeze. – AlexKuznetsov Jan 23 at 22:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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