Solved

Trying to restore database in Enterprise Manager and I get error

Posted on 2004-10-19
8
615 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:bhess1
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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:bhess1
bhess1 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
When are cursors useful? 8 61
Report 8 27
SQL query 45 41
How to Rank results in SQL Server based on the search criteria 3 12
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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