September 7, 2010 at 4:17 am
I have a query regarding best practice people are using out there for the order of scheduling jobs for the 3 steps of SQL Server maintenance plans:
1) Full Backup
2) Integrity Checks
3) Optimisations
In my place of work we are still using SQL Server 2000 for most of our SQL Instances and in my experience I have always thought it best to run in reverse order that i listed above and do it 3, 2, 1. i.e. Do the Optimisations, Integrity Checks and then the Full Backup. That way I know I have a good backup that is fully optimised and consistent.
The reason I am now querying this is today I received table errors in my SQL error log which turned out to be a bug listed in the article below when DBCC DBREINDEX clashed with Autoshrink on the database. The AutoShrink option is another matter but I am waiting to hear from a 3rd party that it can be turned off.
http://support.microsoft.com/kb/277848
Because the Integrity Checks had ran after the Optimisations the error log reported there were no consistency errors - great! (I double checked and ran a manual DBCC CheckDB as a safety net as well.)
However what i am wondering is because the backup ran after both the optimisations and integrity checks had completed, if the database had been corrupted due to the optimisations I would have backed up the corrupt one. Also because I only keep one days retention of backups due to space I would have had to go to tape for a previous backup. This has me thinking now that I need to change the sequence I schedule these jobs and do Full Backup, Optimisations & Integrity Checks.
I would appreciate some feedback on this and what other people are doing....
Thanks in advance.
September 7, 2010 at 4:47 am
you can add one more step to verify the backup when its being copleted, and it will confirm you either backup is valid set or not.
----------
Ashish
September 7, 2010 at 7:05 am
ashish.kuriyal (9/7/2010)
you can add one more step to verify the backup when its being copleted, and it will confirm you either backup is valid set or not.
Do note that verify does not guarantee that the backup is undamaged and restorable. Not on SQL 2000.
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
September 7, 2010 at 8:40 am
Thanks, I do verify the backups anyway but I thought that was just to check it was a complete backup and not if corruption existed.
This leads me to think more that Full backup should be run prior to other jobs.
September 7, 2010 at 9:22 am
My preference is two maint plans (when I use maint plans)
1) - Rebuild indexes, update statistics
2) - Integrity check, backup database
Integrity check before backup. There is no point whatsoever in backing up a corrupt database.
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
September 7, 2010 at 2:14 pm
GilaMonster (9/7/2010)
My preference is two maint plans (when I use maint plans)1) - Rebuild indexes, update statistics
2) - Integrity check, backup database
Integrity check before backup. There is no point whatsoever in backing up a corrupt database.
In most cases, I use the same strategy - with the rebuild and update statistics scheduled weekly and the integrity check and backup scheduled daily.
I also have one additional plan - that is an hourly (or half-hour, or 15 minute, ...) transaction log backup plan.
Oh, I have one more task in the daily plan - a maintenance cleanup task that is run if the backup is successful.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply