I've installed Ola Hallengrens Maintenance scripts and it has created the jobs for me.
DatabaseBackup - SYSTEM_DATABASES - FULL
DatabaseBackup - USER_DATABASES - FULL
DatabaseBackup - USER_DATABASES - DIFF
DatabaseBackup - USER_DATABASES - LOG
DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES
I plan on following his guidelines to the question on his FAQ How should I schedule the jobs?
This depends on your maintenance
window, the size of the databases, the
maximum data loss and many other
things. Here are some guidelines that
you can start with, but you will need
to adjust it to your environment.User databases: Full backup one day a
week. Differential backup all other
days of the week. Transaction log
backup every hour. Integrity check one
day a week. Index optimization one day
a week.System databases: Full backup every
day. Integrity check one day a week.Integrity check after index
optimization. This is because index
rebuilds sometimes can fix database
corruption. Full backup after index
optimization. Then the following
differential backups will be small.
Full backup after the integrity check.
Then you know that the integrity of
the backup is ok. This means first
index optimization, then integrity
check and finally full backup.
My question is still, How should I schedule the jobs?.
In particular:
If I perform a full/diff backup at midnight each day, should I run a transaction log backup at midnight as well? Or, should I make the midnight job run a transaction log back and THEN do a full/diff backup? Or, do I just not perform a transaction log backup at midnight?
How should I setup a job to perform the index optimization, then itegrity check and then the full backup? I don't want the diff backups and transaction log backups to be huge after an index rebuild unless absolutely necessary.
Any advice as to how others have set this up would be great.
Best Answer
This is all going to be very general but here goes.
Full backups daily. Transaction log backups every 15 minutes (more or less depending on how much data loss is acceptable in the event of a total database failure). Index rebuilds or defrags should be done probably weekly (daily for larger databases). Integrity checks should be run daily. If daily isn't possible (integrity checks are very CPU and IO intensive) then weekly at least.
The transaction log backup directly after the index maintenance has been done will be quite large compared to the others. There is nothing you can do about this. Do NOT change the database from full to simple recovery for the index rebuild operation. Do NOT shrink the files after the work is complete. Let the logs grow to the size they need to be and leave them there.