2

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?

Vivian River
  • 329
  • 4
  • 10
  • Ideally, it would be great if I could do this with a series of SQL commands that could be run as a privileged user. – Vivian River Jun 16 '10 at 20:21

2 Answers2

2

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

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

Rob Moir
  • 31,664
  • 6
  • 58
  • 86
1

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
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47