Recovery Point Objective (RPO)

Question

Post reply

Recovery Point Objective (RPO)

lan2022nguyen

SSC-Addicted

Points: 459
More actions
November 18, 2023 at 1:15 pm

#4330105

Does the following backup strategy satisfy the RPO of 24 hours. these are SIMPLE recovery. no transaction
FULL every sunday at 12 am.
DIFF daily at 2am

Viewing 15 posts - 1 through 15 (of 15 total)

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

lan2022nguyen wrote:

Does the following backup strategy satisfy the RPO of 24 hours. these are SIMPLE recovery. no transaction
FULL every sunday at 12 am.
DIFF daily at 2am

Mostly but, personally, I wouldn't tolerate an RPO of 24 hours for any important data. Why are these databases in the SIMPLE Recovery Model?

--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)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

I would only "grant" an RPO of 24 hours for DEV systems, and even then urge then to use full recovery and persue an RPO of max 1 hour.

Don't let them trap you into accepting such RPO for budget reasons. In my early days as a DBA, I've been trapped a couple of times, but at DRP time, it turned out their db being 24 h "behind" was useless and they needed days to manually gather and record everything they to bring the db back into a usable and accurate state.

Nowadays I try to prevent such situation, for myself, but above all, for our company !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 4

Jeff Moden wrote:

Mostly but, personally, I wouldn't tolerate an RPO of 24 hours for any important data. Why are these databases in the SIMPLE Recovery Model?

these are read-only dbs. there's no write/update/delete.

some are updated once or twice a month with the new data load.

is the SIMPLE recovery sufficient in this scenario?

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

Creating a single full backup after its monthly ( or whatever frequency ) operations have been done and it has been switched to "read_only" state, would do the job.

However, you need to consider what should be done during these insert/update/delete operations and maintenance window(?).

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

lan2022nguyen wrote:

Jeff Moden wrote:
Mostly but, personally, I wouldn't tolerate an RPO of 24 hours for any important data. Why are these databases in the SIMPLE Recovery Model?
these are read-only dbs. there's no write/update/delete.
some are updated once or twice a month with the new data load.
is the SIMPLE recovery sufficient in this scenario?

Got it. Johan has, IMHO, the best idea. Just do a FULL after changes.

The next question, though, is are the databases truly "Read Only" where no one can make any changes to the databases until they're set to non-ReadOnly?

--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)

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 7

thank you both @jeff and @johan, i'm still new to db administration and learning about the project/app as well. as such, some questions you asked i'm unable to respond as i'm still learning, not that i'm intentionally avoiding or not responding.

aside from the monthly data loading of a few dbs, we disable inactive logins and add new logins (so insert/update/delete). are these changes to these logins on the master db? if so, we need to set master to FULL and log ship this every 15 minutes.

sorry if my questions doesn't make sense or my thoughts are all over the place. newbie learning in progress...

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 8

12am full to 2am diff is 26 hours for the first one, so no, not 24 hours.

In terms of your last question, changing logins makes changes in master. Changing users would make changes in a user db. Most people don't enable full mode in master, but you could. You wouldn't logship master to another instance as it's specific to that instance, not something you can just move to another instance.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Steve Jones - SSC Editor wrote:

12am full to 2am diff is 26 hours for the first one, so no, not 24 hours.
In terms of your last question, changing logins makes changes in master. Changing users would make changes in a user db. Most people don't enable full mode in master, but you could. You wouldn't logship master to another instance as it's specific to that instance, not something you can just move to another instance.

I may have misunderstood. It thought he was doing DIFs every day, regardless.

Totally agreed about the master database.

--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)

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 10

If diffs are every day, then this would cover things. If there is a break between the full and first diff, which is how I see people set things up (Full Sun, Diff M-Sa), then it misses.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

Totally agreed.

--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)

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 12

how would i backup my master from primary to secondary? and continue to backup new changes (logins, server roles, securables)?

how about new agent jobs on msdb from primaruy to secondary? and continue to backup new changes on msdb?

thanks everyone!

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 13

In SQL Server 2022, there is a container AG (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16), but not in 2019.

You can back up master, but you aren't restoring it elsewhere, as this will also make the restored instance the same name as the original, which isn't allowed on a network.

You can capture login changes (or linked servers) and transfer them, but this is a custom process. There are some ideas in this article on how to do that: https://www.sqlservercentral.com/articles/preparing-for-the-unthinkable-a-disasterrecovery-implementation

lan2022nguyen SSC-Addicted Points: 459 More actions · Answer 14

thanks steve

This reply was modified 1 year, 1 month ago by lan2022nguyen.