Recently I had to restore master database on a SQL instance. One way to restore master database is
- Restore the master database as user database on same version of SQL.
- Detach the database from the instance.
- Copy the files to original instance.
- Restart the original instance.
Make sure the logical and physical file names are same as original when the database was restored in step 1.
Anyways, after I did the above steps, I noticed the master database was coming up in “single user” mode on the original instance ( Step 4).
I, eventually, realized that it was due to how I was detaching the database. So, when I was detaching the database, I selected the option to “drop connections” in the UI and SQL accomplishes this by putting the DB in single user mode before it detaches.
That’s the reason why it was coming up in single user mode on the original instance when the files were copied over.