Wednesday 5 March 2014

Master Database is crashed.How do I recover the same

If the master database is crashed SQL Server would not start and the same can be found in SQL Server Error log.So the first step would be to rebuild the master database for which read the below artcile

Rebuild Master

Once the master is rebuilded SQL Server will be up and running , but you will not be able to find your User databases or logins you have added to the SQL Server.This happens because this master datbase file is copied from Install path, so now latest backup of master database needs to be restored over the existing master database.

If its user database we can use the simple restore command to restore the latest backup,but can the same be done for master? Answer is 'No'.

So first SQL Server needs to be started in Single User Mode,for which stop the running SQL Server and goto command prompt and go to directory where sqlservr.exe (Bin Folder)
and type

sqlservr.exe -f -m
-f For Minmal configuration
-m Single User Mode

Now I can fire the same restore command as before which would be

RESTORE DATABASE master FROM DISK='D:\master_full_backup.bak'

On executing the above command you immediately notice a message saying SQL Server is terminating the process which means restore is successful and the server needs to be started again in normal mode for settings to take effect.

The master database has been successfully restored. Shutting down SQL Server.
The SQL Server is terminating this process



Now you can see you user databases and logins present as before.If there are jobs configured on the server msdb database also needs to be restored.

No comments:

Post a Comment