RESTORE VERIFYONLY via GUI?

dhtucker

Is it possible to perform a RESTORE VERIFYONLY via the GUI? Or is there another preferred method to validate a backup file after it's been written? I know this can be done at backup time, but I don't want to run 'verify' on all backups (which roughly doubles the processing time) - I'd rather go back and randomly sample a few backups each day.

petey

Via the GUI, no. You'll need to use the SQL Backup extended stored procedure e.g.

EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = [g:\backups\AdventureWorks.sqb] "'

dhtucker

There's no other way to validate a backup after the fact?

The extended stored procedure isn't very user friendly, given that the file name isn't simple (with timestamp encoding) and might reside on a network share.

Might I suggest this as an enhancement for a future release?
For a quick validation of a single backup, it'd be really nice as a rt-click option under Activity History.

:idea: Actually, for my purposes (validating a subset of last night's backups), it's probably more efficient to query SQLBackupReportingDatabase for a set of databases and their associated filenames and pipe those to the stored procedure you described.

USE [SQLBackupReportingDatabase]
SELECT dbname, name
FROM backupfiles bf
JOIN backuphistory bh ON bf.backup_id = bh.id AND bf.server_id = bh.server_id
JOIN backuplog bl ON bh.id = bl.backup_id AND bh.server_id = bl.server_id
JOIN identities i ON i.id=bf.server_id
WHERE {criteria of your choice}