SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Importance of Validating Backups


The Importance of Validating Backups

Author
Message
Michael Meierruth
Michael Meierruth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4034 Visits: 2522
Paul,

I have a few automated backups running in various places.
They typically are started via a .bat file launching sqlcmd.exe with the -i parameter pointing to the .sql file issuing the backup command.
The occassional restore I had to do never presented a problem. But I prefere to be on the safe side.
Once I have put the WITH CHECKSUM into the .sql file, how is an error signaled?
What sort of message is generated? If so, I suppose it's in the sqlcmd.exe output which I would have to pipe into a log.
Does sqlcmd.exe return a DOS error code of some sort in this case?

Thanks.
DBA_Learner
DBA_Learner
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1719 Visits: 450
Restore verify only with checksum can only be used when the backup file which queried with checksum. If we do not do backup file with checksum, we cannot use the command Restore verify only with checksum
Paul Brewer
Paul Brewer
SSC Eights!
SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)

Group: General Forum Members
Points: 979 Visits: 1330
.
JamesMorrison
JamesMorrison
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 469
deleted
Zivio!
Zivio!
SSC-Addicted
SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)SSC-Addicted (478 reputation)

Group: General Forum Members
Points: 478 Visits: 64
Thanks for the great article, Paul. I was one of those "people around the world asking for help with corrupt databases, where their recent backups also contained the corruption" whom you mercifully helped years ago!

QUESTION: The database maintenance plan dialogs in Microsoft SQLServer2000 (yes, we're still running it) has a "Verify the integrity of the backup upon completion" option. Is this the same as the "WITH CHECKSUM" option?
JamesMorrison
JamesMorrison
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 469
[b]

QUESTION: The database maintenance plan dialogs in Microsoft SQLServer2000 (yes, we're still running it) has a "Verify the integrity of the backup upon completion" option.


The problem with that option is that it takes forever with a large database. When we were using it the database backup times were twice as long. We had to stop using it due to the lack of time available each night. It blew through our window.
Paul Randal
Paul Randal
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13946 Visits: 1722
Yes, that's what does the WITH CHECKSUM option on RESTORE VERIFYONLY. Either do it on the prod system, or copy and restore somewhere else.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
JamesMorrison
JamesMorrison
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1115 Visits: 469
Paul Randal,

What would you suggest for those that cannot do the 'verify backup integrity' due to time constraits?

In my environment we have multiple production database clusters, each with multiple TB databases.

1) It doesn't seem feasible to use 'verify backup integrity' because of the extra time requirements.
2) Restoring all of the backups elsewhere does not seem viable just due to the number of backups that exist.

We are requested to refresh QA, Dev or Performance testing environments every few weeks. So we do take prod backups and restore them elsewhere. But there is not a fixed schedule for this.

So what would be suggested then? Thanks
crazy4sql
crazy4sql
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8043 Visits: 4514
Very nice article. I have started using this in my backup after reading it 4-5 month back in some other forum.
Also, I have configured another job to verify the backup. This job includes
1) Verify the backup
2) Fill the result in a table
3) Put the table in dailycheck list once you back in office.

The below steps configured in job to verify the backup.

begin try
restore verifyonly from disk = 'your backup file path'
with checksum
end try
begin catch
insert into DBMaint.dbo.bkpverify (ErrorMessage ,dateNtime)
( SELECT
ERROR_MESSAGE() AS ErrorMessage,getdate())
END CATCH;




The code to create this bkpverify table is :-

create table DBMaint.dbo.bkpverify (ErrorMessage varchar(200),dateNtime datetime)



Also, you can configure to purge the records in this table which are older than 5 days(or your preferred value)

----------
Ashish
Gazareth
Gazareth
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12428 Visits: 6070
Paul Randal (9/24/2012)

2) Did CHECKDB complete and find errors? Check the value of @@ERROR afterwards - guarantees to be non-zero if CHECKDB found/had a problem.


Thanks Paul, that's a much better way to do it!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search