How to automate functional/integration tests and database rollbacks

Question

In contrast to my previous question, i'll try to give my requirements.

I am trying to find some framework/methodology/"thing" that would fit the following:

• Ability to write an automated test, preferably written in Visual Studio, using C#.
• Test should drive a web browser and interact with SUT just like an user would.
• Test should be able to setup a test scenario in DB.
• Test should be able to assert that user interactions had the expected effect in DB.
• After test is completed, it should be able to roll back all changes it made in DB.

My first attempt was to use NUnit test to drive Selenium (and Watin before that), but i faced a bit of a problem (check the link above) while using TransactionScope to roll back the changes Selenium-driven browser did in the DB.

Has anyone done anything like this in the "real world"? I've found some references through Google, but haven't been able to find any concrete examples on how to implement this. There wouldn't be any problems if i'd be doing unit testing. In that case TransactionScope would be quite enough.

Edit: R. Harvey pointed me to this question, which is almost identical to my situation.

However that question is just almost identical. My application is part of a family of services, all of them accessing the same set of database tables. Amount of test data required does not allow for efficient use of drop/create-scripts, so is there some alternate solution for this?

We are using SQL Server 2005, and i'm not very proficient in database magic, so if there's some way to use sql scripting other than drop/create, then that could be an option.

Edit 2:

Based on the answers and some additional head scratching, we'll go for more lightweight databases for developers to perform unit-, integration- and functional testing. This enables us to use sql-scripts for setting up and tearing down the test.

Answer 1

Changes made in a transaction are only visible inside said transaction. Also wrapping the test in a transaction scope (if possible) would make the test behave differently than the real thing in a very critical aspect (transactions).

It is much better to use a database image that you restore before every test suite. This way after the suite completes and the verification is done, you drop the test database. The next run, during the suite setup, the database is re-created from the saved image in a pristine state ready for testing. Even better would be to have a script that deploys the database from scratch and run that script during suite setup.

Btw is not feasible to restore to a pristine state before every test. More generically is not feasible to have lengthy individual test setup and cleanup steps. As you add more tests the time spent restoring the database to test-ready condition between tests will become just unmanageable. Suites with hundreds of tests are quite common and full test runs of tens of thousands of tests would mean hours and hours spent just restoring database for test. Design your individual test so that they can be run independently, ie. test N has to produce valid results even if test N-1 failed.

Another thing to consider is failure investigation, you want your failed test to leave the database in a state that can be investigated for meaningful info and you want subsequent tests to be able to run and produce valid results. Sometimes these requirements will contradict each other, but you must take them into consideration and design your test around them.

Answer 2

If the amount of data required to restore the database to a known-good state is prohibitive of drop/create scripts and you are running you tests on Developer or Enterprise edition of SQL 2005, you could look into creating a database snapshot of the good state, and reverting to it before each test. This is considerably faster than a full restore, although it may still be too time consuming if you have hundreds of tests.