Today we will go over two step easy method to restore ‘master’ database. It is really unusal to have need of restoring the master database. In very rare situation this need should arises. It is important to have full backup of master database, without full backup file of master database it can not be restored.
It is necessary to start SQL Server in single user mode before master database can be restored. It is very easy to start SQL Server server in single user mode. Follow the tutorial SQL SERVER – Start SQL Server Instance in Single User Mode.
Once SQL Server instance is running in single user mode, immediately connect it using sqlcmd and run following command to restore the master database.
RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.bck' WITH REPLACE;
I have tested it couple of times and it has worked fine for me. If you encounter any error please leave a comment and I will do my best to solve it.
Reference : Pinal Dave (https://blog.sqlauthority.com)
I’m doing one scenario for corrupting the master database and recover it. In my scenario, I’ve corrupted the boot page of master database manually and tried to restore it from the backup by starting the SQL Server in single user mode. But, the system doesn’t let me start the server in single user mode. However, I’ve another instance of SQL Server 2016 and I also tried to restore master database as a user database (master_test) but it’s not being restore there as well. Moreover, I also tried to fix it from the setup bootstrap but it was also not working. Can you please advice, what should I do?
I am getting below error msg.
To restore the master database, the server must be running in single-user mode. For information on starting in single-user mode, see “How to: Start an Instance of SQL Server (sqlservr.exe)” in Books Online.