Skip to content

Latest commit

 

History

History
130 lines (108 loc) · 8.35 KB

restore-statements-filelistonly-transact-sql.md

File metadata and controls

130 lines (108 loc) · 8.35 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
RESTORE FILELISTONLY (Transact-SQL)
RESTORE Statements - FILELISTONLY (Transact-SQL)
MikeRayMSFT
mikeray
03/30/2018
sql
t-sql
reference
RESTORE FILELISTONLY
RESTORE_FILELISTONLY_TSQL
FILELISTONLY
FILELISTONLY_TSQL
backups [SQL Server], file lists
RESTORE FILELISTONLY statement
listing backed up files
TSQL
=azuresqldb-mi-current||>=sql-server-2016||>=sql-server-linux-2017

RESTORE Statements - FILELISTONLY (Transact-SQL)

[!INCLUDEsql-asdbmi]

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

Note

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 }   
 | [ METADATA_ONLY | SNAPSHOT ] [ DBNAME = { database_name | @database_name_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 | URL } = { 'physical_backup_device_name' |  
       @physical_backup_device_name_var }   
}  

Note

URL is the format used to specify the location and the file name for Microsoft Azure Blob Storage and is supported starting with [!INCLUDEssSQL11] SP1 CU2. Although Microsoft Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seamless restore experience for all the three devices.

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 [!INCLUDEssNoVersion] log file

D = [!INCLUDEssNoVersion] data file

F = Full Text Catalog

S = FileStream, FileTable, or [!INCLUDEinmemory] container
FileGroupName nvarchar(128) NULL 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.
DifferentialBaseGUID uniqueidentifier NULL 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) NULL 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 Transparent Data Encryption (TDE).
SnapshotURL nvarchar(360) NULL Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL16] (CU1) through current version.

The URL for the Azure snapshot of the database file contained in the FILE_SNAPSHOT backup. Returns NULL if no FILE_SNAPSHOT backup.

Security

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 [!INCLUDEmsCoName] [!INCLUDEssNoVersion] tools. However, a password does not prevent overwrite of media using the BACKUP statement's FORMAT option.

Important

The protection provided by this password is weak. It is intended to prevent an incorrect restore using [!INCLUDEssNoVersion] 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. [!INCLUDEssNoteDepFutureAvoid] 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.

Permissions

Beginning in [!INCLUDEsql2008-md], obtaining information about a backup set or backup device requires CREATE DATABASE permission. For more information, see GRANT Database Permissions (Transact-SQL).

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

BACKUP (Transact-SQL)
Media Sets, Media Families, and Backup Sets (SQL Server)
RESTORE REWINDONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)
RESTORE (Transact-SQL)
Backup History and Header Information (SQL Server)