Backup/Restore from different database causing Restore failed exclusive access could not be obtained

sql sql-server sql-server-2012
user960567 picture user960567 · Dec 19, 2013 · Viewed 38.9k times · Source

I have a database A. I have taken a backup of database A called A.bak. I created a new database B. Now, I right click and Restore B from A.bak. In the Restore Dialog, I checked overwrite existing database and change the LogicalFileName from C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\A.mdf to C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\B.mdf and did the same with ldf file. But I am getting

Exclusive access could not be obtained because the database is in use.

Also tried,

ALTER DATABASE [B] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Also sp_who2, there was no existing connection of [B]

Answer

Prof Von Lemongargle picture Prof Von Lemongargle · Mar 12, 2014

A cause for the attempt to get exclusive access comes from the options page of the restore dialog in SQL Server 2012 Management Studio. It will turn on tail-log and leave in restoring state options for the SOURCE database. So, it will try to gain exclusive access to the source database (in this case A) in order to perform this action. If you turn off the tail log option, you will find that the operation works much more smoothly.

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More