| Andrew J. Kelly 2006-10-24, 6:28 pm |
| First off it is never a good idea to issue a FULL backup on a database that
is in the process of an index rebuild or an Integrity check. Each of these
have the potential to use a lot of resources and together with a Full backup
can really hurt performance if the hardware is not optimal. Second what good
is a backup of half the db indexes rebuilt? But whether to do it before or
after is debatable and each has it's pros & cons. If you do it before and
something happens during the rebuild you always have a good starting point.
But then when you are thru you have up to 100% of the indexes modified.
Some take one before and after. It depends on what you need and what makes
sense for your situation. Most of the time I prefer to do the backup after
the reindexing. Since the DBREINDEX is fully logged it should be able to
roll back if there is an issue. If you have good log backups you should
always be able to get to a point in time restore as well.
--
Andrew J. Kelly SQL MVP
"Victor D" <ssto@kdy.com> wrote in message
news:Xns983FB9F32240
9sstokdysfs@207.46.248.16...
> What's the preferred order for the following jobs:
>
> Integrity Check
> Data Optimization
> Complete Backup
>
> The databases I'm now administering are set up to perform the Integrity
> Check, followed by the Data Optimization which is barely 25% done when the
> Complete Backup begins.
>
> I have one person saying this is the proper method since the backup is
> that
> for a fully optimized database while another person says that optimizing
> runs
> the risk (however small but made larger by the running of a
> near-simultaneous
> backup) of ruining the data and resulting in a bad backup.
>
> Victor D.
|