Restore a SQL Server Database (T-SQL)

If you use SQL Server Management Studio (SSMS) or some other GUI to manage your databases, you might be used to backing up and restoring databases using “point and click”.

Usually this involves right-clicking on the database and selecting Restore or similar, then following the prompts (for example, when restoring a database in Azure Data Studio).

But if you ever need to do it with T-SQL, you can use the RESTORE DATABASE statement.

Example

Here’s a basic example:

RESTORE DATABASE World
  FROM DISK = N'/var/opt/mssql/Bak/World.bak' 
  WITH FILE = 1;

This is almost as simple as it can get. The RESTORE DATABASE statement has quite a complex syntax (as with most things T-SQL), but this statement is sufficient for a basic, default restore operation.

In this case, I restored a database called World from a .bak file. I used FROM DISK to specify that it was from a .bak file, and I provided the full path to that file. Other options here include FROM TAPE and FROM URL.

I’ve also included WITH FILE = 1 here but that’s the default value anyway. This clause specifies the backup set file number to use. That is, which backup set to use in the file (a file can have multiple backup sets).

Get a List of Backup Sets

You can use RESTORE HEADERONLY to get a list of backup sets in the file. More specifically, it returns a result set of backup header information for all backup sets.

Example:

RESTORE HEADERONLY
FROM DISK = N'/var/opt/mssql/Bak/WideWorldImporters-Full.bak';

This returns a lot of columns so I won’t present them all here.

One of the columns is called Position. This is to be used with the FILE = option when restoring the database.

Example with More Options

Here’s an example with more options:

RESTORE DATABASE [WideWorldImporters] 
  FROM DISK = N'/var/opt/mssql/Bak/WideWorldImporters-Full.bak' 
  WITH FILE = 1,  
  MOVE N'WWI_Primary' TO N'/var/opt/mssql/data/WideWorldImporters.mdf',  
  MOVE N'WWI_UserData' TO N'/var/opt/mssql/data/WideWorldImporters_UserData.ndf',  
  MOVE N'WWI_Log' TO N'/var/opt/mssql/data/WideWorldImporters.ldf',  
  MOVE N'WWI_InMemory_Data_1' TO N'/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1',  
  NOUNLOAD,  
  STATS = 5;

This is actually the script that Azure Data Studio generated for me when I used the GUI interface to initiate a restore operation. When you do that, Azure Data Studio gives you the option of running the restore immediately, or generating a script with the T-SQL code that you can run later.

In this case, the script uses the MOVE argument to move each logical file name in the backup file, to the specified physical file location on the operating system. In this case, the .bak file used a different physical file location (and used Windows file paths) and so this had to change to suit my system. See below for an explanation of how to get this info.

The NOUNLOAD is actually a tape option. It ensures that the tape is not unloaded from the drive once the restore is complete. Given I wasn’t restoring from tape, this option was ignored.

The STATS argument allows you to gauge the progress of the restore operation. It specifies that a message will be displayed each time another percentage completes. If you don’t include a percentage value here, SQL Server will display a message after each 10% is completed.

RESTORE FILELISTONLY

If you wanted to create a statement like the earlier one, which uses the MOVE argument to move each logical file name in the backup file, to the specified physical file location on the operating system, you can use RESTORE FILELISTONLY to return the logical file names (and more).

RESTORE FILELISTONLY returns a result set containing a list of the database and log files contained in the backup set.

Here’s an example using the same WideWorldImporters .bak file from the earlier example:

RESTORE FILELISTONLY
FROM DISK = N'/var/opt/mssql/Bak/WideWorldImporters-Full.bak';

Result (using vertical output):

