Creating & rolling back test data with SQL server

Question

I am creating a series of web automation tests which require test data to be in a database (SQL Server 2008). To generate the required data for each test I have to call some C# code which will insert the correct data into the DB (i.e. I can't just write SQL scripts to insert the data). My problem is I don't want to pollute my test db with lot's of test data from these automated tests. So would like to rollback all of the changes made to the DB during the test.

Can anyone suggest a sensible way of achieving this?

Answer 1

Simple way would be to create a backup of the database before running the tests, and then just restore back at the end.

Answer 2

Two ways of doing this.

One is to enclose your test within a transaction and roll it back. Another is to use a cleanup script as part of your test completion code (we do this for some of our integration tests where a transaction does not work).