Learning about SQL Server can come from unexpected places, and sometimes it’s through encountering error messages. In this blog post, we’ll explore one such error message that a reader recently encountered: “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.”
Upon further investigation, it was discovered that this error message is not asking to restore the master database first. Instead, it is indicating that the restore command is running in the same database for which the restore is being attempted. To resolve this issue, we need to change the context to the master database before performing the restore.
Let’s take a look at an example to better understand this error:
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
In the above example, the SQL Server is unable to complete the restore operation because the user database has an active connection within the current session. To resolve this, we can use the T-SQL USE command to change the database connection to a different database before attempting the restore.
Here is the modified script with the necessary change:
/* Modified script */
USE MASTER
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuthority.bak' WITH REPLACE
GO
By changing the context to the master database before executing the restore command, we can successfully restore the database without encountering the error.
It’s important to note that this error is specific to the scenario where the restore command is being executed in the same database. If you encounter a similar error in your environment, make sure to check if the restore command is running in the correct context.
Have you ever faced a similar error in your SQL Server environments? Feel free to share your experiences and any additional insights in the comments section below.