Order of maintenance plan operations

Question

Order of maintenance plan operations

CGSJohnson

SSCertifiable

Points: 7208
More actions
March 6, 2008 at 8:21 am

#71708

Good morning. I apologize if this has been covered somewhere else on this web site, but my search didn't find it anywhere.
Is there a correct order in which the maintenance plan operations - integrity check, optimizations and backups - should be performed? Thanks.
Chris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I do not know any standard rule. But I think it is our DBAs' default rule to backup a database before modifying it. At least, we can return to the starting point when we have a backup. Therefore, the order will be:

Backup;

Integrity check/optimization/...

Of course, it will be better to have a backup after modification so that we do not need to redo the tasks in case of disasters.

Donn Polix Default port Points: 1453 More actions · Answer 2

I wonder if the order these tasks show up in maintenance plan wizard has something to do with it (optimization>integrity check>backups). You do not want to keep a backup with inconsistent data, (that' why the option "Perform these tests before doing backups") in integrity check wizard. Anyway, I do check db before the backup.

_____________
Donn Policarpio

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

CGSJohnson

SSCertifiable

Points: 7208

March 11, 2008 at 10:39 am

#788141

Thanks for the feedback.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 4

Hi,

I agree with donpolix, you wouldn't want a backup of a mess, but if time and space permit, do a backup before applying changes.

Thanks,

Phillip Cox

MCITP - DBAdmin

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 5

These operations do appear in a certain order. However, when you click each of them on, a scheduling order goes into play which puts the integrity check first then the optimization, followed by the backup. As far as recoverability, if you were doing full backups nightly and transaction log backups every so often, you would be able to recover anyway, depending on whether or not you do the backup before or after the other two operations. So, I guess there's no right way, huh?

Any thoughts on which dbs should be backed up first? System or user? I read a post on another site that said that system dbs should be backed up last due allow the msdb backup to contain all the data about the other backups.

Thanks...Chris