How to Restore a SQL Server Database from a .bak File (SSMS, T-SQL and Linux)

Applies to: SQL Server 2016 to 2022 and later on Windows and Linux, with SQL Server Management Studio (SSMS) or sqlcmd; commands and messages from Microsoft’s documentation, as SQL Server images don’t run on our arm64 test lab

To restore a SQL Server database from a .bak file, in SSMS right-click Databases, choose Restore Database, select Device, add the .bak file and select OK. In T-SQL it is RESTORE DATABASE ... FROM DISK = '...', usually with MOVE to put the files in the right folders on the new server. One hard rule: a backup can only be restored on the same or a newer SQL Server version, never an older one.

Before you start

• Copy the .bak file to a local disk of the SQL Server machine, in a folder the SQL Server service account can read.
• Check the free disk space. RESTORE FILELISTONLY (below) shows each file’s Size, its current size in bytes, which can be much larger than the backup itself.
• If the database uses Transparent Data Encryption, you need its certificate on the new server first; Microsoft is clear that without it the restore is impossible.

Method 1: SSMS

1. In Object Explorer, connect to the instance, right-click Databases and select Restore Database...
2. On the General page, under Source, choose Device and select the ... button. Keep File as the media type, select Add, pick the .bak file and select OK. (Database only lists backups this server made itself.)
3. Under Destination, keep or change the database name. A new name creates a separate copy; then check on the Files page that its file names don’t clash with the existing database’s files.
4. On the Files page, check the paths under Restore the database files as. If the backup came from another server, point them to this server’s data and log folders.
5. On the Options page, tick Overwrite the existing database (WITH REPLACE) only when you really want to replace a database. Tick Close existing connections to destination database if the database is in use; SSMS then switches it to single-user mode during the restore. Leave Recovery state at RESTORE WITH RECOVERY unless you still have log backups to apply.
6. Select OK.

New to SSMS? See install SQL Server Management Studio and connect.

Method 2: T-SQL

First list the logical file names inside the backup:

RESTORE FILELISTONLY FROM DISK = N'D:\Backup\ShopDB.bak';

The LogicalName column gives names such as ShopDB and ShopDB_log. Use them with MOVE:

RESTORE DATABASE ShopDB
FROM DISK = N'D:\Backup\ShopDB.bak'
WITH MOVE N'ShopDB'     TO N'D:\SQLData\ShopDB.mdf',
     MOVE N'ShopDB_log' TO N'E:\SQLLogs\ShopDB_log.ldf',
     RECOVERY, STATS = 10;

STATS = 10 prints progress every 10 percent. RESTORE HEADERONLY shows which backups a file contains, and RESTORE VERIFYONLY checks that the backup set is complete and readable without restoring it (it doesn’t check the data structure inside).

SQL Server on Linux

Microsoft’s steps: put the file in a subfolder of /var/opt/mssql, which the mssql user owns, then restore with sqlcmd and Linux paths:

sudo mkdir -p /var/opt/mssql/backup
sudo mv ~/ShopDB.bak /var/opt/mssql/backup/
sqlcmd -S localhost -U sa

RESTORE DATABASE ShopDB FROM DISK = '/var/opt/mssql/backup/ShopDB.bak'
WITH MOVE 'ShopDB' TO '/var/opt/mssql/data/ShopDB.mdf',
     MOVE 'ShopDB_log' TO '/var/opt/mssql/data/ShopDB_log.ldf';
GO

A backup made on Windows restores on Linux this way; the MOVE clauses replace the Windows paths stored in the backup.

Common errors

• Exclusive access could not be obtained because the database is in use. (3101): close the connections, with the SSMS option above or ALTER DATABASE ShopDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before the restore and SET MULTI_USER after it.
• The backup set holds a backup of a database other than the existing 'ShopDB' database. (3154): you are restoring over a different database. Pick a new name, or add REPLACE if overwriting is intended.
• The tail of the log for the database "ShopDB" has not been backed up. (3159): the database you are overwriting has transaction log records that were never backed up. Back up the log first if you need that work; otherwise use WITH REPLACE, as the message itself suggests.
• The database was backed up on a server running version ... That version is incompatible with this server (3169): the backup is from a newer SQL Server. Restore it on a server running the same or a newer version, or get a backup made on a compatible version.
• Cannot open backup device '...'. Operating system error 5 (3201): error 5 is access denied: the SQL Server service account can’t read that folder. Move the file or grant it read access.
• The media family on device '...' is incorrectly formed. (3241): SQL Server can’t read the file as a backup. Check that the copy is complete (compare the file size or a checksum with the original) and run RESTORE VERIFYONLY on it.

After the restore

Logins live in the server, not in the database. On a new server, database users can end up without a matching login (orphaned users). Create the login, then link it, as Microsoft recommends instead of the old sp_change_users_login:

USE ShopDB;
ALTER USER appuser WITH LOGIN = appuser;

If you lost the sa password on the new server, see reset the SQL Server sa password; if the service won’t start, SQL Server service is not starting. To run SQL Server you need a server with administrator access, such as a Windows VPS.

Official documentation: restore a database backup using SSMS, RESTORE statements and restore on Linux.