MSSQL Restore Filelistonly | Understanding Its Usage

What is MSSQL Restore Filelistonly and when to use it

MSSQL Restore Filelistonly is one of the T-SQL RESTORE statements, used to list all the files related to the specific SQL Server database backup, along with their information.

Executing MSSQL Restore Filelistonly option returns a result set with a list of the database and log files contained in the backup set, along with the corresponding data values. Most of the information cannot be obtained from SSMS GUI.

Therefore, using MSSQL Restore Filelistonly option in SQL Server database restore tasks allows us to clearly understand the information related to the backup file, and improves our work efficiency.

When to use MSSQL Restore Filelistonly command

Here I summarized several common situations where you may need to use it:

• If you want to use T-SQL to restore SQL backup to new database on the same or another SQL Server, then first you need to use Restore Filelistonly to list the logical file names of the backup file.
• If you want to forecast the size of the database before restoring, you can sum up the values in the Size list (in bytes) in the result set.
• If you only want to view the detailed information of the backup file without restoring it, or store them in a temp table for later reference.

How to use MSSQL Restore Filelistonly from Disk Command

Launch SSMS and connect to your instance. Click New Query on toolbar and input the following command:

RESTORE FILELISTONLY FROM DISK = 'filepath\filename.bak'

Note:
1. In ‘filepath\backup.bak’ you need to type where your backup is putted in and the file name of your backup, with the suffix .bak. For example: 'D:\Backup\DAname_1.bak'
2. If there are multiple backups in the backup device, the first backup is specified by default. But you can also use FILE option to specify a specific backup (Executing RESTORE HEARDERONLY to get the Position number).
For example: To specify the second backup on ExampleBackups, a logical backup device in SQL Server：RESTORE FILELISTONLY FROM ExampleBackups WITH FILE=2

The logical name of the backup file will be listed in Results, now you can use T-SQL WITH MOVE commands to restore SQL backup to new database on same or another server.

✦ Here are some commonly needed values in the result set:

• LogicalName: The logical name of the backup file.
• PhysicalName: The physical path of the backup file, also called operating-system name.
• Type: The type of the file. There are 4 types of a file: D=SQL Server data file, L=SQL Server log file, F=Full Text Catalog, S=FileStream, FileTable, or In-Memory OLTP container.
• FileGroupName: Name of the filegroup that contains the file.
• Size: The current size of the file (in bytes). You can sum up the size of the .mdf file and the ldf file to forecast the size of the database.
• MaxSize: Maximum allowed size in bytes.
• FileID: File identifier, unique within the database.
• UniqueID: Globally unique identifier of the file.
• DifferentialBaseLSN: For differential backups, changes with log sequence numbers greater than or equal to DifferentialBaseLSN are included in the differential. For other backup types, the value is NULL.
• DifferentialBaseGUID: For differential backups, the unique identifier of the differential base. For other backup types, the value is NULL.
• IsReadOnly: 1=the file is read-only.

For more explanation of the information in result set of MSSQL Restore Filelistonly, you can refer to: RESTORE Statements - FILELISTONLY (Transact-SQL)

Other common parameters of RESTORE statements

Except for Restore Filelistonly, there are also 3 commonly used parameters in MSSQL Restore statements.

• Restore Headeronly: returns a result set of all backup sets’ header information on a backup device.
• Restore Labelonly: returns a result set of the backup media information.
• Restore Verifyonly: is used to verify if the backup is complete and readable.

In this section, I will introduce you what they can be used for, and how to use them.

How to use MSSQL Restore Headeronly

In MSSQL Server, Restore Headeronly and Restore Filelistonly are 2 important options that should be implemented before restoring the database.

Executing MSSQL Restore Headeronly, you can see who performed the backup and the other details in the backup from the information returned related to the SQL backup set. This can effectively help us perform SQL Server database backup validation before restoring.

For example, since the backup of a later version of SQL Server database cannot be restored on an earlier version, you can use Restore Headeronly to query the database version of that backup before restoring.

The basic syntax is:

RESTORE HEADERONLY FROM DISK = 'filepath\filename.bak'

✦ Here are some commonly needed values in the result set:

