Solved

Trying to restore database in Enterprise Manager and I get error

Posted on 2004-10-19
8
618 Views
Last Modified: 2013-12-03
I am attempting to restore f:\sqldata\mydatabasebackup  to my mydatabase.mdf file in Enterprise Manager and I get this error "Exclusive access could not be obtained because the database is in use.  Restore database is terminating abnormally."  

I tried to take it offline and it failed with an error 5070.    
What do I need to do to get exclusive use and to get this restore done.
I tried to clear connections when I Detached the database but when I reattached it the connection was back.

Thanks for any help.
0
Comment
Question by:patrickmiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Expert Comment

by:apirnia
ID: 12352107
When you do a restore go to the options tab and select the 3rd Chekc box:
Force restore over existing database.

I think this will do the job
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 12352163
Where is the connection from?  Is it, perhaps, you?  (Do *not* expand the DB in enterprise manaqger, do *not* look at any data in it).

If the connection is not you, then who / where is it / is it from?

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12352216
Or use QA:


ALTER DATABASE myDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE myDatabase
FROM FILE = 'f:\sqldata\mydatabasebackup'
WITH REPLACE
--* next lines are optional, if you want to restore to a different file name
--    ,MOVE 'logical_mdf_name' TO 'f:\data\<mdf_file_name>.mdf'
--    ,MOVE 'logical_log_name' TO 'f:\log\<log_file_name>.ldf'

ALTER DATABASE myDatabase
SET MULTI_USER WITH ROLLBACK IMMEDIATE
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:patrickmiller
ID: 12352374
Checking the 3 box didn't help.

Using QA gets stuck on the syntax line 5    the backup file is called pam_mast_data_dump.dat

ALTER DATABASE pam_mast
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE pam_mast
FROM FILE = 'f:\sqldata\pam_mast_data_dump'
WITH REPLACE
--* next lines are optional, if you want to restore to a different file name
--    ,MOVE 'logical_mdf_name' TO 'f:\data\<mdf_file_name>.mdf'
--    ,MOVE 'logical_log_name' TO 'f:\log\<log_file_name>.ldf'

ALTER DATABASE pam_mast
SET MULTI_USER WITH ROLLBACK IMMEDIATE
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 450 total points
ID: 12352517
D'OH!! SORRY, keyword is DISK not FILE (that's what I get for not looking in BOL before posting!!).

Also, be sure to specify the full path and file name from the SQL Server point of view.  That is, if you have mapped drive F: on *your* login SQL won't know that; you must use the drive as SQL knows it (this may or not apply here, just trying to cover all bases).


ALTER DATABASE pam_mast
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE pam_mast
FROM DISK = 'f:\sqldata\pam_mast_data_dump.dat'
WITH REPLACE  -- only use if db already exists on server
0
 

Author Comment

by:patrickmiller
ID: 12353248
I still get this error.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Server: Msg 3101, Level 16, State 1, Line 4
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
0
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 50 total points
ID: 12353255
Note:  One thing I noticed with SQL 2k that differed from SQL 7.0 is that a Restore can (in some cases) require two connections to the DB, and that if the DB is in Single_User mode, this will not be allowed.  (this drove me up the wall the first time our VB app tried to trigger the backup/restore sequence through an SQL call after the upgrade to 2k). Try the RESTRICTED_USER option.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12358000
Thanks, bhess1.  I hadn't run into that issue yet and so didn't realize it.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question