Problem
Sometimes it happens that you cannot start a SQL Server instance because a system database (or some of them) is corrupted. In this case, it is necessary to rebuild the system databases. Additionally, we need to rebuild the system databases if we want to change the default server-level collation. In this tip, we are going to discuss how to rebuild the system databases and what follow-up steps are required after having the databases rebuilt.
Solution
Suppose our SQL Server instance failed to start due to a corrupt system database. In the example below, we tried to start the SQL Server instance from SQL Server Configuration Manager and got the following error message:
Well, as the message suggests, let’s see what we can find in the error logs. If we open the ERRORLOG file, we can find that there is a problem with the master database files:
Let’s assume that our master database files are corrupt and therefore, rebuilding is necessary. Before starting the process of rebuilding, it is important to realize that the rebuild operation drops and re-creates the system databases. Therefore, any changes made in the system databases will be lost and we need database backups to restore the databases after the rebuild if we want to recover any of those changes that were made. Additionally, this operation rebuilds the system databases altogether and it is not possible to specify a single system database, such as the master.
The process described below does not rebuild the resource database. Rebuilding the resource database is a separate process and will be discussed in another article.
It is also important to mention that if the instance cannot be started due to a damaged tempdb database, it is possible to rebuild it separately, without rebuilding other system databases.
In terms of permissions, the sysadmin fixed server role membership is required in order to perform the rebuild operation. The rebuild operation uses system database template files, so we need to make sure that the copies of these files exist on the local machine. The default location of these templates is:
DiskDriveLetter:\Program Files\Microsoft SQL Server\MSSQL<number>.MSSQLSERVER\MSSQL\Binn\Templates
In our case, it is:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates
If we open this folder, we can see that all the necessary template files are present:
If these templates do not exist, we need to copy them from the installation media or repair the instance from the setup. After considering these pre-requirements, we move on to the rebuild process. We should locate the setup.exe file and go to the directory of that file in the command prompt. The default location of that file is:
C:\Program Files\Microsoft SQL Server\<num>\Setup Bootstrap\SQLServer<versionNum>
We use SQL Server 2014 in our example and the path is:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
To open a command prompt, we can type cmd in Windows search and this will open the command prompt. Then, in the command prompt, we change to the directory where the setup.exe is located by typing:
cd C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
After that, we run the setup with the following options (need to update the parameters for your server) and press enter:
setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators /SAPWD=yourPassword
Thus, we issued the rebuild database command, specify the default instance as the instance name (MSSQLSERVER), use BUILTIN\Administrators as the SQL sysadmin accounts, and specify the password of the sa user. We do not specify a collation and in that case, the current collation of the server will be used.
If the process successfully completes, no error messages will appear and a new line for entering a command will appear like in the picture below:
We can read the log of the rebuild process in the Summary.txt file located in the “C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log” folder. If we open the file, we can see that our rebuild process is successfully completed:
After the rebuild, we can start the instance from SQL Server Configuration Manager:
We can see that the instance successfully started. The rebuild operation creates the system databases in their original location. So, let’s see where they are created by running the following code in SQL Server Management Studio:
SELECT name AS FileLogicalName, physical_name AS FileLocation FROM sys.master_files WHERE database_id BETWEEN 1 AND 4
Below, we can see the current location of the system database files, which should be the original location of these files as assumed:
However, before starting the rebuild process, in the ERRORLOG file we saw “C:\MSSQL\Master-Databases” as the master database files’ path. Well, this is because after moving the system database files the SQLDataRoot was not changed in the registry (which is not recommended and can cause patching and upgrading failures):
So, saying the original location is considered the location mentioned in the SQLDataRoot and not the location where the system database files are physically located. If we want to move the system database files to the previous location, we can do that and also update the registry to avoid failures and this kind of confusing situation.
As mentioned above, after the rebuild, the system databases are dropped and re-created, so all user-made changes are lost. Therefore, we need to restore the most recent backups of the master, model, and msdb databases. If we do not have backups, we need to manually create all missing objects such as SQL logins, endpoints, and so on.
Conclusion
In conclusion, if your instance does not start because of a damaged system database, this can be fixed by rebuilding these databases. This operation, however, rebuilds the initial versions of the system databases, so all changes made in these databases will be lost. So, to get the changes back, the most recent versions of the master, msdb, and model databases should be restored.
Next Steps
Please find more information by following the links below:
