Jonathan Kehayias blogged [Pay Attention to Maintenance Cleanup Job Configuration (If you use Maintenance Plans)] yesterday going over one of his pet peeve about Maintenance plans. Maintenance plans are so inflexible when compared to regular T-SQL jobs and I want to present one of my reason(s) why I don’t like them.
Starting from SQL Server 2005, we have an option to enable PAGE CHECKSUM on the database level using the PAGE_VERIFY database option. Going over in detail about PAGE CHECKSUM is out of the scope [detailed blog post coming soon with additional tests and interesting results] for this post, but in essence it helps to uncover corruption caused by the IO subsytem.
With regular T-SQL BACKUP, one can use the CHECKSUM clause with the BACKUP command and it helps in the following ways.
1) Validate the PAGE CHECKSUM in all of the data pages where CHECKSUM/TORN_PAGE_DETECTION value is available.
2) Even if the PAGE CHECKSUM values are NOT present, adding this clause helps to compute the combined CHECKSUM of all data pages which is stored on the backup media. This is valuable if you want to check if the backup itself is NOT corrupted prior to restoring from the backup.
While BACKUP CHECKSUM is NO replacement for the extensive DBCC CHECKDB to validate corruption issues, but has a place of its own and should be run in between the regular schedule of the DBCC CHECKDB as a best practice.
So far so good for the T-SQL but a mega fail on the maintenance plan as it doesn’t have any option to use this feature. Or at-least I couldn’t figure out yet if it is hidden somewhere.
If you are interested about BACKUP CHECKSUM, here is How to check if backups in SQL Server are taken using BACKUP CHECKSUM?
Popular posts