Skip to content

Latest commit

 

History

History
129 lines (90 loc) · 6.27 KB

sql-server-linux-restore-master-database-in-single-user-mode.md

File metadata and controls

129 lines (90 loc) · 6.27 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords
Restore master Database on SQL Server in Single-User Mode on Linux
Learn how to restore the master database using single-user mode in SQL Server on Linux.
rwestMSFT
randolphwest
11/18/2024
sql
configuration
how-to
template-how-to
linux-related-content
starting SQL Server on Linux, single-user mode
single-user mode [SQL Server] Linux

Restore the master database on Linux in single-user mode

[!INCLUDE SQL Server - Linux]

Under certain circumstances, you might need to restore the master database on an instance of [!INCLUDE ssNoVersion] in single-user mode on Linux. Scenarios include migrating to a new instance, or recovering from inconsistencies.

Note

[!INCLUDE ssnoversion-md] will automatically shut down after the restore is complete. This behavior is by design.

To restore the master database, you must start [!INCLUDE ssNoVersion] in single-user mode, by using the startup option -m from the command line.

For starting a [!INCLUDE ssNoVersion] instance in single-user mode on Windows, see Single-user mode for SQL Server.

Prerequisites

Starting [!INCLUDE ssNoVersion] in single-user mode enables any member of the local administrator group to connect to [!INCLUDE ssNoVersion] as a member of the sysadmin fixed server role. For more information, see Connect to SQL Server when system administrators are locked out.

When you start an instance of [!INCLUDE ssNoVersion] in single-user mode:

  • Only one user can connect to the server.
  • The CHECKPOINT process isn't executed. By default, it runs automatically at startup.

Stop the SQL Server service

  1. The following command stops the [!INCLUDE ssnoversion-md] instance if it's currently running:

    systemctl stop mssql-server

Change current user to mssql

  1. [!INCLUDE ssnoversion-md] on Linux runs under the mssql user, so you need to switch to this user first. You're prompted for the root password when running this command.

    su mssql

Start SQL Server in single-user mode

  1. When you use the -m option with SQLCMD, you can limit the connections to a specified client application (SQLCMD must be capitalized as shown):

    /opt/mssql/bin/sqlservr -m"SQLCMD"

    In the previous example, -m"SQLCMD" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you're starting [!INCLUDE ssNoVersion] in single-user mode to restore a master database.

  2. When SQL Server starts up, it generates several log entries. You can confirm that it's running in single-user mode by looking for the following lines in the output:

    [...]
2022-05-24 04:26:27.24 Server      Command Line Startup Parameters:
         -m "SQLCMD"
[...]
2022-05-24 04:26:28.20 spid8s      Warning ******************
2022-05-24 04:26:28.21 spid8s      SQL Server started in single-user mode. This an informational message only. No user action is required.

Connect to the SQL Server instance

  1. Use sqlcmd to connect to the SQL Server instance. After finishing the steps described in the Start SQL Server in single-user mode section, you can see that [!INCLUDE ssNoVersion] is running in interactive mode. Thus, you need to open a new terminal session to launch sqlcmd as follows.

    /opt/mssql-tools/bin/sqlcmd -S <ServerName> -U sa -P <password>

    In the previous example, <ServerName> is the name of the host running SQL Server if you're connecting remotely. If you're connecting directly on the host where SQL Server is running, you can skip this parameter, or use localhost. <StringPassword> is the password for the SA account.

Restore the master database

  1. Run the following commands inside sqlcmd. Remember that sqlcmd expects GO at the end of the script to execute it.

    USE [master];
GO

RESTORE DATABASE [master] FROM DISK = N'/var/opt/mssql/data/master.bak'
    WITH FILE = 1,
    MOVE N'master' TO N'/var/opt/mssql/data/master.mdf',
    MOVE N'mastlog' TO N'/var/opt/mssql/data/mastlog.ldf',
    NOUNLOAD, REPLACE, STATS = 5;
GO

    In the previous example, the path to the master database backup file is /var/opt/mssql/data/master.bak. You must replace this value with the correct path to your master database backup file.

  2. You should see output similar to the following example, if the restore is successful.

    Processed 456 pages for database 'master', file 'master' on file 1.
Processed 5 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

Restart the SQL Server service

  1. To restart SQL Server, run the following command.

    systemctl start mssql-server

Remarks

When you restore a master database backup, any existing user databases that were added to the instance after the backup was taken, won't be visible after restoring master. The files should still exist on the storage layer, so you need to manually reattach those user database files to bring those databases online. For more information, see Attach a Database.

Related content