SQL Server backup and restore process

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?

Solution 1:

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.

Solution 2:

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.