December 15, 2014 at 7:38 pm
Hi SQL Gurus,
If I am taking regular full and Differential backup on Prod server should I take backups with CHECKSUM, or Restore VERIFYONLY or both?
thanks
December 15, 2014 at 10:33 pm
Hi,
Checksum and verifyonly essentially performs the same function, But still a healthy combination of both would go a long way.
I take all the backups with checksum and regularly keeps on checking the backup files using verifyonly.
Hope it helps..!!
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 16, 2014 at 4:54 am
Both. And more.
CHECKSUM validates that what was read from the disk is what got written to your backup file. VERIFY_ONLY will then validate that what was written to your backup file is accessible and matches the CHECKSUM. They don't do the same thing.
Here's a short article I wrote on backup verification[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2014 at 5:12 am
Thank you Gurus. Last thing just to confirm, I should do CHECKSUM on PROD db backups. Since Maintenance plan only does VERFIYONLY.
If I have a sql job do this with the time stamped, I found below, is this good enough?
DECLARE @MyFileName varchar(200)
SELECT @MyFileName='c:\backups\' + 'FULLBACKUP_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak'
BACKUP database AdventureWorks TO DISK=@MyFileName with checksum
December 16, 2014 at 5:19 am
Mr. Grant Fritchey
you said in previous reply 'They don't do the same thing' (VERIFYONLY AND CHECKSUM).
In your article you said 'Verifyonly: Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this'.
Not sure if I understood you. I have backup jobs setup thru maintenance plan with VerifyOnly. Is that enough to have good backup file or should I do CHECKSUM instead or Both. Please explain.
December 16, 2014 at 7:06 am
Tac11 (12/16/2014)
Mr. Grant Fritcheyyou said in previous reply 'They don't do the same thing' (VERIFYONLY AND CHECKSUM).
In your article you said 'Verifyonly: Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this'.
Not sure if I understood you. I have backup jobs setup thru maintenance plan with VerifyOnly. Is that enough to have good backup file or should I do CHECKSUM instead or Both. Please explain.
Yes, CHECKSUM validates that the pages read from the disk are the same as what it writes to the backup file as it writes those pages. That's one thing. VERIFYONLY will read from the backup file, later, and validate that what is written to the file matches the checksums that are also written to the file match one another. That's two different types of checks, yes, of the same data, but two different checks.
If you only use VERIFYONLY without CHECKSUM, then, all VERIFYONLY can do is read the header, and not all of the header, to validate that it is structured correctly. This is in the article, but possibly not emphasized adequately.
My recommendation is to use all the processes in combination that I outline in the article, as I outline in the article.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2014 at 1:19 am
Shafat Husain (12/15/2014)
Checksum and verifyonly essentially performs the same function
They don't do essentially the same thing.
A backup WITH CHECKSUM checks the checksum on each page as it is backed up, then at the end of the backup computes a checksum of the entire backup and writes it into the backup file.
The 'verify only' does a RESTORE VERIFY ONLY after the backup has completed, which on a backup taken without checksum just checks the header of the backup file for validity. If the backup was taken WITH CHECKSUM, then the RESTORE VERIFY ONLY also recomputes the checksum for the backup file and compares it with the one written into the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2014 at 2:42 am
Thank you Grant and GilaMonster. I guess i have to be more Conscious from now.
Regards
Shafat Husain
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 18, 2014 at 5:23 am
I know both have toll. what would you do on production db backup? only CHECKSUM? CHECKSUM AND RESTORE VERIFYONLY? OR VERIFYONLY?
December 18, 2014 at 5:26 am
Only checksum = you have no idea if your backup file is damaged
Only verify only = you have no idea if your backup file is damaged
Both - you have a fairly good idea that the backup file is intact and restorable as of the end of the backup.
What do you prefer?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2014 at 6:01 am
Tac11 (12/18/2014)
I know both have toll. what would you do on production db backup? only CHECKSUM? CHECKSUM AND RESTORE VERIFYONLY? OR VERIFYONLY?
There are caveats to all this, but the article I link above is what I do on production systems, CHECKSUM, VERIFYONLY, DBCC and, where possible, a full restore. All of it. I would only change this if I could demonstrate that any part of it was causing the system to slow down through specific wait statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2014 at 7:44 am
Thank you!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply