Automating RESTORE VERIFYONLY as seperate SQL Agent Job

epetro

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.

epetro

<THIS POST RESERVED FOR FINAL SOLUTION>
update pending...
update still pending...(hotter fires)4/5/2012

ChrisAVWood

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?

Chris

epetro

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.

ChrisAVWood

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).

Chris

petey

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

Colin Millerchip

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 http://www.red-gate.com/products/dba/sql-backup/eap-v7. 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,


Colin.