RESTORE FILELISTONLY (Transact-SQL)

Switch View

RESTORE FILELISTONLY (Transact-SQL)

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

RESTORE FILELISTONLY (Transact-SQL)

Updated: 31 October 2008

Returns a result set containing a list of the database and log files contained in the backup set.

Note:
For the descriptions of the arguments, see RESTORE Arguments (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

Syntax

RESTORE FILELISTONLY 
FROM <backup_device> 
[ WITH 
  {
--Backup Set Options
   FILE = { backup_set_file_number | @backup_set_file_number } 
 | PASSWORD = { password | @password_variable } 

--Media Set Options
 | MEDIANAME = { media_name | @media_name_variable } 
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
  
--Error Management Options
 | { CHECKSUM | NO_CHECKSUM } 
 | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--Tape Options
 | { REWIND | NOREWIND } 
 | { UNLOAD | NOUNLOAD }  
  } [ ,...n ]
]
[;]

<backup_device> ::=
{ 
   { logical_backup_device_name |
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } 
}

Arguments

For descriptions of the RESTORE FILELISTONLY arguments, see RESTORE Arguments (Transact-SQL).

Result Sets

A client can use RESTORE FILELISTONLY to obtain a list of the files contained in a backup set. This information is returned as a result set containing one row for each file.

Column name	Data type	Description
LogicalName	nvarchar(128)	Logical name of the file.
PhysicalName	nvarchar(260)	Physical or operating-system name of the file.
Type	char(1)	The type of file, one of: L = Microsoft SQL Server log file D = SQL Server data file F = Full Text Catalog
FileGroupName	nvarchar(128)	Name of the filegroup that contains the file.
Size	numeric(20,0)	Current size in bytes.
MaxSize	numeric(20,0)	Maximum allowed size in bytes.
FileID	bigint	File identifier, unique within the database.
CreateLSN	numeric(25,0)	Log sequence number at which the file was created.
DropLSN	numeric(25,0) NULL	The log sequence number at which the file was dropped. If the file has not been dropped, this value is NULL.
UniqueID	uniqueidentifier	Globally unique identifier of the file.
ReadOnlyLSN	numeric(25,0) NULL	Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change).
ReadWriteLSN	numeric(25,0) NULL	Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change).
BackupSizeInBytes	bigint	Size of the backup for this file in bytes.
SourceBlockSize	int	Block size of the physical device containing the file in bytes (not the backup device).
FileGroupID	int	ID of the filegroup.
LogGroupGUID	uniqueidentifier NULL	NULL.
DifferentialBaseLSN	numeric(25,0) NULL	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. For information about log sequence numbers (LSNs), see Introduction to Introduction to Log Sequence Numbers.
DifferentialBaseGUID	uniqueidentifier	For differential backups, the unique identifier of the differential base. For other backup types, the value is NULL.
IsReadOnly	bit	1 = The file is read-only.
IsPresent	bit	1 = The file is present in the backup.
TDEThumbprint	varbinary(32)	Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted. For information about database encryption, see Understanding Transparent Data Encryption (TDE).

Permissions

In SQL Server 2008 and later versions, obtaining information about a backup set or backup device requires CREATE DATABASE permission. For more information, see GRANT Database Permissions (Transact-SQL).

A backup operation may optionally specify passwords for a media set, a backup set, or both. When a password has been defined on a media set or backup set, you must specify the correct password or passwords in the RESTORE statement. These passwords prevent unauthorized restore operations and unauthorized appends of backup sets to media using Microsoft SQL Server tools. However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.

Security Note:
The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.

Security Note:

The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by other means or the replacement of the password. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The best practice for protecting backups is to store backup tapes in a secure location or back up to disk files that are protected by adequate access control lists (ACLs). The ACLs should be set on the directory root under which backups are created.

Examples

The following example returns the information from a backup device named AdventureWorksBackups. The example uses the FILE option to specify the second backup set on the device.

RESTORE FILELISTONLY FROM AdventureWorksBackups 
   WITH FILE=2;
GO

Change History

Updated content
Added the TDEThumbprint column to the "Results Sets" table.

Reference

Other Resources

Help and Information