Post reply

Restore Database with Active Connections

  • June 12, 2008 at 12:36 pm

    #192282

    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

    #828059

    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

    #828094

    "... shutdown the app server ..." Or just stop the service ?

    Or delete the database. Then restore.

  • June 12, 2008 at 2:39 pm

    #828128

    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

    #829758

    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