Restore the master database on Linux in single-user mode
Applies to: SQL Server - Linux
Under certain circumstances, you may need to restore the
master database on an instance of SQL Server in single-user mode on Linux. Scenarios include migrating to a new instance, or recovering from inconsistencies.
SQL Server will automatically shut down after the restore is complete. This behavior is by design.
master database requires starting SQL Server in single-user mode, by using the startup option
-m from the command line.
For starting a SQL Server instance in single-user mode on Windows, see Start SQL Server in single-user mode.
Starting SQL Server in single-user mode enables any member of the local administrator group to connect to SQL Server 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 SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process isn't executed. By default, it is executed automatically at startup.
Stop the SQL Server service
The following command stops the SQL Server instance if it's currently running:
systemctl stop mssql-server
Change current user to
SQL Server on Linux runs under the
mssqluser, so you'll need to switch to this user first. You'll be prompted for the
rootpassword when running this command.
Start SQL Server in single-user mode
When you use the
SQLCMD, you can limit the connections to a specified client application (
SQLCMDmust be capitalized as shown):
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 SQL Server in single-user mode to restore a
When SQL Server starts up, it generates several log entries. You can confirm that it is 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
Use sqlcmd to connect to the SQL Server instance:
/opt/mssql-tools/bin/sqlcmd -S <ServerName> -U sa -P <StrongPassword>
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
<StringPassword>is the password for the SA account.
Run the following commands inside sqlcmd. Remember that sqlcmd expects
GOat the end of the script to execute it.
use [master]; 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
masterdatabase backup file is
/var/opt/mssql/data/master.bak. You'll need to replace this value with the correct path to your
masterdatabase backup file.
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
To restart SQL Server, run the following command.
systemctl start mssql-server
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'll need to manually reattach those user database files to bring those databases online. See Attach a Database for more information.