SQL Server Restore Master DB (SQL DBA)

Restoring master DB is as simple as:

• ·         Backup master
•      Start the server with the -m startup parameter (which will start server in single user mode)
•      Restore master backup by using SQLCMD command line
•      The server shuts down automatically when the restore ends
•      Remove the -m startup parameter and then restart the server
•      If any databases were created after the master backup, reattach them
•      If any server-scoped objects were created after the master backup, recreate them

Now let’s see everything in detail

1.       Backup master

  Backup Master Database normally as u backup other databases

2.       Start the server with the -m startup parameter.

• Go to SQL Server Configuration Manager Select SQL Server Services from left panel

·         

From right panel select the server which need to be start with single user mode

Go to properties à in Startup Parameter property add  -m;  in starting à ok

  You’ll get the message that you need to restart services .

  Again select same SQL Server from Configuration Manager and Restart Service

   Close your SSMS window.

3.       Restore your master backup by using SQLCMD command line

·         Open command line à Type  sqlcmd àenter

·          In next line issue restore command for master database

·         Restore database master from disk = ‘c:\backups\backup_master_full.bak’

 go

4.       The server shuts down automatically when the restore ends

5.       Remove the -m startup parameter and then restart the server

6.       If any databases were created after the master backup, reattach them

7.       If any server-scoped objects were created after the master backup, recreate them

Other DBA related blogs
windows services maintenance
automated backup with logs
tips for DBA
getting blocked sessions
sqlcmd export data to txt file