RTO 15 minutes with differential backups

Question

RTO 15 minutes with differential backups

DBANewbie

Hall of Fame

Points: 3095
More actions
January 25, 2020 at 9:34 am

#3720364

Hi.
I'm working through the official exam reference book for 70-764 by Isakov and some of the sample questions + answers are driving me crazy!
One such example is for a question which asks you to come up with a backup strategy to meet a set of requirements, one of these requirements being that the RTO is 15 minutes.
In the answer section for this question, the author states that an RTO of 15 minutes cannot be met by performing differential backups every 15 minutes but doesn't explain why this is the case. The author states that the correct answer is that log backups should be taken every 15 minutes instead. As far as i know, either of the two solutions could work, and you wouldn't be able to know this for sure until you tested how long it takes for the restore of the differential backups to complete.
Am i mistaken here?
Any help would be greatly appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

You have come across one of the primary reasons why I hate certification courses, exams, and the related books/articles. They're a possibly decent starting point but, oh my...

IMHO, it is quite feasible to actually reduce the RTO by taking more differential backups (you only need to restore the latest one) to reduce the number of transaction log backups that you'd have to restore after doing the initial FULL restore or after a later differential restore. They also seem to have forgotten that the original required FULL restore might take more than 15 minutes all by itself. There has to be more to the precursory information leading up to that question in order for the author to make such a claim.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sterling3721 SSChasing Mays Points: 658 More actions · Answer 2

In my opinion, based on the answer, "an RTO of 15 minutes can be met by log backups every 15 minutes", it's more of an RPO, NOT RTO. log backup every 15 minutes or even differential if feasible can cut data loss to 15 minutes or less(RPO), but does not mean your system be up and running within 15 minutes. For example, if it's fail-over cluster and it's hardware issue, a fail-over only takes seconds(or minutes based on how soon crash recovery can finish); it's standalone instance, you may need to setup SQL server, restore backups(full, differential, log, etc) and it may take hours to be up and running(this is RTO). RTO can be accomplished via database mirroring/log shipping/Always On availability groups(synchronous or asynchronous).

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Recovery Time Objective (RTO) is not determined by what you back up or how frequently. Well, it is, but not the way described here. It's about how long it takes you to recover. In fact, a more frequent differential backup could make RTO faster. Recovery Point Objective (RPO), the amount of data expressed in time, that you're will to lose, that's what is usually covered by the frequency and type of backup.

It all sounds a little backassward.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 4

Backup policy can be leveraged more specifically for Point In Time Recovery. It can be also used for DR where the RTO applies but if your Database Size is huge then you can talk about RTO in minutes. I've had a Production instance in SQL 2008 with SSD drive with IO speed more than 2K, 64 GB RAM and 16 Cores and it was a physical server. The database size was around 850 GB. Restores use to take around an hour for Full Backup sometimes even more. You also need to add the time taken for Differential Backup and obviously Log Backups.

Better RTO can be achieved by keeping a standby Secondary/DR server with replication enabled using Logshipping, AlwaysOn or Mirroring depending upon you version and edition of SQL Server. Recently I've also came across a concept in Azure where there is no Database Replication but the Disk based replication and they call it as ASR (Azure Site Recovery).

P.S. You need to have your Secondary/DR over cloud for ASR.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

DBANewbie Hall of Fame Points: 3095 More actions · Answer 5

Thanks very much to each of you for taking the time to reply, I think that the common conception here is that the given answer doesn't actually fit the question. I'll just exclude it from my practice questions going forwards.