June 28, 2006 at 5:43 pm
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.
June 28, 2006 at 5:52 pm
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.
June 28, 2006 at 6:10 pm
Have you tried a manual refresh? EM is notorious for that (at least IME).
June 28, 2006 at 6:17 pm
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.
June 29, 2006 at 12:15 am
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
June 29, 2006 at 1:53 am
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
June 29, 2006 at 1:56 am
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
June 29, 2006 at 9:58 am
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