Red Gate forums :: View topic - Automating RESTORE VERIFYONLY as seperate SQL Agent Job
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Automating RESTORE VERIFYONLY as seperate SQL Agent Job

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Mar 15, 2012 4:11 pm    Post subject: Automating RESTORE VERIFYONLY as seperate SQL Agent Job Reply with quote

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.
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Thu Mar 15, 2012 4:12 pm    Post subject: Results Reply with quote

<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
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Thu Mar 15, 2012 9:20 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
epetro



Joined: 31 May 2011
Posts: 55
Location: Zotec Partners

PostPosted: Fri Mar 16, 2012 1:17 pm    Post subject: Scheduled spot check Reply with quote

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.
Back to top
View user's profile Send private message
ChrisAVWood



Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA

PostPosted: Fri Mar 16, 2012 3:15 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2299

PostPosted: Sat Mar 17, 2012 1:55 pm    Post subject: Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job Reply with quote

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 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
Back to top
View user's profile Send private message Send e-mail
Colin Millerchip



Joined: 31 Oct 2007
Posts: 63
Location: Cambridge, UK

PostPosted: Mon Mar 19, 2012 10:45 am    Post subject: Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group