Restore problem

Question

Restore problem

Bron Tamulis

Ten Centuries

Points: 1090
More actions
December 5, 2010 at 11:10 am

#231899

I'm trying to restore a SQL2008 database from a device (.bak)
I keep getting this message -
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I get this whether I use the right click restore or whether I use the SQL syntax.
I've started and stopped the database engine several times to no avail............
Any thoughts?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You are getting this error because some sessions are active on that database. Execute this query to find out which are those sessions and KILL them.

SELECT * FROM sys.dm_exec_requests WHERE db_name(database_id) = 'YourDBName'

Alternatively you can also use the following query to set the database to SINGLE_USER with ROLLBACK IMMEDIATE[/url] option to terminate all the connections in one go.

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

For altering database mode from multi user mode to single user mode please refer to How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Stamey SSCertifiable Points: 5600 More actions · Answer 3

I have also set a DB offline to get exclusive access to it for a restore. Sometimes you have a program that automatically re-establishes a DB connection, as it should, and you will never be able to get exclusive access for a restore if you cannot track down that program and stop it.

ALTER DATABASE [DATABASE NAME]

SET OFFLINE WITH ROLLBACK IMMEDIATE

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 4

Thanks for the replies - I was able to 'track' down the service that had connection established - I manually stopped and was able to restore my database.

Very educational forum - appreciate everyone's willingness to share expertise.