Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
+1. Good answer. Should probably be migrated to dba.stackexchange ? –  Mitch Wheat Mar 13 '13 at 3:04
    
The question is to old to move to another site apparently. –  mrdenny Mar 13 '13 at 19:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.