1

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?

3
  • 2
    I think this is DBA territory, serverfault material.
    – Vinko Vrsalovic
    Aug 6, 2009 at 12:01
  • Yeah, ServerFault. A great idea if you want to take your questions away from the community and put them on a site with squat for a userbase. ServerFault -- for all of your question killing needs. Aug 6, 2009 at 12:43
  • Well, Dave, that's pretty harsh. I've found some excellent answers here on SF, even though i generally have very little interest in most sysadmin topics. Regardless, the answer is to get more questions on here, not less...
    – Shog9
    Aug 7, 2009 at 22:37

4 Answers 4

4

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.

3
  • So just going with my scenario with your suggestion, I would do a full weekly back up, daily differential backup, say hourly transaction log back. With the hourly transaction log, will I be able to append to the log every hour and at the 24th hour (when I have my differential back), truncate the log and restart the process?
    – Nai
    Aug 6, 2009 at 12:23
  • You don't need to truncate your logs, ever, if you're doing frequent log backups. Once you backup the log, the space in the log file for entries you've backed up is freed. You just keep backing up the tran log no matter what, and if you need to restore, you simply apply the logs after your differential until you get to the point in time you want. Aug 6, 2009 at 12:45
  • i agree. most people only need to do Simple FULL backups because they only need to roll back to the last backup. Using "Simple" also makes you not need to have to worry about shrinking logs.
    – djangofan
    Dec 10, 2009 at 21:09
4

There are generally two factors to take into account when determining a backup strategy. Restore Point Objective (RPO) and Restore Time Objective (RTO).

Restore Time Objective

I won't cover RTO at all as that goes in database mirroring/clustering where you keep your backups etc. However keep in mind how long it does take you to get server back online atfter a failure e.g. getting the tapes back onsite, getting a new server, rebuilding the server, etc. RPO & RTO are two figures you should report back to the business and you should test them reguraly.

Restore Point Objective

Restore Point Objective is a point in time you want to recover the database to.

Full Vs Simple

If you need to restore to a specific point in time then you need to move to Full Recovery Mode and Transaction Log backups. Otherwise you will only be able to restore to the point of the last Full or Differential Backup.

Even though most business say that a nightly or 4 hour data loss is acceptable. When you are in that situation it most always isn't. So I always go full recovery with transaction log backups.

Full Recovery

As a general rule I do nightly full backups on most of my databases. I also do differentials every 4 hours and Transaction logs every 15 minutes. Tf your differential backup size starts to exceed your full backup size. It is time to do more frequent full backups.

If your differential backup size starts to exceed the full backup size. It is time to do a full backup as you are losing any benefits of the differential backup in terms of restore speed.

The biggest gotcha will Full Recovery is you must always have the last full backup in the set to be able to restore from. If someone does a manual backup of the database and doesnt keep the backup they will break your set and you won't be able to recover without it. (use backup with copy only)

Also if you are doing Full Recovery make you are doing transaction log backups as otherwise your log files will become unmanageable.

Restoring

To restore in full recovery mode you need. The last full backup closest to your restore point, then the closest diff backup, then you need to roll forward each transaction log to get to the point in time you require.

The restore process is much more difficult but defintely worth it. As you can restore to the exact point in time where the developer sent delete all to the database.

So to answer the question Full Recovery is defintely worth the extra work because you will be thankful to be able to restore to a point in time for various situations. Especially when you have a major failure immediately before a month end financial processing.

1
  • 1
    Hey I know a bit verbose but trying to brain dump some things for others to find. Aug 8, 2009 at 0:52
1

The size of the database plays the large factor in differential versus transaction log backups. On a very large database the time that it takes to do a differential or full backup will exceed requirements for how frequently the database needs to get backed up. For example, in many places I've seen a mix of nightly differential, hourly transaction log and weekly full backups.

Being able to restore to a point in time or checkpoint is another reason that transaction log backups are useful. This feature is unavailable with differential backups if I remember right.

On a small database the time of a differential versus transaction log database might be less significant which might be why you are not seeing that additional value. You may also have simpler requirements for maintenance.

1
  • Right. I quess I was also assuming differential and log backups are exclusive. I've not thought of doing both together. So I guess it would be possible to restore the full db, and use either or the latest transaction log or differential back up.
    – Nai
    Aug 6, 2009 at 12:27
0

For expert information on this check out everything on Paul Randal's blog about backup and transaction logs. Every situation is different and really its only you that can decide what you need. Educate yourself and then go with what will work for the data recovery you need & how much data loss is acceptable.

In the case for instance of a configuration data (ie no user data, only configuration settings) you might go with a simple backup to remove the need to do transaction logs.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .