RESTORE VERIFYONLY (T-SQL)

Verifies the backup but does not restore the backup. Checks to see that the backup set is complete and that all volumes are readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, Microsoft® SQL Server™ returns the message: “The backup set is valid.”

Syntax

RESTORE VERIFYONLY
FROM <backup_device> [,...n]
[WITH
    [FILE = file_number]
    [[,] {NOUNLOAD | UNLOAD}]
    [[,] LOADHISTORY]
]

<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(s) to use for the restore. Can be one or more 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(s) created by sp_addumpdevice from which the database is restored. The maximum number of backup devices in a single RESTORE statement is 32. 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.

n

Is a placeholder indicating that multiple backup devices and temporary backup devices may be specified. The maximum number of backup devices or temporary backup devices in a single RESTORE VERIFYONLY statement is 32.

---

Note In order to specify multiple backup devices for <backup_device>, all backup devices specified must be part of the same media set.

---

FILE = file_number

Identifies the backup set to be restored or processed. For example, a file_number of 1 indicates the first backup set and a file_number of 2 indicates the second backup set. If no file_number is supplied, the first backup set on the specified <backup_device> is assumed.

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. If a nontape device is being used for the restore, this option is ignored.

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. If a nontape device is being used for the RESTORE, this option is ignored.

LOADHISTORY

Specifies that the restore operation loads the information into the msdb history tables. The LOADHISTORY option loads information, for the single backup set being verified, about SQL Server backups stored on the media set to the backup and restore history tables in the msdb database. No information for non-SQL Server backups is loaded into these history tables. For more information about history tables, see System Tables.

Permissions

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

See Also