How to Restore a Database Used By Web Site 24/7?
January 9th, 2008 by Andrew Chen
Did you experienced a situation where a database was used by a high traffic 24/7 web application and somehow some important data got deleted and need a restoration? The database was located on a server that had a few other important databases and it was located in a data center far away and you couldn’t disconnect the database server from the network.
In this situation if you try to restore the database you may find it always fail with an error message saying somebody else is using the database. What can you do to restore the database? You may want to stop the web servers first and wait till all the connections are gone then restore the database. It may work but in a load balanced web site that means you may have to stop a list of web servers. The worse case is that there may be applications other than the web site accessing the database. Probably killing all the processes that are accessing the database using Enterprise Manager or Query Analyzer and then restore it? Most likely it will fail either because hundred thousands of users are using the web site the second you kill a connection a new one is created by the web servers.
A convenient way to handle this situation is to temporary take out the database user that the applications use to access the database. The following screen shot shows how to remove a database user.
Once you take out these database users you can kill the existing processes that are accessing the database knowing that no new connection to the database is allowed. After that the database can be restored without complaining. When database restoration is done you can run the following query to re-associate the database users to database logins.
sp_change_users_login @Action = 'Auto_Fix'
Related Posts:
(No Ratings Yet)
Loading ...
No comments yet.