Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The Importance of Validating Backups Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 7:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:29 AM
Points: 542, Visits: 2,123
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.
Post #1363486
Posted Monday, September 24, 2012 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 180, Visits: 420
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
Post #1363514
Posted Monday, September 24, 2012 8:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:40 AM
Points: 80, Visits: 1,000
.
Post #1363548
Posted Monday, September 24, 2012 9:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
deleted
Post #1363580
Posted Monday, September 24, 2012 10:02 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 1:14 PM
Points: 22, 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?
Post #1363590
Posted Monday, September 24, 2012 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
[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.
Post #1363609
Posted Monday, September 24, 2012 11:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
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
Post #1363643
Posted Monday, September 24, 2012 12:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
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
Post #1363674
Posted Tuesday, September 25, 2012 1:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:28 PM
Points: 882, Visits: 4,111
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
Post #1363837
Posted Tuesday, September 25, 2012 3:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 2,005, Visits: 3,414
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!
Post #1363870
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse