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,
LVL 5
garycrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

myrthnCommented:
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
garycrisAuthor Commented:
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?
garycrisAuthor Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 PletcherSenior DBACommented:
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.
LunchyCommented:
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.