Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL server (MS SQL 2005) in my development environment. I have a suite of unit tests for some .net code that will connect to the database and perform some operations. If the code under test works correctly, then the database should be in the same (or similar) state to how it was before the tests.

However, I would like to be able to roll back the database to its state from before the tests run. One way of doing this would be to programmatically use transactions to roll back each test operation, but this is difficult and cumbersome to program; it could easily lead to errors in the test code.

I would like to be able to run my tests confidently knowing that if they destroy my tables, I can quickly restore them?

What is a good way to save a snapshot of one of my databases with its tables so that I can easily restore the database to it's state from before the test?

share|improve this question
    
Ideally, it would be great if I could do this with a series of SQL commands that could be run as a privileged user. –  Rising Star Jun 16 '10 at 20:21
add comment

2 Answers

up vote 2 down vote accepted

Why not use the Transact-SQL backup and restore routines?

Have a look at the documentation on MSDN here and here.

share|improve this answer
    
Thanks! This looks like exactly what I need. –  Rising Star Jun 16 '10 at 20:48
add comment

if you had sql server 2008 then you could take a snapshot, run all your tests then restore the database back to the point of the snapshot with:

 RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT
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.