Solved

Trying to restore database in Enterprise Manager and I get error

Posted on 2004-10-19
8
608 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: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:ScottPletcher
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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
ID: 12358000
Thanks, bhess1.  I hadn't run into that issue yet and so didn't realize it.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now