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 Cyber Backup. 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.

Easy-to-use: With the intuitive interface, it's easy to configure SQL backups without script. Flexible Backup: Choose flexible backup methods and able to backup all the devices within LAN. Auto&Centralized Backup: Schedule SQL backups from a central console and run it automatically.

As a professional SQL Server database backup software, it applies to 2005\2008\2008 R2\2012\2014\2016\2017\2019\2022 and corresponding SQL Server Express. Here is a 30-day free trial for you:

Backup your SQL databases with ease

✍ To properly use AOMEI Cyber Backup, you should note these prerequisites for Microsoft SQL backup:

• Computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed
• Local disk or network share to store backup files

1. Access to "Source Device" > "Add Microsoft SQL". If the database exists and the version is supported, it will appear automatically. Otherwise, you can click "Add Microsoft SQL" > "Download proxy program", and install the program on the device with SQL Server installed. Then click "Already installed proxy" and select the proxies you want to add.

2. Click "..."-->"Authentication" to validate the database instance. You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".

3. Go to schedule your SQL database backup as following:

▪ Click "Backup Task" > "Create New Task" to backup your SQL databases. ▪ Choose backup type as "Microsoft SQL Backup", and you could also try virtual machine Backup. ▪ Enter a name for the backup task, then go to select the databases you want to backup and specify a location as a storage end. ▪ Select backup methods as full/incremental/differential and specify the backup time as daily/weekly/monthly. Your SQL databases will be backed up automatically and regularly.

4. Enable Backup Cleanup to automatically remove history backup versions based on rule to save storage space. You will receive email notifications by enabling Email Notification feature when the task is abnormal or successful.

3 quick steps to restore multiple SQL backups

Backup tasks created using AOMEI Cyber Backup will all listed separately. You may need to restore your databases due to database crash, system corruption, data loss, etc.

1. Click "Backup Task" on the left menu bar, locate the task you want to restore, and click ""--> "Restore".

2. Select the backup that you want to restore

3. Specify a target location that you want to restore to.

✍ You could restore to original/new location.

• "Restore to original location": If you choose the option "Over the database with the same name", the detected database with the same name will be overwritten automatically when restoring. Otherwise, the database with the same name will be skipped.
• "Restore to new location": Select target and specify the name of the new database. You can also modify the storage location.

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 Cyber Backup, which allows one to easily manage backups and restores of all databases within LAN, without the need of advanced knowledge.

When restoring, it is able to restore your database to a new computer directly. Specially designed for enterprises, it can greatly promote the work efficiency.