5

Our client has agreed to an RPO of one day data loss. So I am going to change back up strategy to eliminate space constraints.

Database is in full recovery mode.

Existing backup plan:-

  1. Once daily we are taking a full back up of the database.

Thinking to change like below:-

  1. Every week, on Sunday or Saturday we are going to take a full back up of the database.
  2. And planning to take transaction backup or differential backup once every day on the database.

This will avoid space constraint for me.

If we take transaction log back up then will the RTO be larger, since we are taking daily only one transaction backup?

Anyways we need to perform transaction log backup to make free space in log files.

So can you people suggest which back up type will be good, either differential or transaction log?

If I am going in the wrong direction please suggest a backup strategy based on my RPO.

Thanks.

Michael Green
  • 25,255
  • 13
  • 54
  • 100
Chanukya
  • 195
  • 7

2 Answers2

8

For clarification

RPO = Recovery Point Objective; the point in time that you will be restoring to in the case of data loss.
RTO = Recovery Time Objective; the duration it will take to restore to the time defined in RPO.

You might be able to answer your question yourself if I supply you with ample information and give you some ideas to ponder.

Recovery Point Objective (RPO)

Is the RPO defined by business really feasible? Will the business survive with an RPO of one day (or 24 hours)?

Recovery Time Objective (RTO)

Can you bring your database(s) back online to a point-in-time defined by the RPO? Please don't forget: The RPO is a duration. If your database crashed as 2pm in the afternoon then you have to be able to restore the data at least to 2pm in the afternoon the day before. (And not just to 6pm the night you took the backup).

Transaction Log Backup: Yes / No?

Seeing as you now know that RPO and RTO can be relevant for your backup concept, I can now answer your question about the database's Transaction Log. A differential and/or full backup of the database will not free the transaction log, if the database is in FULL recovery model.

Reference: Misconceptions around the log and log backups: how to convince yourself (SQLSkills.com, Paul Randall)

You must perform a BACKUP LOG .... to CHECKPOINT the Transaction Log and allow it to free up space should it be required.

Existing Backup Concept

If your RPO is for 24 hours data loss and the RTO is 4 hours (for one database system), how will you fair if you have to rebuild 10 systems or 20 systems? Will you be able to restore all 20 databases to a point-in-time with the given RPO of 24 hours and an RTO of 4 hours?

Possible solution

  1. Perform a BACKUP DATABASE ... once a week (e.g. Sunday)
  2. Perform a BACKUP DATABASE ... WITH DIFFERENTIAL... every other day, except e.g. Sunday
  3. Perform a BACKUP LOG ... every other hour
  4. Store data in a different location other that your main database(s), or even in an off-site location (geographical redundancy)

This can help ensure that you can restore to the RPO requested by your company's Business Continuity (BC) team and Information Technology Service Continuity (ITSC) team.

You will also have to somehow guarantee that the database backups (FULL, DIFF and TLOG) are stored in a location that will not be affected by a down-time/data-loss in your current location (e.g. separate data centre, off-site data centre).

When storing data off-site, ensure that you can still guarantee the RTO if you have to calculate in some additional time for data copy operations or restore over slower network connections.

References

John K. N.
  • 18,854
  • 14
  • 56
  • 117
6

Since client can afford one day data loss why not change recovery model of database to simple and take ONLY full backup and differential backup. The differential backup can be taken once a day. when you say client can afford a day data loss I assume client does not needs point in time recovery. If so their is no need to keep database in full recovery model. Keeping in simple recovery will remove hassle of taking frequent log backups.

The other approach can be keep database in simple recovery and take weekly full backup and daily differential backup, twice in a day. Just adjust the frequency of diff backup as per RTO agreed. I would prefer daily full backup and a differential backup if enough space is present.

Make sure you verify integrity of your backup when you take it, you have option in maintenance plan. It would also be better to restore the backup on other server and run checkdb to make sure the backup is consistent

Shanky
  • 19,148
  • 4
  • 37
  • 58