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

Question

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 1

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.

Answer 2

The answer was very simple,

Run this command to grab the LogicalNames,

RESTORE FILELISTONLY FROM DISK = 'C:\Users\MyUSer\Desktop\A.bak' 

Then Just put the in LogicalName in below,

RESTORE DATABASE B    FROM DISK = 'C:\Users\MyUSer\Desktop\A.bak'    WITH     MOVE 'LogicalName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Data\B.mdf',     MOVE 'LogicalName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Data\B.ldf' GO 

Note you might need to change the path. Helpful links,

How to restore to a different database in sql server?

http://technet.microsoft.com/en-us/library/ms186390.aspx