Rebuild System Databases in SQL Server

dbtut

5 years ago

Because of the problems that occur in the system databases, it is sometimes necessary to rebuild. Rebuild operation resets system databases and allows the system to be opened cleanly. But since the rebuild process will reset all the data in the system databases, you must first make sure that you have the latest backups of the system databases.

If you rebuild your system databases while you have no backup, you will lose all your logins and jobs. And you can’t see your databases on instance when you open instance. Your Instance will be like a newly installed instance.

However, you can add your databases to this new instance by attaching the database files. You can find out how to attach your database files in the article “How To Change The Disk Of Database Files On SQL Server(Detach Attach & Backup Restore Methods)“.

Below, I explained what should be done before and after the rebuild and the effects of the rebuild process;

Model database does not cause problems even if reset.
If the Msdb database is reset, losing your jobs can cause trouble for you. Therefore, I suggest that you keep the create scripts of your jobs in a separate place.
If you lose the master database, you will lose the login information and the path information of the database files. Therefore it is useful to store the create scripts of logins on the instance with their “SIDs”. And you should also store database files’s path informations. I explained how to get the create scripts of the logins on an instance with the “SIDs” in the article “How To Move Logins To Another Instance(sp_help_revlogin)“.
You will need to reinstall SQL server service packs and hotfixes after Rebuild. So I suggest you keep the following script result in order to be prepared for such situations.

SELECT SERVERPROPERTY('ProductVersion ') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ResourceVersion') AS ResourceVersion, SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime, SERVERPROPERTY('Collation') AS Collation;

1
2
3
4
5
6

SELECT
SERVERPROPERTY('ProductVersion ') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;

You should also keep the current path of all your database files using the following script. In this way, if you need to attach databases, you will know which database files are in which paths.

SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

1
2
3

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

I recommend that you back up your server level configurations with the following script.

SELECT * FROM sys.configurations;

1

SELECT * FROM sys.configurations;

Before you start the Rebuild process, if you do not have a backup, I recommend that you copy the master, msdb and model database files to another folder or back them up.

So how to rebuild the system databases?

First, you must have the sql server setup file on the server. You should then run the cmd command prompt as administrator.

Then on the cmd command prompt, go to the path where the sql server setup file is.

cd C:\SQLServerSetup

1	cd C:\SQLServerSetup

You should then run the following script. You can also use the following script to change the server collation in an instance. But as I mentioned above, note that these operations will reset all the information on the instance.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=your_instance_name /SQLSYSADMINACCOUNTS=a_sysadmin_user 
/SAPWD='user_password' /SQLCOLLATION=server_collation_name

1 2	Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=your_instance_name /SQLSYSADMINACCOUNTS=a_sysadmin_user /SAPWD='user_password' /SQLCOLLATION=server_collation_name

After reboot is finished, you will find an empty instance when you open instance. After this stage, you will restore the master, model and msdb databases one by one or perform the operations described in this article.

To perform the restore of the master database;

I suggest you read my articles “Restore master database in SQL Server“. You should also restore your msdb and model databases.

You can perform the restore operations of the msdb and model database as normal database restore. There is no extra setting. To restore the msdb database, you must stop the SQL Server Agent Service from SQL Server Configuration Manager.

You can find the restore scripts of msdb and model system databases below.

Restore model database;

RESTORE DATABASE model FROM DISK = 'C:\MSSQL\model_backup.bak' WITH REPLACE;

1	RESTORE DATABASE model FROM DISK = 'C:\MSSQL\model_backup.bak' WITH REPLACE;

Restore msdb database;

RESTORE DATABASE msdb FROM DISK = 'C:\MSSQL\msdb_backup.bak' WITH REPLACE;

1	RESTORE DATABASE msdb FROM DISK = 'C:\MSSQL\msdb_backup.bak' WITH REPLACE;

If you don’t have backups of the model and msdb databases, stop the sql server service and paste the files you copied before the rebuild onto the existing msdb and model database files(overwrite, but always take backup of every thing). When you open the service again, you can see your jobs. But I would like to point out that the most healthy method is to return from backup.

Note: You can not restore tempdb database. Because the tempdb database is re-created by the sql server each time after the SQL Server service starts. You may want to read the article “How To Move The Tempdb Database To Another Disk”