January 17, 2007 at 4:04 pm
Hello,
I seem to have this happen whenever I go to restore our SQL Server 2005 database. I get the "database is in use" error.
Is there a specific order of operations I can follow in Management Studio to restore a backup over an existing database without this error? If not, is there a reliable way to restore (to a point in time) via Transact-SQL?
Thanks for any help!
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 17, 2007 at 5:35 pm
Sounds like you need to restrict access to the database before restoring. You could set the database to RESTRICTED_USER by using ALTER DATABASE. This would disconnect anyone who isn't a member of sysadmin, dbcreator, or db_owner roles.
Greg
Greg
January 18, 2007 at 7:15 am
Thanks for your reply!
If the only users left connected are in one of those roles, though, does the restricted user setting not work in this case? If it doesn't, is the only recourse to manually kill the spids of other users?
The reason I ask is that I once tried to put the database into single-user mode to get around the restore problem and that was a disaster -- it kept even me from using the database because it gave the one available connection to another user (I think it was a connection from the web site or something similar). Does restricted mode have the same side effect?
I'm surprised that when one checks "Overwrite existing database" that SQL Server 2005 doesn't automatically disconnect other users. I suppose this is a precaution against making it too easy to accidentally overwrite a database?
Thanks again!
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 18, 2007 at 11:25 am
That's true, though the membership of those roles should be small enough that you can notify them that you want to restore the database.
You could try running
ALTER DATABASE yourdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
then the RESTORE
in a single batch. That should disconnect everyone and start the restore before they can re-connect.
Greg
Greg
January 18, 2007 at 2:14 pm
Thanks again for the help.
Ironically -- and I offer this as advice to anyone out there being asked to do restores -- it turned out that only one table was needed to be rolled back, so I wound up restoring the database to a copy and reloading the table without having to overwrite the original database (and thereby avoided costing users up to an hour's worth of work...).
It's a beta system, but still, it is always good when one can avoid getting users ticked off. 🙂
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 18, 2007 at 4:39 pm
Thing to keep in mind with Single User mode.
Stop SQLServerAgent service. That controls the jobs. If a job starts up - that's one connection. Or disable any jobs that use that database.
If you have both Enterprise Manager and Query Analyzer open, that's two connections EVEN if you open Query Analyzer through Enterprise Manager.
-SQLBill
January 19, 2007 at 8:17 am
It is a disaster if a user has application role on a database. In this scenario, we can disconnect the user. However, it will be reconnected immediately. I had to disable this account before disconnecting this user and restoring the database.
Also, if the database is used in the Query Analyzer, the database is in use. We need to do something accordingly.
January 22, 2007 at 10:24 pm
This command will disconnect all users of the database and keep them out. Once the database is offline, no one can connect to it until you put it back online.
alter database MyDatabase set offline with rollback immediate
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply