Automating RESTORE VERIFYONLY as seperate SQL Agent Job

epetroepetro Posts: 69
edited March 19, 2012 5:45AM in SQL Backup Previous Versions
I have been asked to begin verifying backups outside of our typical maintenance tasks.
I am starting this thread to track some ideas and progress relating to :

1. Capturing the latest full backup for the current database(as valued by db_name() )
2. Record the results to a centralized table for reporting.
3. Supply some data to the job history message.

Step 1 would be simple if we were not using the <auto> variable.


  • epetroepetro Posts: 69
    edited April 5, 2012 10:40AM
    update pending...
    update still pending...(hotter fires)4/5/2012
  • Not sure if this will help you but here is what we do currently.

    We copy the latest backup across to another server and restore it there also running DBB CHECKDB with PHYSICAL_ONLY and DBCC LOGINFO to see how many log fragments there are and checking possible corruption.

    Would this work for you?

    Could you do this in your storage compress enviroment you were testing?

    English DBA living in CANADA
  • I agree that your suggestion has potential to be incorporated with the Hyperbac software. However, I don't see the possibiity to process all 12TB of production data nightly (maybe not your suggestion) from our testing environments.

    A weekly or monthly DBCC would be a good addition to our process though.
  • Yes based on the quantity of data this probably isn't appropriate. I think we currently move around 1Tb of databases (150Gb of compressed backups) from a number of servers to two test servers (SQL2005 and SQL2008R2).

    English DBA living in CANADA
  • peteypetey Posts: 2,358 New member
    epetro wrote:
    Step 1 would be simple if we were not using the <auto> variable.
    Details of the backup file are stored in the physical_device_name column in the msdb..backupmediafamily table e.g.
    SELECT TOP 1 b.physical_device_name
    FROM msdb..backupset a
    INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
    WHERE a.database_name = 'pubs'
      AND a.type = 'D'
    ORDER BY a.backup_finish_date DESC
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • epetro wrote:
    Step 1 would be simple if we were not using the <auto> variable.
    There's some syntax in the SQL Backup engine which might help you here -- it allows you to do a RESTORE LATEST_FULL, to select the most recent full backup (there are other RESTORE_ keywords as well). Have a look here for more details.

    In addition, SQL Backup v7.0 introduces further functionality to help in the area of backup verification. You can get a sneak preview via our Early Access Program, at If you try out v7.0, please let us know your feedback (there are links in the UI to our EAP feedback site).

    Best regards,

Sign In or Register to comment.