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?