Skip to content

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

Latest commit

 

History

History
66 lines (50 loc) · 4.91 KB

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

File metadata and controls

66 lines (50 loc) · 4.91 KB
title Restore the master Database (Transact-SQL) | Microsoft Docs
description This article shows you how to restore the master database in SQL Server from a full database backup by using Transact-SQL.
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service backup-restore
ms.reviewer
ms.technology backup-restore
ms.topic conceptual
helpviewer_keywords
master database [SQL Server], restoring
ms.assetid c83d802c-e84e-4458-b3ca-173d9ba32f73
author cawrites
ms.author chadam

Restore the master Database (Transact-SQL)

[!INCLUDE SQL Server]

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

Warning

In the event of disaster recovery, the instance to which the Master database is being restored should be as close to an exact match to the original as possible. At minimum, the instance Master is being restored to should be the same version, edition, and patch levels of SQL Server, the same selection of features installed, and the same external configuration (hostname, cluster membership, etc). Doing otherwise may result in an undefined SQL Server Instance behavior with inconsistent feature support, and is not guaranteed to be viable.

To restore the master database

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

    For information about how to specify the single-user startup parameter (-m), see Configure Server Startup Options (SQL Server Configuration Manager).

  2. To restore a full database backup of master, use the following RESTORE DATABASE[!INCLUDEtsql] statement:

    RESTORE DATABASE master FROM <backup_device> WITH REPLACE

    The REPLACE option instructs [!INCLUDEssNoVersion] 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 the sqlcmd Utility.

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

  3. Restart the server instance and continue other recovery steps such as restoring other databases, attaching databases, and correcting user mismatches.

Example

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.

Note

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

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

See Also

Complete Database Restores (Simple Recovery Model)
Complete Database Restores (Full Recovery Model)
Troubleshoot Orphaned Users (SQL Server)
Database Detach and Attach (SQL Server)
Rebuild System Databases
Database Engine Service Startup Options
SQL Server Configuration Manager
Back Up and Restore of System Databases (SQL Server)
RESTORE (Transact-SQL)
Start SQL Server in Single-User Mode