I have been looking into back up design and recovery, and I have not found particular information that I need to design it right for our company.
Currently our recovery model is set for simple with a Full backup being done daily, which I know is wrong for a min production system; I want to go to a full recovery model. So part 1 of my question is: Assuming that we cannot afford to lose anymore than a half hour to an hour of work a day. How would I determine how often to take a Tran log backup and differentials? For example. would I take Tran log backup every hour and differential every 4 hours ?
How would I determine if I have enough space for the full, Tran Log, and differentials that I would be taken?
To insure you can recover and no lose any more than 30 minutes of data - you need to perform transaction log backups at least every 30 minutes. I would recommend every 10 minutes...it will not cause issues with the system and the size of the files will be the same as if you did them every 30 minutes.
Whether or not you need to perform differentials will depend on multiple factors. Is your maintenance window every night large enough to perform full backups every night? If not - then you might consider a weekly full and daily differentials. Other factors to consider would include how long it takes to restore - and the number of files to be restored...
The amount of space you need depends on what you do with the files after they are created. You should be copying those files to offline storage - and sending them offsite in case of disaster. Depending on how fast you can retrieve those files should determine how many versions of the backups you need to keep online and available....
The backups files can (and should) be compressed - this will allow you to keep more versions online. The size of the transaction log backups will have to be monitored for a while to determine how much space is needed...and if you decide to perform differentials they will have to be monitored over time also. It really depends on how often you take full backups - and the amount of changes being made between full backups - that will determine the sizes of both types.
Thanks for the reply this is really helpful. One more question:
You said Whether or not you need to perform differentials will depend on multiple factors. Is your maintenance window every night large enough to perform full backups every night? Right now we are doing Fullback ups ever night so it looks like we could continue doing that. If so what differentials would we do? every few hours?
The only reason to perform differentials would be to allow for a 'quicker' restore process. To restore you would restore the full, the latest differential and then all transaction logs from the differential up to the point in time needed.
Or...you can restore the full and all transaction log backups from the full to the point in time needed.
The only difference is how many files need to be restored - the downside is that you have to insure you have a full chain of backups from the full to the point in time available, including all differentials. That will require more disk space to support the differential backups.
You would only ever need to -- and be allowed to -- restore one differential (they are true differential backups, not incremental backups). That one differential will bring you up to the time of that diff. Then, if needed, you would apply tran log(s) from that point forward. For differential backups, either every 8 or 12 hours has worked well for me.
If at all possible, you want to move all backups to a different disk (or other media) as soon as possible after the backup is complete. You don't want a drive failure to include both the current db files and the most recent backups, as then you've lost them all and must recover to a (much) earlier point.