Category: Restore

Master database in single user mode after restore

Recently I had to restore master database on a SQL instance. One way to restore master database is

  1. Restore the master database as user database on same version of SQL.
  2. Detach the database from the instance.
  3. Copy the files to original instance.
  4. 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.

Advertisement

Restore Master Database

Sometimes, you need to restore the master database. Restoring master db is different than other user databases. I followed the below instructions while doing the restore .
1.Set the server in single user mode. This can be done by adding -m to the startup parameters in the configuration manager(right click on the sql service and go to properties) and restart the sql server.
2.Disable the sql server agent.
3.Go to command prompt and connect to the sql server as SQLCMD -E.If your windows account has sysadmin rights on the server, you can get through it else, specify the credentials. Also, if it is not the default instance, you will need to mention the servername\InstanceName.
Example : SQLCMD -S SERVERNAME\INSTANCE -U LOGINNAME -P MYPASSWORD
4.After connecting to the server, you can run the below
RESTORE DATABASE MASTER FROM DISK = ‘E:\BACKUPPATH\BACKUPFILE.BAK with REPLACE’
This will replace the master database with your backup copy. There are couple of other ways to do but I find this one easy to deal in these scenarios. As a tip one other option is using DAC(Dedicated Admin Connection).

Commonly Used Restore Commands

Restore is the generic T-sql command we use to restore database backup files. We can also use restore command for other purposes such as validating the backups, find the database files in the backupset etc. Below are the commonly used restore functions other than the obvious restore database.

1.Validating the Backup :

Restore VerifyOnly From Disk= 'E:\BackupFilePath\BackupFile.Bak'

2.Find the Backup Files in the BackupSet

Restore HeaderOnly From Disk = 'E:\BackupFilePath\BackupFile.Bak'

3.Find the Database Files in the Backupset

Restore FileListOnly From Disk = 'E:\BackupFilePath\BackupFile.Bak'

There are also other restore command syntaxes we can use but I refer these more often.