The need to rebuild Master database comes when it is corrupted and we are not able to start SQL Server Service and getting error like this:
2016-01-01 21:21:20.19 spid5s Starting up database ‘master’.
2016-01-01 21:21:20.23 spid5s Error: 5172, Severity: 16, State: 15.
2016-01-01 21:21:20.23 spid5s The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf’ is not a valid database file header. The PageAudit property is incorrect.
2016-01-01 21:21:20.25 spid5s Error: 5173, Severity: 16, State: 1.
2016-01-01 21:21:20.25 spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
2016-01-01 21:21:20.26 spid5s Error: 5173, Severity: 16, State: 1.
2016-01-01 21:21:20.26 spid5s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Or any other error specifying that Master database is corrupt.
If any user database gets corrupt than we can restore that database, as the services will not be hampered but that’s not the case with Master database.
We don’t have any other option left than rebuilding the Master database and then restoring the latest backup on it.
- Open Command prompt with administrator privileges. By right clicking on cmd and clicking “Run as administrator”.
- Now we have to go to the setup directory in cmd.
The “setup” is in the C:\Program Files\Microsoft SQL Server\110\ Setup bootstrap\SQLSERVER2012 directory.
The location of “Setup.exe” in different version of SQL Server is almost same (keeping in mind that the directory for 2005 is 90, 2008 is 100, 2012 is 110, 2014 is 120 and 2016 is 130).
As I am working in SQL Server 2012 that’s why the location of setup.exe is “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012”.
To go to this directory use ‘cd’ command.
- To rebuild the master database, we have to use ‘setup’ with some additional parameters.
/SQLSYSADMINACCOUNTS= Provide the account name which have administrator privileges.
/SAPWD= <Strong Password>If you are using mixed authentication mode than provide a strong sa password.
/Q For silent mode without user interaction.
/INSTANCENAME= <Name of the instance of SQL Server if its default than MSSQLSERVER>
As I am rebuilding default instance that is mixed authentication mode the command I need to run is:
.\setup /ACTION=REBUILDDATBASE /SQLSYSADMINACCOUNTS=WIN2\SQL /INSTANCENAME=MSSQLSERVER
If you don’t get any message and just moves to the next line that means everything’s good and the Master database has been rebuilt.
To verify check the SQL Server Services from configuration manager. It will be running without any issue this time.
But when you open SQL Server Management studio and will find that all the user databases are gone.
That’s because as your master database has been rebuilt there is no information in it about the user databases. As it’s the master database that holds this information.
Now we have to restore the master database from a backup.
- To restore master database, we have to open the SQL Server services in the single user mode. For that add ‘-m’ in the startup parameter of SQL Server service.
Open SQL Server Configuration manager. Right Click the instance and click properties. Go to Startup parameters tab. In ‘Specify a startup parameter’ write ‘–m’. And click ‘Add’.
- We have to use the SQLCMD utility for restoring the master database. Open command prompt with administrative privileges. And write SQLCMD if its default instance but if it is named instance than we have to provide the name of the instance by using –S flag.
Now Restore the master database.
RESTORE DATABASE master
AS you can see when taking backup I named the backup file as .bac (By mistake). But it still it restores successfully as the extension doesn’t matter.
Remove –m from the startup parameter and start the SQL Server Services. Check SSMS for the verification.
Now we have all our beloved user databases. J