There are multiple reasons you may want the master database to be restored. While a DBA understands clearly the backup strategies, the backup of the master database is very critical. In most cases, restoring the master database will be a rare scenario. However, including all the system databases in our regular backup strategy is a must.
Restoring
a master database is not a straight method like any other normal backup restore
we perform. As the name suggests, the master database bags all the
information about the server including databases, database files, and locations,
configuration settings, login information, and more.
Being
said that the master database in most cases is rare, it is always recommended
for the DBAs to keep the restore process handy. Any of our databases at any moment
can fall and is the best option to make a practice of disaster recovery.
Let’s look at the steps to restore the master database.
1. Start SQL Server Instance In Single Mode
Follow
the path for starting SQL Server Instance in Single User Mode.
SQL Server Configuration Manager --> Click on SQL Server Services --> Click on Startup Parameter Option --> Type “-m” in the specified field --> Click on Add --> OK
By doing this, the SQL Server Instance will be set to Single User Mode. Now, restart the SQL Server Services to get the effects in place.
2. Open Command Prompt To Restore Master Database
Open the command prompt and type >sqlcmd. This will navigate to enter the SQL commands.
RESTORE DATABASE master FROM DISK = 'C:\BackupRestore\master.bak' WITH REPLACE;
3. Remove “-m” Startup Parameter From SQL Server Configuration Manager
Sometimes
we may be in a hurry to fix it and may forget to revert the startup parameter. Once
the master database is restored, the SQL Server shuts down and terminates the sqlcmd
process. Once the restart has been finished, before you open the SQL Server
Instance, revert to multi-user mode by removing the startup parameter.
Please refer to the MSDN HERE for more details.
0 comments:
Post a Comment