Master Database is crashed.How do I recover the same
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