RESTORE LABELONLY (T-SQL)

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

Syntax

RESTORE LABELONLY
FROM <backup_device>
[WITH {NOUNLOAD | UNLOAD}]

<backup_device> ::=
    {
        {'backup_device_name' | @backup_device_name_var}
        | {DISK | TAPE | PIPE} =
            {'temp_backup_device' | @temp_backup_device_var}
    }

Arguments

<backup_device>

Specifies the permanent or temporary backup device to use for the restore. Can be one of the following:

{'backup_device__name' | @backup_device_name_var}

Is the logical name, which must follow the rules for identifiers, of the backup device created by sp_addumpdevice from which the database is restored. If supplied as a variable (@backup_device_name_var), the backup device name can be specified either as a string constant (@backup_device_name_var = 'backup_device_name') or as a variable of character string data type, except for the ntext or text data types.

{DISK | TAPE | PIPE} =

'temp_backup_device' | @temp_backup_device_var

Allows backups to be restored from the named disk, tape, or pipe device. The device types of disk and tape should be specified with the actual name (for example, complete path and file name) of the device: DISK = ‘C:\Mssql7\Backup\Mybackup.dat’ or TAPE = \\.\TAPE0. A device type of pipe should specify the name of the named pipe that is used by the client application. If specified as a variable (@temp_backup_device_var), the device name can be specified either as a string constant (@temp_backup_device_var = 'temp_backup_device') or as a variable of character string data type, except for the ntext or text data types.

Pipe devices have been added to allow third-party vendors a flexible way to connect their own software. The pipe device is not used for typical Transact-SQL use.

If you are using either a network server with a UNC name or a redirected drive letter, specify a device type of disk.

NOUNLOAD

Specifies that the tape is not unloaded automatically from the tape drive after a restore. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices.

UNLOAD

Specifies that the tape is automatically rewound and unloaded when the restore is finished. UNLOAD is set by default when a new user session is started. It remains set until NOUNLOAD is specified. This option is used only for tape devices.

Permissions

RESTORE permissions default to members of the sysadmin fixed server role and the db_owner fixed database role, and are not transferable.

Remarks

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. The result set from RESTORE LABELONLY consists of a single row with this information.

See Also