Skip to content

Latest commit

 

History

History
86 lines (62 loc) · 5.33 KB

restore-the-master-database-transact-sql.md

File metadata and controls

86 lines (62 loc) · 5.33 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Restore the master Database (Transact-SQL)
This article shows you how to restore the master database in SQL Server from a full database backup by using Transact-SQL.
MashaMSFT
mathoma
randolphwest
04/17/2025
sql
backup-restore
how-to
master database [SQL Server], restoring

Restore the master database (Transact-SQL)

[!INCLUDE SQL Server]

This article explains how to restore the master database from a full database backup.

Warning

In the event of disaster recovery, the instance where the master database is being restored to should be as close to an exact match to the original as possible. At a minimum, this recovery instance should be the same version, edition, and patch level, and it should have the same selection of features and the same external configuration (hostname, cluster membership, and so on) as the original instance. Doing otherwise can result in undefined SQL Server instance behavior, with inconsistent feature support, and isn't guaranteed to be viable.

Restore the master database

  1. Start the server instance in single-user mode.

    You can start SQL Server by either using the -m or -f startup parameters. For more information about startup parameters, see Database Engine Service startup options.

    From a command prompt, run the following commands, and make sure you replace MSSQLXX.instance with the appropriate folder name:

    cd C:\Program Files\Microsoft SQL Server\MSSQLXX.instance\MSSQL\Binn
sqlservr -c -f -s <instance> -mSQLCMD
    • The -mSQLCMD parameter ensures that only sqlcmd can connect to SQL Server.
    • For a default instance name, use -s MSSQLSERVER
    • -c starts SQL Server as an application to bypass Service Control Manager to shorten startup time

    If the SQL Server instance can't start due to a damaged master database, you must rebuild the system databases first. For more information, see Rebuild system databases.

  2. Connect to SQL Server using sqlcmd from another command prompt window:

    sqlcmd -S <instance> -E -d master

  3. To restore a full database backup of master, use the following RESTORE Statements[!INCLUDE tsql] statement:

    RESTORE DATABASE master FROM <backup_device> WITH REPLACE;

    The REPLACE option instructs [!INCLUDE ssNoVersion] to restore the specified database even when a database of the same name already exists. The existing database, if any, is deleted. In single-user mode, we recommend that you enter the RESTORE DATABASE statement in the sqlcmd utility. For more information, see Use sqlcmd.

    [!IMPORTANT]
    After master is restored, the instance of [!INCLUDE ssNoVersion] shuts down and terminates the sqlcmd process. Before you restart the server instance, remove the single-user startup parameter. For more information, see SQL Server Configuration Manager: Configure server startup options.

  4. Restart the server instance normally as a service, without using any startup parameters.

  5. Continue other recovery steps such as restoring other databases, attaching databases, and correcting user mismatches.

Examples

The following example restores the master database on the default server instance. The example assumes that the server instance is already running in single-user mode. The example starts sqlcmd and executes a RESTORE DATABASE statement that restores a full database backup of master from a disk device: Z:\SQLServerBackups\master.bak.

For a named instance, the sqlcmd command must specify the -S<computer-name>\<instance-name> option.

C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;
2> GO

Related content