Avoid recovery while booting SQL Server under Unittesting

Question

I am currently building unit tests that check to see if they are editing the SQL Server 2005 database correctly. To do this, I created a small subset of the data from our production and save it as a backup file. Whenever a unit test needs to be sure that it has a clean database state, it calls the restore procedure, which basically calls the following sql:

RESTORE DATABASE database FROM DISK = 'c:\test\backup.bak' WITH REPLACE, NORECOVERY 

This works fine and has decent speed. When I say this usually, because sometimes the database gets stuck in "recovery" mode, error messages appear that look like this (assuming it's an Alter command):

 ALTER DATABASE is not permitted while a database is in the Restoring state. 

This means that if the first failure, each test fails. I can get the database out of this stuck state, but it is rather annoying to do it every time, it is a lot of time, and the whole point of unit testing was that I did not need anything when I activated the test.

I tried to figure out if I could avoid this with the RESTORE syntax, but I can't see any parameters or flags that sound as if this could solve the problem.

Is there any other method that is much safer than the RESTORE command?

Update: I found that recovery failure occurs mainly when the connection is still active. I usually looked at this issue with the following structure:

 ALTER DATABASE MyDatabase SET Single_User WITH Rollback Immediate -- Restore logic here ALTER DATABASE MyDatabase SET Multi_User 

The problem is that it limits all connections to just one. Sometimes one of the connections, which is forced to close, switches to the only free one, disrupts recovery and makes it unsuccessful (or is it something that I can collect from documents and pages that I read).

Instead, I try to execute the following SQL command before proceeding with my recovery logic:

 Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id('Mydatabase') While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id('Mydatabase') and spid > @spid End -- Do Restore Logic 

http://geekswithblogs.net/AngelEyes/archive/2010/02/24/kill-connections-to-resote-db---sql-server.aspx

This section of code is turned on and actively closes all connections, which makes it possible to perform a logical recovery room. However, I am not entirely sure that this is the best way to go, simply because it does not prevent connection recovery, which may again disrupt the recovery process. But so far I have not seen evidence that this has happened yet.

Answer 1

Using the usr sentence and scripting my path through the recovery process, I got it working. This answer should close the question so that the visitor knows that he has found a solution. The solution is described in the original question as an update.

Answer 2

What I found with success is first rolling back the database, and then deleting it, which affects any problems that it is already open or related to:

 if db_id('MyDatabase') is not null ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE if db_id('MyDatabase') is not null drop database MyDatabase create database MyDatabase