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:

Solarwinds

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

SQL SERVER - FIX: Msg 3102, Level 16, State 1 - RESTORE cannot process database 'Name' because it is in use by this session restore-error-01

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.

SQL SERVER - FIX: Msg 3102, Level 16, State 1 - RESTORE cannot process database 'Name' because it is in use by this session restore-error-02

/* 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)

Solarwinds
Previous Post
SQL SERVER – FIX – Error 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use
Next Post
Interview Question of the Week #023 – Error Handling with TRY…CATCH

Related Posts

3 Comments. Leave new

  • nakulvachhrajani
    June 6, 2015 3:12 pm

    This one is like changing the wheel of a vehicle while sitting in/on the vehicle itself (or changing the foundation of a building while you are within the building itself).

    This post, however is necessary – I have seen a lot of people make the same mistake myself. As a solution, we either tell our developers to use the UI for the backup/restores (after closing all open windows), or to use a SQLCMD based script where all they need to do is provide the location of the backup files.

    Reply
  • Thank you for the help, you have solve my problem.

    Reply

Leave a Reply

Menu