| Author |
Message |
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
 Posted: Thu Mar 15, 2012 4:11 pm Post subject: Automating RESTORE VERIFYONLY as seperate SQL Agent Job |
 |
|
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 |
|
 |
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
| Posted: Thu Mar 15, 2012 4:12 pm Post subject: Results |
|
|
<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 |
|
|
ChrisAVWood
Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA
|
| Posted: Thu Mar 15, 2012 9:20 pm Post subject: |
|
|
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 |
|
|
epetro
Joined: 31 May 2011
Posts: 55
Location: Zotec Partners
|
| Posted: Fri Mar 16, 2012 1:17 pm Post subject: Scheduled spot check |
|
|
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 |
|
|
ChrisAVWood
Joined: 18 Dec 2007
Posts: 308
Location: Edmonton, Alberta, CANADA
|
| Posted: Fri Mar 16, 2012 3:15 pm Post subject: |
|
|
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 |
|
|
petey
Joined: 24 Apr 2005
Posts: 2218
|
| Posted: Sat Mar 17, 2012 1:55 pm Post subject: Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job |
|
|
| 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 |
|
|
Colin Millerchip
Joined: 31 Oct 2007
Posts: 42
Location: Cambridge, UK
|
| Posted: Mon Mar 19, 2012 10:45 am Post subject: Re: Automating RESTORE VERIFYONLY as seperate SQL Agent Job |
|
|
| 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 |
|
|
|
