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.