RESTORE VERIFYONLY
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™ 2000 returns the message: "The backup set is valid."
Syntax
RESTORE VERIFYONLY
FROM < backup_device > [ ,...n ]
[ WITH
[ FILE = file_number ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] LOADHISTORY ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] { NOREWIND | REWIND } ]
]
< backup_device > ::=
{
{ 'logical_backup_device_name' | @logical_backup_device_name_var }
| { DISK | TAPE } =
{ 'physical_backup_device_name' | @physical_backup_device_name_var }
}
Arguments
<backup_device>
Specifies the logical or physical backup device(s) to use for the restore. Can be one or more of the following:
- {'logical_backup_device_name' | @logical_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. If supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified either as a string constant (@logical_backup_device_name_var = 'logical_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.
- {DISK | TAPE } =
- 'physical_backup_device_name' | @physical_backup_device_name_var
Allows backups to be restored from the named disk or tape 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:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'. If specified as a variable (@physical_backup_device_name_var), the device name can be specified either as a string constant (@physical_backup_device_name_var = 'physical_backup_device_name') or as a variable of character string data type, except for the ntext or text data types.
If 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 logical backup devices may be specified. The maximum number of backup devices or logical backup devices in a single RESTORE VERIFYONLY statement is 64.
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.
PASSWORD = { password | @password_variable}
Is the password for the backup set. PASSWORD is a character string. If a password was provided when the backup set was created, the password must be supplied to perform any restore operation from the backup set.
For more information about using passwords, see Permissions.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable}
Is the password for the media set. MEDIAPASSWORD is a character string data type, with a default of NULL.
If a password was provided when the media set was formatted, that password must be supplied to create a backup set on that media set. In addition, that media password also must be supplied to perform any restore operation from the media set.
NOREWIND
Specifies that SQL Server will keep the tape open after the backup operation. NOREWIND implies NOUNLOAD.
REWIND
Specifies that SQL Server will release and rewind the tape. If neither NOREWIND nor REWIND is specified, REWIND is the default.
Permissions
Any user may use RESTORE VERIFYONLY.
In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, restore is not allowed unless the correct media password and backup set password are specified in the restore command.
Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.
Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.
It is an error to specify a password if none is defined.
See Also
Backing Up and Restoring Databases