Rebuilding the system databases

The rebuild of the master database (or system databases) on a stand-alone and Failover Clustered (FC) SQL server instance is the same – https://msdn.microsoft.com/en-us/library/dd207003.aspx.

When you install a SQL Server instance you should check the collation for the instance which you expect it to be the default SQL_Latin1_General_CP1_CI_AS. But it’s not always. When you don’t check the collation you may end up in an install with a collation inherited from the one of the OS.

Here are the steps to rebuild the system databases.

  • Review the official documentation for system databases rebuild – https://msdn.microsoft.com/en-us/library/dd207003.aspx

  • Go to the node that is owning the clustering instance.

  • Open a command prompt and go to the directory where the setup.exe is located; which typically is in C:Program FilesMicrosoft SQL Server120Setup Bootstrap or go to the installation media (H: in the example below). Run the following command:

H:>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DomainNameDomainUser/SAPWD=xxxxxxxx /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Usually you’ll end up with an error message on the first try, but to avoid that see the Troubleshoot rebuild errors section in the reference from step 1). The successful rebuild ends up without a message.

If the instance was installed with mixed mode, then you must specify the password for the Domain user, otherwise you shouldn’t.

If you set up a password for the sa login then you have to re-set it. If you did some configuration (sys.sp_configure) changes, then you have to re-do them. Any changes previously done in in the master, msdb and model databases must be re-done.

If you created your tempdb with multiple files and especially on another disk then you’ll have to re-check the tempdb because it will be recreated in the default DATA folder created by the SQL server installation. You have to check the old files as well. Note of the reference in 1) that you have the options to use the tempdb data and log folders in the rebuild command.

At the end find and check the summary.txt log file in the Logs folder for SQL Server.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *

*