SQL SERVER – FIX: Msg 3102, Level 16, State 1 – RESTORE cannot process database ‘Name’ because it is in use by this session

Some of the simple learnings can come from unexpected places and this blog post is a classic example to that statement. Recently I got an email from one of my readers about the below error message:

Msg 3102, Level 16, State 1, Line 2
RESTORE cannot process database 'DBProductDevelopment' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

As I always do, I searched on my own blog using http://search.sqlauthority.com and found few earlier blogs where few of my readers reported the error which is the source of this blog.

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

One of my reader has asked interesting question related to this error:

Does it mean that I need to restore master database first and then restore this? How is that possible? Is there something wrong with error message?

Answer: No, the error message is not asking to restore master. All it is saying is that the restore command is running in the same database for which restore is being attempted. Error message is asking to change context to master database before performing restore.

Here is the repro of the problem:

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak'
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuthority.bak' WITH REPLACE
GO


As we can see, SQL Server can’t complete the restore operation because the user database has an active connection within our current session. We’ll need to change our database connection to a different database before we attempt to perform the restore. We can use the T-SQL USE command:

Here is the modified script where I have highlighted the change.

Here is the cleanup script.

/* clean up*/
USE MASTER
GO
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SQLAuthority
GO


Have you ever face similar error in your environments? Do you have any similar experience to share? Let me know over the comments section below.

Reference: Pinal Dave (https://blog.sqlauthority.com)