Hello.
I have only ever been required to take a full back up of my main prod database every night.
Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.
I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:
- Full backup @. 7pm
- Backup tran log every hour after that starting @. 7am (as there are no changes overnight)
How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it?Basically I need to know what to do so it doesn't get too big!
Thanks
That's a good starting strategy.
The tran log will re-use space just the same way it does in simple recovery mode, with one change: It will not re-use space taken up by log records that haven't been backed up yet. Logical when you think about it.
So, with a regular tran log backup job, and a consistent workload, the tran log should reach a steady state, and not need to be shrunk unless it grows unusually due to an extraordinary event, like loading millions of records at once, when that's not the normal pattern.
You can then govern the size of the tran log to a large extent by varying the frequency of your log backups. The more often you back up the logs, the less records are still active due to not having been backed up, so the log doesn't need to grow as large. Also, the more frequently you back up the log, the less work is exposed to loss. Your commitment is to lose no more than an hour, but you can deliver better if you back up more frequently. The flip side of that is that with more frequent log backups, you have more backup files to manage, and restoring becomes more complicated. That's a tradeoff you have to make.
I would also STRONGLY suggest that you do a test restore of a database including log backups so that your procedures for restoring in this configuration are proven. You really don't want to be figuring this out when the database is down and everyone is screaming!
