Take the 2-minute tour ×
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.

What are the most efficient ways to save the exact state of a SQL server database and then be able to bring it back to that state at will?

I am testing code performance against a database and each test alters it so I would like to be able to (as quickly and easily as possible) return it back to its beginning state each time I want to test performance again. I expect to be doing a lot of this.

share|improve this question
    
Are you running on a virtual server or a physical server ? If virtual, then snapshot the current good state and then revert it back once the testing is done. –  Kin Jan 3 at 20:52
1  
@Kin I personally wouldn't deal with VM snapshots or save points. Way too broad of a brush for a more narrow requirement. Unless the OP is dealing with many OS entities that also need to be reverted. –  Thomas Stringer Jan 3 at 21:14
    
@ThomasStringer Good point. Thanks ! –  Kin Jan 3 at 21:26
add comment

1 Answer 1

up vote 4 down vote accepted

To be honest, a full backup will be the easiest and quickest way to do that. Simply backup your database (a full backup) to the point you want to revert to, and then when you need to get back to that point, do a full restore.

Also, depending on your version and edition of SQL Server (currently not specified in your question), you could work with Database Snapshots, and use a snapshot to revert back to.

The above two solutions are quick and easy if you're wanting to revert the entire database back to a certain point (either at the full backup, or the snapshot). But if you're only looking to revert a subset of your database back to a certain point in time (think: certain types of development, benchmarking, or demo'ing) then the easiest thing would probably be a reversion script to target your specified affected objects.

With a tiny database that you want to revert the entire thing back, the first two options would be quickest. But if you are dealing with a relatively large database, where a restore is just not working for you timewise, then you could also consider a script to do the revert. It all depends on what you're trying to do, how big the database is, and what you're willing to handle based on the clock vs. trouble of reverting.

share|improve this answer
add comment

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.