RESTORE VERIFYONLY via GUI?

dhtuckerdhtucker Posts: 41 Bronze 3
edited July 23, 2010 12:37PM in SQL Backup Previous Versions
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.
Doug Tucker
Database Administrator / Software Engineer
Nelnet Business Solutions - FACTS-SIS

Comments

  • peteypetey Posts: 2,358 New member
    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] "'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • dhtuckerdhtucker Posts: 41 Bronze 3
    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}
    Doug Tucker
    Database Administrator / Software Engineer
    Nelnet Business Solutions - FACTS-SIS
Sign In or Register to comment.