|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Sunday, May 05, 2013 10:12 AM
Points: 480,
Visits: 1,604
|
|
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.
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 2:37 PM
Points: 170,
Visits: 388
|
|
| 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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 1:06 AM
Points: 54,
Visits: 613
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 2:55 PM
Points: 125,
Visits: 432
|
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Tuesday, December 04, 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?
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 2:55 PM
Points: 125,
Visits: 432
|
|
[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.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 2:55 PM
Points: 125,
Visits: 432
|
|
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
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876,
Visits: 3,731
|
|
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
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474,
Visits: 2,341
|
|
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!
|
|
|
|