asked on

Unable to get exclusive access to restore a database

I have a backup of a database called PandL. I have another database called zTest. I wish to restore the PandL backup over zTest. The goal here is to have zTest as an exact copy of PandL for testing purposes. I'm trying to do this through Enterprise manager.

From the right click menu, I select restore database, I select zTest and then choose my most recent backup from PandL. I check the option to 'Force restore over existing database'. When I click OK, I get an error "Exclusive access could not be obtained because the database is in use". There is no one else connected to the zTest database although it is possible an application has an open connection. So I need to know how to sever all connections.

I attempted detaching the database. I got a message saying there were active connections and would I want to clear them. I said Yes, but then when i went to restore the database I got the same error.

I also tried to just delete the database, but again I get the error that there is an open connection and the database cannot be deleted.

As you can tell I'm not that advanced with SQL server. I'm willing to do this through a query in query analyzer if I need to, but I need to know the exact statement since I am not familiar with the language.

Thanks,

myrthn

Use the following commands in Query Analyzer to see who is doing what on the Server.

You will want to be careful when using the sp_lock because I have had QA lock up on me when returning a LARGE recordset. Which is usually when you really need to know who is locking up the system.

------------------------
use master
exec sp_lock
exec sp_helpdb
exec sp_who
------------------------

sp_lock will tell you who is using which databases
sp_helpdp will tell you the names of each database
sp_who 'id' will give you the user information

to kill the process, use KILL 'spid' from sp_lock

You can get help on all these SQL stored procedures in SQL Server Books Online.

Hope this helps...

~Myrthn

garycris

ASKER

Isn't there just a way to Restore and force over any existing connections? I'm not that interested in what is connected. This is just a shell for a test database. As mentioned above, I'm willing to delete the database and restore as a new database, but even deleting a database is not allowed because it's hung up. I'm logged in as the SA, can't I just bulldoze over this?

garycris

ASKER

I actually figured out a way to do this myself. by detaching the zTest database and then restoring as PandL and zTest, clicking force restore over database, it worked.

Thanks anyway.

Guy Hengel [angelIII / a3]

just to clarify:
if you want to restore a database (using QA or EM), you shall NOT be connected yourself to the database you want to restore.

In QA:
USE MASTER
RESTORE DATABASE TestDb FROM ...

In EM:
Close the database you want to restore, and select another db. then, right-click to the db to choose restore db in All Tasks

Scott Pletcher

In QA:

USE master

-- force every user off the db
ALTER DATABASE zTest
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE zTest
FROM DISK = '...',
...

Or, to use EM to do the restore, immediately *before* clicking OK to begin the restore, run the first command in QA, then immediately click "OK" to begin the restore.

ASKER CERTIFIED SOLUTION

Lunchy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial