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