Automating RESTORE VERIFYONLY as seperate SQL Agent Job

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Automating RESTORE VERIFYONLY as seperate SQL Agent Job

Postby epetro » Thu Mar 15, 2012 4:11 pm

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
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Results

Postby epetro » Thu Mar 15, 2012 4:12 pm

<THIS POST RESERVED FOR FINAL SOLUTION>
update pending...
update still pending...(hotter fires)4/5/2012
Last edited by epetro on Thu Apr 05, 2012 3:40 pm, edited 1 time in total.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Thu Mar 15, 2012 9:20 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Scheduled spot check

Postby epetro » Fri Mar 16, 2012 1:17 pm

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.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby ChrisAVWood » Fri Mar 16, 2012 3:15 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job

Postby petey » Sat Mar 17, 2012 1:55 pm

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.

Code: Select all
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 7
petey
 
Posts: 2229
Joined: Sun Apr 24, 2005 12:34 pm

Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job

Postby Colin Millerchip » Mon Mar 19, 2012 10:45 am

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.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests