Tell me more ×
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.
up vote 2 down vote favorite

I am facing an issue while restoring the database.

I have many dump files in a folder and I try to restore them one by one to a database xyz, perform some update operation and continue my restore operation with the other dumps. During this restore operation all the other databases are entering the state of In Recovery . Also the database xyz is also entering the state of recovery and my restore operation fails for the 2nd dump saying the "Not able to achieve exclusive lock on the xyz" .

I am using SQL Server 2008 R2.

So how can I solve this issue please suggest.

share|improve this question
1  
The error about exclusive lock usually means there is an active session/connection to the database. I do not really follow your strategy though to doing the restore. Can you provide you process in a script format so we can see the process you are actually doing? – Shawn Melton Jul 21 '12 at 2:23

migrated from stackoverflow.com Jul 20 '12 at 23:18

2 Answers

up vote 1 down vote

In such situations I am trying this piece of code:

ALTER DATABASE <DB> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
share|improve this answer
Why do you use this ? What is the use ? Can you explain ? Thanks – Abhishek Srinath Jul 24 '12 at 21:30
RESTRICTED_USER means, that only db_owner and sysadmin might connect to database, WITH ROLLBACK IMMEDIATE means that every process in database would be killed. After making Your scripts You might to use : ALTER DATABASE <DB> SET MULTI_USER – Norbert Ozdoba Jul 25 '12 at 17:36
up vote 0 down vote

I've run sp_who to see who/what is connected to the database. 

spid ecid   status   loginame      hostname     blk   dbname    cmd                 request_id
59   0      sleeping TestUser1     machineName  0     xyz       AWAITING COMMAND     0

I run kill spid num, if I see that it is something that won't break anything, like testUser1 has a session and it is 'AWAITING COMMAND'.

kill 59

I have done this when I need to do a restore and someone is away from their computer with a SSMS query open on that database.

This won't work if it is an application that that is programmed to reconnect right a way.

share|improve this answer

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.