Restore database from snapshot in TestSuiteTeardown

Apr 23, 2009 at 3:22 PM
Edited Apr 23, 2009 at 3:27 PM
When I try to restore my database from a shapshot with the following statement (the restore is executed in the TestSuiteTeardown):

<SqlCommand connectionRef="master" returnsValue="false">

 RESTORE DATABASE MyDatabase FROM DATABASE_SNAPSHOT = 'MyDatabase_SSISUnit'

</SqlCommand>

I get the following message: 

Error occurred: RESTORE DATABASE is terminating abnormally.
Database state cannot be changed while other users are using the database 'MyDatabase'

This is just a "regular" error message that is generated by SQL Server. The message itself is clear: there are still connections to MyDatabase which prevents the restore from taking place. This implies that ssisUnit still has a connection to the database. I have set up two connections:
 

 

 

<Connection name="MyDatabase" connection="Data Source=(local)\SQL2005;Initial Catalog=MyDatabase;Provider=SQLNCLI.1;Integrated Security=SSPI;" connectionType="ConnectionString"/>

 

 

  <Connection name="master" connection="Data Source=(local)\SQL2005;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;" connectionType="ConnectionString"/>

The restore command is executed with the master connection. But appearently ssisUnit still maintains a connection to MyDatabase. I have read that others also use the snapshot technique to reset their database back to the original state. Is there a way to prevent this error from occuring?

 

Coordinator
Jun 11, 2009 at 8:45 PM

nessinot, sorry for the delayed response to this. I've done some testing, and can't replicate the error. Have you verified that, prior to running the unit test, that there are no open connections to the DB? Visual Studio often has an open connection to the database when you are working with SSIS.

You can check for open connections by running sp_who2, and see if any connections reference the database you are working with.

Coordinator
Jun 12, 2009 at 2:25 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jun 17, 2009 at 12:32 AM

A few additional things I found with this - you might have to force the connections closed. From Books Online:

"To force current users out of the database immediately or within a specified number of seconds, also use the ROLLBACK option: ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK rollback_option. For more information, see ALTER DATABASE (Transact-SQL). "

The problem does seem to be connection pooling, so forcing the connections closed seems to be the cleanest approach.

Jun 18, 2009 at 8:03 AM

John, no worries about the delayed response. We are all busy people ;-)

Thanks for your tips. I will try your them and let you know if this solved the problem.