-[ RECORD 1 ]-------------------------
LogicalName          | WWI_Primary
PhysicalName         | D:\Data\WideWorldImporters.mdf
Type                 | D
FileGroupName        | PRIMARY
Size                 | 1073741824
MaxSize              | 35184372080640
FileId               | 1
CreateLSN            | 0
DropLSN              | 0
UniqueId             | 8d30f4f9-a463-404f-805a-9bd1c634b66b
ReadOnlyLSN          | 0
ReadWriteLSN         | 0
BackupSizeInBytes    | 11993088
SourceBlockSize      | 512
FileGroupId          | 1
LogGroupGUID         | NULL
DifferentialBaseLSN  | 626000002440500037
DifferentialBaseGUID | 0c5a4141-4a09-4b31-8c83-217870278065
IsReadOnly           | 0
IsPresent            | 1
TDEThumbprint        | NULL
SnapshotUrl          | NULL
-[ RECORD 2 ]-------------------------
LogicalName          | WWI_UserData
PhysicalName         | D:\Data\WideWorldImporters_UserData.ndf
Type                 | D
FileGroupName        | USERDATA
Size                 | 2147483648
MaxSize              | 35184372080640
FileId               | 3
CreateLSN            | 37000000095200001
DropLSN              | 0
UniqueId             | 28d406e0-78ff-4400-9a4b-3a05d136b1f3
ReadOnlyLSN          | 0
ReadWriteLSN         | 0
BackupSizeInBytes    | 434962432
SourceBlockSize      | 512
FileGroupId          | 2
LogGroupGUID         | NULL
DifferentialBaseLSN  | 626000002440500037
DifferentialBaseGUID | 0c5a4141-4a09-4b31-8c83-217870278065
IsReadOnly           | 0
IsPresent            | 1
TDEThumbprint        | NULL
SnapshotUrl          | NULL
-[ RECORD 3 ]-------------------------
LogicalName          | WWI_Log
PhysicalName         | E:\Log\WideWorldImporters.ldf
Type                 | L
FileGroupName        | NULL
Size                 | 104857600
MaxSize              | 2199023255552
FileId               | 2
CreateLSN            | 0
DropLSN              | 0
UniqueId             | 6ac6807e-8774-415b-8efc-e8c569b0855e
ReadOnlyLSN          | 0
ReadWriteLSN         | 0
BackupSizeInBytes    | 0
SourceBlockSize      | 512
FileGroupId          | 0
LogGroupGUID         | NULL
DifferentialBaseLSN  | 0
DifferentialBaseGUID | 00000000-0000-0000-0000-000000000000
IsReadOnly           | 0
IsPresent            | 1
TDEThumbprint        | NULL
SnapshotUrl          | NULL
-[ RECORD 4 ]-------------------------
LogicalName          | WWI_InMemory_Data_1
PhysicalName         | D:\Data\WideWorldImporters_InMemory_Data_1
Type                 | S
FileGroupName        | WWI_InMemory_Data
Size                 | 0
MaxSize              | 0
FileId               | 65537
CreateLSN            | 624000000336200003
DropLSN              | 0
UniqueId             | f65663c8-a250-433e-bbe6-e13a5599a607
ReadOnlyLSN          | 0
ReadWriteLSN         | 0
BackupSizeInBytes    | 980090880
SourceBlockSize      | 512
FileGroupId          | 3
LogGroupGUID         | NULL
DifferentialBaseLSN  | 626000002440500037
DifferentialBaseGUID | 0c5a4141-4a09-4b31-8c83-217870278065
IsReadOnly           | 0
IsPresent            | 1
TDEThumbprint        | NULL
SnapshotUrl          | NULL

So we can see that the physical locations of this file use Windows file paths. For example, the first one (with a logical name of WWI_Primary) has a physical location of D:\Data\WideWorldImporters.mdf.

In my case, I restored the database to SQL Server for Linux (running in a Docker container on my Mac), so when I restored this .bak file to my system, I had to change the physical paths to suit my system.

Of course, you can also change the file paths when restoring it to a Windows machine if required.

The Full Syntax

Backing up and restoring databases can be quite involved, depending on your requirements. The RESTORE statement is designed to cover many different scenarios. In particular, it covers the following restore scenarios:

  • Restore an entire database from a full database backup (a complete restore).
  • Restore part of a database (a partial restore).
  • Restore specific files or filegroups to a database (a file restore).
  • Restore specific pages to a database (a page restore).
  • Restore a transaction log onto a database (a transaction log restore).
  • Revert a database to the point in time captured by a database snapshot.

The full syntax contains a lot of options, so if your requirements exceed the scope of this article, check out the Microsoft documentation for the official RESTORE syntax and explanation.

Also read Microsoft’s Restore and Recovery Overview for an overview of the various considerations and approaches to restoring databases.