[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exclusive access could not be obtained ... Restore error

Posted on 2011-05-09
9
Medium Priority
?
769 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:MikeMCSD
9 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 35723077
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
 
LVL 11

Expert Comment

by:brutaldev
ID: 35723098
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
 
LVL 11

Assisted Solution

by:brutaldev
brutaldev earned 500 total points
ID: 35723144
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 3

Assisted Solution

by:GSGDBA
GSGDBA earned 500 total points
ID: 35723397
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
 
LVL 11

Expert Comment

by:brutaldev
ID: 35723558
@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
 
LVL 3

Expert Comment

by:GSGDBA
ID: 35723822
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
 
LVL 3

Expert Comment

by:GSGDBA
ID: 35723836
oops, sorry. It is for simple recovery.

thanks.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 500 total points
ID: 35728126
Second one is the best, so you can restore it. If off line you cannot
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 35738884
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

872 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