Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trying to restore database in Enterprise Manager and I get error

Posted on 2004-10-19
8
Medium Priority
?
623 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
  • 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 70

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 70

Accepted Solution

by:
Scott Pletcher earned 1800 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 200 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 70

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

885 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