RESTORE FILELISTONLY (Transact-SQL)
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).
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
RESTORE FILELISTONLY
FROM <backup_device>
[ WITH
[ { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] FILE = backup_set_file_number ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword |
@mediapassword_variable } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] REWIND ]
[ [ , ] { UNLOAD | NOUNLOAD } ]
]
[;]
<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. |
Permissions
Any user can use RESTORE FILELISTONLY.
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 2005 tools. However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.
.gif "ms173778.security(en-US,SQL.90).gif") Security Note: |
|---|
| The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server 2005 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. 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
See Also
Reference
BACKUP (Transact-SQL)
RESTORE REWINDONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)
RESTORE (Transact-SQL)
Other Resources
Media Sets, Media Families, and Backup Sets
Viewing Information About Backups
Introduction to Log Sequence Numbers