How to gain Exclusive Access to a database

Question

How to gain Exclusive Access to a database

Bill Wagman

SSCommitted

Points: 1763
More actions
June 28, 2006 at 5:43 pm

#61919

Greetings, I am attempting to restore a copy of a production database to a test server. I am unable to get exclusive access to the database as it is in use. I see the processes accessing it and attempt to kill them using the Enterprise Manager. I choose the process, right click and then choose kill process. It asks me if in fact I do wish to kill the process, I say yes, click ok but the process does not go away. I suspect there is an additional step I must take but I don't know what it is, can anyone help? Or, is there some other means whereby I can get exclusive access to the database so I can either drop it or restore over the existing database? Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bill Wagman SSCommitted Points: 1763 More actions · Answer 1

I forgot one detail, all the processes are in the state AWAITING COMMAND which BO says should not be killed. I don't understand why that is. Thanks.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

Have you tried a manual refresh? EM is notorious for that (at least IME).

Bill Wagman SSCommitted Points: 1763 More actions · Answer 3

One way I tried is to highlight the database in EM, choose all tasks, restore database, find the backup from which I want to restore and choose restore over existing database. That gives me the error (I didn't note the number) and tells me it won't grant me exclusive access to the database. Is that what you are thinking of or are you referring to something else? Thanks.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

perform in a single batch :

ALTER DATABASE yourdb Set RESTRICTED_USER , READ_only WITH ROLLBACK IMMEDIATE

Restore database yourdb from .....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

deenadayalan Say Hey Kid Points: 670 More actions · Answer 5

hi..

you can restore the db after executing the below T-SQL

using SQL QueryAnalyser.

ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

close query analyser after executing the query.

now try to restore yourdb from the backupset.

After successfull restore, don't forget to execute the following query.

ALTER DATABASE yourdb SET MULTI_USER WITH ROLLBACK IMMEDIATE

otherwise only one connection can access yourdb

regards deena

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

there is a chance someone else will get into the db during the time that you switch sessions !

Executing the alter and restore in a single batch avoids this risk