Rebuilding system databases in SQL Server

SQL Server has few system databases, which help it to manage the system. They are:

• master
• msdb
• model
• tempdb
• resource

tempdb is rebuilt every time your restart your SQL server, so there is not issues there.

In case of a corruption, how do you re-create all those system databases? One approach is restoring backup for those system databases. But if you don't backup system databases or backups also contains the corruption, you have no other option, but recreate them.

However, note that if you re-create them, you will lost any custom database object you created in those databases (i.e. it is like doing a factory reset) hence you will lost things like customized settings and agent jobs.

Prep

If you want current settings to be applied after restore/rebuild, you need to do following pre-requisite tasks:

        1. Note down current server configuration. You can fetch current server configuration by running following SQL (you probably want to extract this to a excel sheet or to a user database):

            SELECT * FROM sys.configurations;

        2.   If you hoping to restore to latest hotfixes, note down current hotfixes and current collation:

            SELECT

                    SERVERPROPERTY('ProductVersion ') AS ProductVersion,

                    SERVERPROPERTY('ProductLevel') AS ProductLevel,

                    SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

                    SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

                    SERVERPROPERTY('Collation') AS Collation;

        3. Following query shows current database file locations. Note this down if you are not using the default locations.

        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'));

Rebuild

In order to rebuild, you need SQL Server installation media. There fore please mount or insert the installation media into the server you are rebuilding.

Then run following command on command line (make sure you are on the correct folder):

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]

There are many other parameters you can specify. For more detail please refer to Microsoft documentation - https://learn.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver16

Once rebuild completed, you can put your old settings back if necessary.