Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Why is this tagged .NET? –  Blam Dec 12 '12 at 21:08
    
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 –  Nitin Nov 16 '13 at 7:16
add comment

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

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

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

I know this dosen't answer your question, but, well written tests should be independent of the state of the database (or specifically the data contained in the database). Tests should supply any data they need, then clean that data up after running (leaving the database as they found it). The DB restores should not be necessary.

The tests should not be dependent on each other either.

share|improve this answer
2  
I see what your getting at but a snapshot is a convenient (read fast) method of doing the clean-up for an integration test suite. If the snapshot creation and restoration is part of the test suite, what's the difference between this approach and the more laborious route? –  Mark Storey-Smith Dec 13 '12 at 0:42
add comment

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
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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