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?.
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.