6

I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the beginning of these tests.

The restoration code looks like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY
ALTER DATABASE {0} SET MULTI_USER

After this, the tests connect to the database and do whatever they need to do.

The problem is that, during the tests debug, if eventually I need to hit the stop button to cancel the tests, the database is being left in the Restoring state forever. It's strange because it only happens when I stop the debug session. If I have 20 tests and all of them restores the snapshot prior to the test, I'll get no error during these test executions.

Do you have any suggestions of what might be causing this?

EDIT

Complementing the @usr's response, to recover the database from the inconsistent state at the beginning of the tests, it's necessary to add the REPLACE option to the restore statement.

It will work if it's like this:

USE Master
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
RESTORE DATABASE {0} FROM DATABASE_SNAPSHOT = '{1}' WITH RECOVERY, REPLACE
ALTER DATABASE {0} SET MULTI_USER

migrated from stackoverflow.com Dec 12 '12 at 21:25

This question came from our site for professional and enthusiast programmers.

  • Just an fyi, you can check the status of a database restore and get an estimated amount of time for it to complete. Not sure what's actually going on, but would be interested to see what the query results are. The query can be found here: sqldbadiaries.com/2010/09/07/… Just change the where clause from B.COMMAND LIKE '%BACKUP%' to B.COMMAND LIKE '%RESTORE%' – brian Dec 12 '12 at 21:08
  • this might be helpful::: RESTORE DATABASE Databse_name WITH RECOVERY – user30588 Nov 16 '13 at 7:16
  • Note that if you DROP the snapshot whilst it's in the aborted restore (nothing happening, but database in 'restoring'), then the snapshot files (.ss) are orphaned and left on disk. This prevents creating the snapshot again in future (unless you use different path). We found this out the hard way - don't do this! This is current in SQL 2012, not sure about after. – piers7 Jun 29 '16 at 5:01
6

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new.

Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests.

To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot again.

  • Surprised that killing the debugger kills the restore (depends what you are debugging), but certainally if the SPID doing the restore is terminated (on the server, not just kill the client), then the database is left dangling. The scenario I had for this was that the restore for the unit test was occasionally killed due to a deadlock (on master presumably). The solution is as above - just restore from the snapshot again. – piers7 Jun 29 '16 at 4:54
-1

There might be another query still running against your original database that is blocking your restore command.

First, verify the status of your database:

-- check status of databases
select name, state_desc
from sys.databases
where name = 'DATABASENAME';
go

Run this to see if anyone is running a query against your database:

select r.session_id, s.host_name, s.program_name, s.login_name,
DB_NAME(r.database_id) as 'Database', r.status,
r.command, r.percent_complete, st.text
from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) st
where s.is_user_process = 1
and r.session_id <> @@SPID
order by r.session_id;
go

If so, kill 'em:

-- kill the offending session
kill SESSIONID;
go
  • Why would a query against the snapshot block a restore?; Also, one cannot access a database that is in restore. – usr Dec 12 '12 at 22:43
  • Whoops, that was a typo. I meant against the original database. Thanks for the catch, I'll edit that now. – Steven Dec 12 '12 at 23:46
  • 1
    I understand. Still, it is not possible to have a DB in state RESTORING and have a session using it. RESTORING is set after the restore command can obtain an exclusive lock. – usr Dec 13 '12 at 9:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.