unit / integration testing and restoring databases

Question

Busy with automated test cases in C#, and we need to restore a DB snapshot after every test. Problem is, when running multiple tests it fails because the "Database state cannot be changed while other users are using the database."

We use SqlConnection.ClearAllPools(); before the restore, but after the fourth test it won't restore again for the run, and it seems that the pools stop clearing. (Why four? See edit 2)

How can I make this more reliable?

EDIT

Maybe I should give more information about the system. These are unit integration tests for a service. The tests reference the service dll (no service reference, we access the service methods directly). So, there are no SQL in the tests other than restoring the DB snapshot after every test block.

This is a service, so we don't exactly manage the connections. We have a central point where we create the Database objects from which we get our DbCommands to execute stored procedures.

Using sp_who2 within SQL studio, I observe the following: There is one session to the DB for the first four test blocks (where each block is separated by a ClearAllPools() and a snapshot restore), but as of the fifth test block there are three sessions against it. (Why? This might be a clue to the problem.) (The snapshot restore opens an additional connection to the master DB.) All open connections have status sleeping, awaiting command, even the one blocking the snapshot restore connection.

EDIT 2

Why five? I thought the test cases would execute in random, but I was wrong. I found the culprit. More than one connection opens, the system only uses the last one, and the others are left hanging and won't clear until you exit.

Answer 1

Before you restore your snapshot set the database in single user mode:

   ALTER DATABASE <mydb> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

If you don't wish to kill the open connections you can make the command wait by running it without the rollback option

   ALTER DATABASE <mydb> SET SINGLE_USER

It is a hack, but it is very hard to get your snapshot restore working consistently otherwise. (once bitten, twice shy.)

Answer 2

I use a different approach. I run the tests in a transaction that will be disposed (rollback) at the end of every test. This way you don't need to throw away the database on every test session because the db is always "clean". In C# you can create a TransactionScope and dispose it after the test, or (better) if you use xUnit.net you can use the AutoRollback attribute.