June 12, 2008 at 12:36 pm
Does anyone have any tips/tricks for restoring a database that has a continuous process that needs to be connected? We have a piece of software that has 3 servers (web server, database server, and app server) and those 3 servers communicate back and forth. So there is always a process connected to the database server doing various tasks. When it comes time to restore that database, I need exclusive rights and it's very difficult when that process to "reaching out" every few seconds to gain a connection.
I've tried setting the database in Single User mode but somehow there are always more than 1 user, and then I get blocked!
I've tried to kill the processes but they come back before I can get the database restore going.
I've also tried to use "BACKUP LOG ... WITH NORECOVERY" so the database is not accessible but that doesn't work either! Once again, you have to have exclusive rights to use "with nocovery" and I can't gain that.
I know I can go around and shut the services down but there has to be away to block all users to a certain database. Or some similar process for shutting users out of a database on command. Just looking for tips/tricks. Thanks!
Mike
June 12, 2008 at 12:42 pm
Not knowing what application you are running I can only give you this suggestion: shutdown the app server then do your restore on the database server. I am assuming that the web server talks to the app server which then talks to the database server.
😎
June 12, 2008 at 1:39 pm
"... shutdown the app server ..." Or just stop the service ?
Or delete the database. Then restore.
June 12, 2008 at 2:39 pm
This SQL should work from the master database:
alter database carl_test
set offline with rollback immediate
restore database carl_test
from disk = 'r:\carl_test.bak'
with replace
SQL = Scarcely Qualifies as a Language
June 17, 2008 at 7:19 am
Assuming the continous connection does not have dbo user access, you could restrict access to the database only to dbo thus preventing connection....assuming you have dbo access, you can restore.
Gethyn Elliswww.gethynellis.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply