Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trying to restore database in Enterprise Manager and I get error

Posted on 2004-10-19
8
Medium Priority
?
625 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

581 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