Exclusive access could not be obtained ... Restore error

Using  sql server 2008, I'm getting this message when trying to Restore :

"exclusive access could not be obtained because the database is in use"

This is a live database for an E-Commerce store. What is the best way to fix this?

I've seen these solutions :

1.
Select database , bring it offline and bring it online and try to restore database.

2.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;

What is the best method to use to get rid of that error?  thanks
LVL 16
MikeMCSDAsked:
Who is Participating?
 
knightEknightCommented:
I assume that you are restoring the database between the two ALTER commands, correct?

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
-- restore the database before running the next ...
ALTER DATABASE AdventureWorks
SET MULTI_USER;
0
 
brutaldevCommented:
Script (number 2) would be a better, faster and less error prone option in my opinion.

You can also achieve what you will be manually doing in number 1 with the following script:
 
USER master
GO
ALTER DATABASE AdventureWorks
SET OFFLINE 
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;

Open in new window


You are getting the error because there are user's that are currently connected to the database (which may even be you, hence using the master DB when performing these actions). Whichever option you choose will result in an appropriate error for the user. Offline will probably be more understandable that "single user mode".
0
 
brutaldevCommented:
As knightEknight mentioned, you have to of course run the first portion if you are are doing a restore using the visual tools:
 
USER master
GO
ALTER DATABASE AdventureWorks
SET OFFLINE 
WITH ROLLBACK IMMEDIATE;

Open in new window

And then the second part to bring the database back online so users can connect to the database again once your restore is complete:
 
ALTER DATABASE AdventureWorks
SET ONLINE;

Open in new window


Excuse my copy and paste error in the previous post, you need to bring the database back online when using that script, not allowing multiple users.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GSGDBACommented:
Sorry, When database is offline. You will not be able to restore the database. As the live db will be ofline.

I would  suggest you to use the below query as the db is live and users require some time to close their app's.

1) create cursor and use sys.sysprocesses to fetch active users.
2) send dbmail alert to active users.( I'm considering that the app's are using nt id for SQL Access.
3) ALTER DATABASE AdventureWorks
SET SINGLE_USER
4) RESTORE DATABASE
5) ALTER DATABASE AdventureWorks
SET MULTI_USER;

This way your end users will not be affected.
To make it simple you can even try using Stored proc for the same
Your tsk will be easier to complete
0
 
brutaldevCommented:
@GSGDBA: You can do file level restores on an offline database since 2005: http://msdn.microsoft.com/en-us/library/ms191253(v=SQL.90).aspx

GSGDBA's comment is valid though, I wasn't aware of the method you are using to restore the database as there are a few ways.
0
 
GSGDBACommented:
For file level restore the database should be set to read-only.
Not offline. It is what mentioned in the link mentioned by you.

correct me if i am wrong?


0
 
GSGDBACommented:
oops, sorry. It is for simple recovery.

thanks.
0
 
Alpesh PatelAssistant ConsultantCommented:
Second one is the best, so you can restore it. If off line you cannot
0
 
MikeMCSDAuthor Commented:
thanks guys . .

I tried :

USER master      << should this be USE ??
GO
ALTER DATABASE AdventureWorks
SET OFFLINE            
.............

After going Off Line, the Menu Option for "Restore" is greyed-out - can't acess it.

I then tried :

USE master
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Gave the same "Exclusive access  . .  " message.

I finally got it to work by changing to Single User mode by using Properties, Options on the menu instead.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.