THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns a result set containing information about the backup media identified by the given backup device.

System_CAPS_ICON_note.jpg Note

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

Topic link icon Transact-SQL Syntax Conventions

FROM <backup_device>   
[ WITH   
--Media Set Options  
   MEDIANAME = { media_name | @media_name_variable }   
 | MEDIAPASSWORD = { mediapassword | @mediapassword_variable }  
--Error Management Options  
--Tape Options  
 | { 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 }   

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

The result set from RESTORE LABELONLY consists of a single row with this information.

Column nameData typeDescription
MediaNamenvarchar(128)Name of the media.
MediaSetIduniqueidentifierUnique identification number of the media set.
FamilyCountintNumber of media families in the media set.
FamilySequenceNumberintSequence number of this family.
MediaFamilyIduniqueidentifierUnique identification number for the media family.
MediaSequenceNumberintSequence number of this media in the media family.
MediaLabelPresenttinyintWhether the media description contains:

 1 = Microsoft Tape Format media label

 0 = Media description
MediaDescriptionnvarchar(255)Media description, in free-form text, or the Tape Format media label.
SoftwareNamenvarchar(128)Name of the backup software that wrote the label.
SoftwareVendorIdintUnique vendor identification number of the software vendor that wrote the backup.
MediaDatedatetimeDate and time the label was written.
Mirror_CountintNumber of mirrors in the set (1-4).

Note: The labels written for different mirrors in a set are identical.
IsCompressedbitWhether the backup is compressed:

0 = not compressed

1 =compressed
System_CAPS_ICON_note.jpg Note

If passwords are defined for the media set, RESTORE LABELONLY returns information only if the correct media password is specified in the MEDIAPASSWORD option of the command.

Executing RESTORE LABELONLY is a quick way to find out what the backup media contains. Because RESTORE LABELONLY reads only the media header, this statement finishes quickly even when using high-capacity tape devices.

A backup operation may optionally specify passwords for a media set. When a password has been defined on a media set, you must specify the correct password in the RESTORE statement. The password prevents 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.

System_CAPS_ICON_important.jpg Important

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.


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).

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

Community Additions