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.
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
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak'
RESTORE DATABASE SQLAuthority FROM DISK = 'SQLAuthority.bak' WITH REPLACE
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*/
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE SQLAuthority
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 (http://blog.sqlauthority.com)