SQL Server - Restore and Rebuild Master Database

Do you know if the master database in SQL Server becomes corrupted or unavailable, you won't be able to bring up the SQL Server? This is how important to know the steps to restore and rebuild master database so that you could address the concern when it is required.

SQL Server master database contains all the system-level information for the SQL Server instance. The information includes login accounts, linked servers, endpoints, database files path, instance configuration and other critical information.

In certain environment, restoring master database is included as part of disaster recovery strategy to retain all information in production (albeit there are other ways to do that like scripting out the login information using this script and deploy it in the disaster recovery instance, if login information is the only data to retain).

Restore master database

Let's examine the steps of restore master database. First, prepare the information below,

- master database backup file location
- SQL Server instance name
- Are the physical file path for current system databases same as the system databases file path when the backup was taken

In this example,
Backup file - D:\Backup\master_backup.bak
SQL Server instance - default instance (mssqlserver) on current host machine (localhost)
System database physical file path - Same

Here are the steps to restore master database. We will be using Powershell and SQL Server Configuration Manager (SSCM) for our steps.

1) Make sure the SQL Server service is stopped.
Get-Service | where {$_.name -eq "mssqlserver"}

If the service is running, stop the service.
net stop mssqlserver

For named instace (e.g. MSSQL`\test1, ` is to escape \ in Powershell)

2) Make a copy of all system databases and put it in another folder. In case the restore failed and other issues arise, we could easily replace these original system databases instead of rebuilding them as discussed below.

3) Restoring master database require SQL Server to be started in single-user mode. This could be achieve by using the -m (in SSCM) or /m (net start) parameter. We could also use /c (net start) to shorten the start up duration when starting from command prompt or Powershell.

net start mssqlserver /m /c

Important! You may see the result as service could not be started. You may also see the 'Change Pending' state for the service in SSCM. That is misleading. The service has started in single user mode. In case you couldn't figure it out and you want get the service back to stopped state, you could open the task manager, identify the relevant SQL Server service (in case you have other SQL instances running) and kill the process. It should returns the service to stopped state.

4) Use sqlcmd utility to execute command to restore master database. The parameter -S is to specify SQL Server instance. The -E parameter is to use trusted connection (the current Windows account).

& sqlcmd -S localhost -E

As you see, we could connect to the SQL Server instance. The & operator is used in powershell to call executable.

5) Restore master database

RESTORE DATABASE [master]
FROM DISK = 'D:\backup\master_backup.bak'
WITH CHECKSUM, STATS;
GO

Examine the result to verify the restore operation is successful. SQL Server should be stopped at this time. Check the status of the service, issue net stop if it is not stopped.

Get-Service | where {$_.name -eq "mssqlserver"}

6) Now, we could start the SQL Server service in the normal fashion (without single user mode). It is advised to start the SQL Server through SSCM for regular startup.

Go to SQL Server Configuration Manager (SSCM), locate the SQL Server service and start the service.

7) Bring up SSMS and connect to the SQL Server instance for verification.

Common Issue
If the current system databases physical path differs from the physical file path when the backup was taken, SQL Server may fail to start up. It is because SQL Server requires other system database like model database during the startup process and it is unable to find the system database files as the newly restored master database maps the files to different location. Trace flag T3608 may be utilized to start the service restricting SQL Server from starting other databases except master database. The meta data of physical path of system databases could then be modified in the master database. For example,

net start mssqlserver /m /c

If service started successfully,

& sqlcmd -S localhost -E

ALTER DATABASE [model]
MODIFY FILE
(
  NAME = modeldev, FILENAME = 'D:\MSSQLSERVER\DATA\model.mdf'
);
GO

ALTER DATABASE [model]
MODIFY FILE
(
  NAME = modellog, FILENAME = 'D:\MSSQLSERVER\DATA\modellog.ldf'
);
GO

Change other system databases if required. Stop the service and start the service in SSCM without any parameter. Once the service is started successfully, SSMS could be used to connect to the instance for verification.

When connecting with SSMS, if user databases are stuck in recovery pending state, it is very likely due to the user databases physical path meta data in master database is mapping to different location. Run the query to examine,

SELECT name, physical_name
FROM sys.master_files;

Verify if the physical name is pointing to the current user databases location. If not, use the alter database statement to change the meta data. You could then restart SQL Server service, or set the user database online. For example,

ALTER DATABASE [test]
SET ONLINE;

If all hell break loose and you couldn't get the server to start up. Replace the master database or all system database files (mdf/ldf) with the copies you made before the backup (from step 2). This should put you in the original environment before the backup. If not, the other choice may be rebuilding the system databases.

Rebuild master database
If the master database is corrupted, SQL Server may fail to start up successfully. In order to fix master database corruption, it may required to rebuild the master database. Note that rebuild operations drop and recreate all system databases including master, msdb, model and tempdb. All the data in these databases will be lost e.g. login, jobs, linked servers and etc.

Before rebuild, prepare information below,
- SQL Server instance name
- SQL Server account with sysadmin role and password
- SQL Server setup.exe file path

From SQL Server 2008 and above, you could locate setup.exe from the original installation media or local directory where you installed the SQL Server (e.g. 110\Setup Bootstrap\SQLServer2012, or 100\Setup Bootstrap\Release depending on SQL Server version)

Here is the rebuild command syntax,

setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS=account
[/SAPWD=password]
[/SQLCOLLATION=collation_name]

For /INSTANCENAME, it is the instance name. Use MSSQLSERVER for default instance. For /SQLSYSADMINACCOUNTS, includes windows account or groups to provision them as sysadmin role. You may need to enclose each account with double quotes and separate each account with space. For /SAPWD, this is the SA password. This information is required if the instance is setup as mixed authentication mode. Don't include this for windows authentication mode. If you want to change to different SQL Server instance collation (e.g. installed previously with incorrect collation), specify the new collation at /SQLCOLLATION. If this information is not supplied, the current collation will be used.

In this example,
SQL Server instance - default instance (mssqlserver) on current host machine (localhost)
SQL Server sysadmin account - test\user
SQL Server setup.exe file - C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

PS C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012>
& .\setup.exe `
>> /QUIET `
>> /ACTION=REBUILDDATABASE `
>> /INSTANCENAME=MSSQLSERVER `
>> /SQLSYSADMINACCOUNTS=test/user
>>

You may see this message too during rebuild,

Immediately after the rebuild is completed. You can check the status in Summary.txt in \110\Setup Bootstrap\Log\ or \100\Setup Bootstrap\Log\ depending on SQL Server version.

Once verified, connect to SQL Server instance and verify if SQL Server is accessible. Change server configuration, change database file path, install service pack, restore msdb/model database where applicable.

Side notes
If master database still accessible when corrupted, obtain these information before rebuild to gather information as reference for post-rebuild configuration.
- Server configuration settings (e.g. sys.configurations)
- Installed version and collation (e.g. SERVERPROPERTY system function)
- Location of databases files (e.g. sys.master_files)
- Information in system databases that you need to retain.

After rebuild, one option is to restore the system databases (master, msdb, model) from the backup to minimize the data loss. If master database is still accessible when it is corrupted, restoring from master database backup may often faster. Therefore, it is paramount to have a backup strategy on all databases including system database.

You may think restoring or rebuilding master database may not be as common compare to user database. However, corruption or disaster can occur anytime. As master database is so crucial in SQL Server environment, it is very important to get yourself familiar with the recovery step. Restoring master database could be daunting. Start practicing the master database restore and rebuild process in test environment today. When the dark force strikes, you will be all ready.