How to gain Exclusive Access to a database

  • 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.

  • 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.

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

  • 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.

  • 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

  • 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

  • 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

    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

  • Thank you all. Things worked as documented and I was able to restore the database. Thanks.

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

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