• BackupName: The name of the backup.
• BackupType: The type of the backup. 1=Database, 2=Transaction log, 4=File, 5=Differential database, 6=Differential file, 7=Partial, 8=Differential partial.
• ExpirationDate: The backup file will expire after this time.
• Compressed:0=not compressed, 1=compressed.
• Position: Position of the backup set in the volume (for use with the FILE = option).
• DeviceType: The type of the backup device. Number corresponding to the device used for the backup operation: Disk:2=Logical, 102=Physical. Tape: 5=Logical, 105=Physical. Virtual Device: 7=Logical, 107=Physical. URL: 9=Logical, 109=Physical.
• UserName: User name that performed the backup operation.
• ServerName: Name of the server that wrote the backup set.
• DatabaseName: Name of the database that was backed up.
• DatabaseVersion: Version of the database from which the backup was created. Later version SQL Server database backups cannot be restored to earlier versions.
• BackupSize: Size of the backup (in bytes).

For more explanation of the information in result set of MSSQL Restore Headeronly, you can refer to: RESTORE Statements - HEADERONLY (Transact-SQL)

How to use MSSQL Restore Labelonly

Executing RESTORE LABELONLY is a quick way to find out what the backup media contains.

For example, if you perform backups to multiple locations at the same time, those locations form a family. If the FamilyCount is larger than 1, then this backup file has a family. And you can tell when it was created or if it was the first one created, by checking MediaDate and MediaSequenceNumber.

The basic syntax is:

RESTORE LABELONLY FROM DISK = 'filepath\filename.bak'

✦ Here are some commonly needed values in the result set:

• MediaName: Name of the media.
• MediaSetId: Unique identification number of the media set.
• FamilyCount: Number of media families in the media set.
• MediaSequenceNumber: Sequence number of this media in the media family.
• MediaDescription: Media description, in free-form text, or the Tape Format media label.
• SoftwareName: Name of the backup software that wrote the label.
• SoftwareVendorId: Unique vendor identification number of the software vendor that wrote the backup.
• MediaDate: Date and time the label was written.
• IsCompressed: 0=not compressed, 1=compressed.

For more explanation of the information in result set of MSSQL Restore Labelonly, you can refer to: RESTORE Statements - LABELONLY (Transact-SQL)

How to use MSSQL Restore Verifyonly

In SQL Server, Restore Verifyonly is used to check the backup is complete and readable.

When you have completed a SQL Server database backup, you can perform MSSQL Restore Verifyonly to check the readability of each backup.

If the result returned is that the backup set is invalid, that means that this backup cannot be used to perform a restore, and you need to back up again.

The basic syntax is:

RESTORE VERIFYONLY FROM DISK = 'filepath\filename.bak'

One effortless way to restore multiple SQL backups to new location

MSSQL Restore Filelistonly option is mainly used to query the logical name of the backup files, to restore backup to new location using T-SQL commands. But if you got many backups to restore, and query them one by one will be laborious and time-consuming, you can choose an effortless way: AOMEI Centralized Backupper Database Edition.

As a professional SQL Server database backup software, it allows you to easily start managing backups and restores of all laptops, workstations, and servers within LAN, without the need of advanced knowledge. When restore multiple databases of SQL Server, it will make a clear backup file list for you to choose. So, you don't need to check them all by yourself.

Here is a 30-day free trial for you:

To properly use Centralized Backupper Database, you can follow these steps:

Launch Centralized Backupper Database, and install Agent remotely or manually by clicking Computers > Install Client Program.

For the computers with client programs installed manually, you still need to Request Control over them for the further operation.

Click Computers > Uncontrolled Computers to find the correct IP, then click Request Control on the upper bar. A prompt window will show on their computers, once all permission received you can start whenever you like.

Select Tasks > New Task > SQL Server Backup to create a new backup task, and follow the 3 steps in the prompt window, you can easily create auto backup tasks of multiple SQL Server databases.

3 quick steps to restore multiple SQL backups

Backup tasks created using Centralized Backupper Database will all show on Tasks tab separately. You can manage or restore them here instead of locating a specific bak file on the disk.

1. Click Tasks > Advanced on the upper left of the task, select Restore to continue.

2. In this step you can select the computer and specific backup (full/differential) to be restored from, and you can select databases in it as you like.

3. Choose to restore the database to the original location or a new location. By selecting the second option, you can specify another instance on the same or another SQL Server in the LAN. Then click Start Restore to accomplish this task.

Summary

In this article, I introduced the MSSQL Restore Filelistonly option, 3 other parameters in Restore statements, and how to use them. They can be of great help if you want to refer to the information from the backup before performing a SQL Server database restore.

But if you got many backups to restore, to query them one by one can be laborious and time-consuming, you can choose an effortless tool: AOMEI Centralized Backupper Database Edition, which allows one to easily manage backups and restores of all computers within LAN, without the need of advanced knowledge.

When restoring, it will make a backup file list so you don’t have to check them by yourself. Specially designed for enterprises, it can greatly promote the work efficiency.