Just wondering what backup processes you guys have.
I am currently operating a weekly full database backup with daily differential backups.
My understanding is that with such a set up, the difference between Full recovery mode and Simple recovery mode is that with Full recovery mode, I will be able to use the transaction logs to rollback my DB to a specific point in time having applied the latest differential backup.
Assuming that in my scenario, the last differential backup serves as my last and ultimate 'save point', I don't see a need to rollback my DB even further back using the logs. This brings me to my question: Is there any additional benefits to be had using a Full recovery mode for my current backup process?
Best Answer
Well, if you aren't saving the logs, the best you can do is roll back to your last diff.
What I generally do is backup the transaction log on much shorter intervals (maybe every 15 minutes or so). This prevents me from losing a lot of data in the worst-case scenario (the machine burns to the ground). This is a very lightweight operation, so this can be done online with minimal impact.
You should generally only use Full backup mode, and you should back up your transaction log often, unless you can afford to lose up to a full day's worth of data, which is what you're exposed to now.