How to back up and recover a database (Backup to Basics series)
Have we got a packed episode for you. This week in our continued Backup to Basics series, we dive deep into the various options for backing up and recovering databases, along with the pros and cons of each. Want to figure out the best way to back up your traditional or modern database? This is your episode. As usual, Mr. Backup and Prasanna also manage to make it fun. This is a great episode and we think you'll enjoy it.
Mentioned in this episode:
Interview ad
Hi, and welcome to Backup Central's Restore it All podcast.
W. Curtis Preston:I'm your host, w Curtis Preston, aka mr.
W. Curtis Preston:Backup, and have with me my cloud backup experiment,
W. Curtis Preston:collaborator, Prasanna Malaiyandi.
W. Curtis Preston:How's it going, Prasanna?
Prasanna Malaiyandi:Good, Curtis, and how much have you spent on cloud these days?
W. Curtis Preston:Um, I don't know.
W. Curtis Preston:I don't know.
W. Curtis Preston:I don't know.
W. Curtis Preston:I'm up, I'm, I'm probably up to about $20 I think so far, just
W. Curtis Preston:because I keep buying little things and, you know, um, and, uh, the.
W. Curtis Preston:The, the, the quickest one.
W. Curtis Preston:You know, I tried, I did like, I, I tried the rsync.net cuz I,
W. Curtis Preston:I know we had those guys on here.
W. Curtis Preston:Um, and I will say it was the simplest, right, cuz basically you just get it,
W. Curtis Preston:it's a, you know, it's a destination.
W. Curtis Preston:You can send stuff to it.
W. Curtis Preston:And it was really very straightforward.
W. Curtis Preston:Um, the,
Prasanna Malaiyandi:But for the amount of data you
Prasanna Malaiyandi:had, it didn't make sense.
W. Curtis Preston:For the amount of data I had, it was overkill because they have a
W. Curtis Preston:minimum charge of 10 bucks a month, which is for 680 gigabytes, which, you know,
W. Curtis Preston:it's 10 bucks a month isn't a lot, but.
W. Curtis Preston:Um, it just, there are other options that are less expensive than that and, um,
W. Curtis Preston:it was just the experiment of, of trying to, I, I'm still, the, the main project
W. Curtis Preston:here is trying to figure out what's the best way to properly back up my iphotos.
W. Curtis Preston:This all started with a conversation on the podcast and
W. Curtis Preston:me re just really realizing.
W. Curtis Preston:My, for my own self that iphotos, you know, that I'm not backing up my photos
W. Curtis Preston:because, or my, my photos in my iPhone and, uh, it used to be called iphoto
W. Curtis Preston:and now they just call it photos and it makes for weird internet posts.
W. Curtis Preston:Because Apple or iCloud is not a backup.
W. Curtis Preston:It is a synchronization.
W. Curtis Preston:Right.
W. Curtis Preston:And so, um, I'm, I, I'm on I think experiment number three.
W. Curtis Preston:Um, And the one that I think will be the ultimate winner.
W. Curtis Preston:I'm not gonna reveal my my thing yet, but I think it'll be the ultimate winner.
W. Curtis Preston:And in the midst of this, I tried out a new product called, um, what is it called?
W. Curtis Preston:I should have it here on my desktop.
W. Curtis Preston:It's called System.
W. Curtis Preston:Uh, it's, it's it's system, but it's, it's like, C I S D E M, duplicate finder.
W. Curtis Preston:Um, and it has some pretty cool technology in it where it will
W. Curtis Preston:actually find similar photos,
Prasanna Malaiyandi:hmm.
W. Curtis Preston:Not just identical photos, but similar photos and like low
W. Curtis Preston:and high-res versions of the same photo.
W. Curtis Preston:Um,
Prasanna Malaiyandi:you took a burst.
W. Curtis Preston:Yeah, well, a b a burst is, it would be, yeah, it
W. Curtis Preston:would be nearly identical photos.
W. Curtis Preston:Uh, but this will find, so it ended up finding 1500 similar photos, and
W. Curtis Preston:then it can, it can smart select all of the dupes for you and delete them.
W. Curtis Preston:Uh, and I did that.
W. Curtis Preston:Um, anyway, it's been, it's been, uh, it's been a journey, you know, as they
Prasanna Malaiyandi:So are your experiment going to be
Prasanna Malaiyandi:finished by the time we have Daniel Rosehill on the podcast?
W. Curtis Preston:Um, yeah, probably, uh, when, what are we having him on?
W. Curtis Preston:How and how many weeks
Prasanna Malaiyandi:week,
W. Curtis Preston:early next week?
Prasanna Malaiyandi:next Wednesday.
W. Curtis Preston:Mm.
W. Curtis Preston:Might not be.
W. Curtis Preston:Cause you know, like everything I try takes about a day.
W. Curtis Preston:So we'll see.
W. Curtis Preston:We'll see, we'll see, we'll see.
W. Curtis Preston:Um, but speaking of modern, you know, hashtag first world problems.
W. Curtis Preston:I'm gonna record our usual disclaimer, uh, Prasanna and I
W. Curtis Preston:work for different companies.
W. Curtis Preston:And, uh, this is not a podcast of either company.
W. Curtis Preston:And these are our opinions, not theirs.
W. Curtis Preston:Uh, please reach out to us at Curtis Preston at gmail,
W. Curtis Preston:or at WC Preston on Twitter.
W. Curtis Preston:And, um, And Owen LinkedIn, yes, linkedin.com/in/mrbackup.
W. Curtis Preston:And you can find us.
W. Curtis Preston:And please also rate us, uh, go to your podcast.
W. Curtis Preston:That helps other people find us.
W. Curtis Preston:If you like the show, help other people find it by giving us,
W. Curtis Preston:you know, stars and whatnot.
W. Curtis Preston:So we're in our continued backup to basic series here.
W. Curtis Preston:We're in part three, which, uh, the first part was about traditional data sources.
W. Curtis Preston:The second is about, uh, is about databases.
W. Curtis Preston:But in that episode, we focused mainly on the architecture, the
W. Curtis Preston:different types of databases.
W. Curtis Preston:The different ways that they sort of exist.
W. Curtis Preston:And, uh, this week we're gonna talk about basically the, you know, the
W. Curtis Preston:meat and potatoes, which is how you actually back up, uh, databases.
W. Curtis Preston:So, um, the.
W. Curtis Preston:What's that?
W. Curtis Preston:You don't,
Prasanna Malaiyandi:said no one ever.
W. Curtis Preston:up the databases said no one.
W. Curtis Preston:Uh, yeah.
W. Curtis Preston:Yeah.
W. Curtis Preston:I mean, you know, the, the only ones I, I'd say the only data
W. Curtis Preston:databases that I would consider valid not to be backed up are
Prasanna Malaiyandi:Test.
W. Curtis Preston:database or something like that.
W. Curtis Preston:Test, yeah.
W. Curtis Preston:Test tester dev.
W. Curtis Preston:An actual dev.
W. Curtis Preston:Not, you know.
W. Curtis Preston:Sometimes dev is not
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Yep.
W. Curtis Preston:Um, so, and, and I'll say that a lot of the things, in
W. Curtis Preston:fact, many of the options that we're gonna talk about really only apply
W. Curtis Preston:to if you are hosting the database on a server or VM that you control.
Prasanna Malaiyandi:Mm-hmm.
W. Curtis Preston:Right.
W. Curtis Preston:Um, because, you know, it'll become obvious.
W. Curtis Preston:And, and so those of you that, that, you know, you're using RDS
W. Curtis Preston:or something like that, um, then.
W. Curtis Preston:Um, you know, you're gonna be like, well, that doesn't work for me.
W. Curtis Preston:Well, you know, if it doesn't work for you, it doesn't work for you.
W. Curtis Preston:Right?
W. Curtis Preston:So, do you remember what what we said was the, the main pro, you know, most
W. Curtis Preston:databases are residing on files, right?
W. Curtis Preston:There are a handful of people out there that like to use
W. Curtis Preston:raw devices for databases.
W. Curtis Preston:What?
Prasanna Malaiyandi:especially with
W. Curtis Preston:Right.
Prasanna Malaiyandi:Yeah.
W. Curtis Preston:Yeah.
W. Curtis Preston:Okay.
W. Curtis Preston:Most databases that are running as an application inside servers
W. Curtis Preston:are really just a bunch of files.
W. Curtis Preston:You know, they're stored as
Prasanna Malaiyandi:a pile.
Prasanna Malaiyandi:Yeah,
W. Curtis Preston:system.
W. Curtis Preston:Right.
W. Curtis Preston:And we talked about last week that you can't just go and back those up.
W. Curtis Preston:You were the one that, you know, you brought that up that, um, I might have
W. Curtis Preston:just edited that so it's fresh in my mind.
Prasanna Malaiyandi:I was like, how do you know
W. Curtis Preston:were the one that brought, yeah.
W. Curtis Preston:You were the one that brought that up about um, that you can't just
W. Curtis Preston:go back up those files, right?
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Yeah, because if you did, right, given how databases operated and they're
Prasanna Malaiyandi:constantly modifying files, right?
Prasanna Malaiyandi:You might get a file at a different point in time than something else, or even
Prasanna Malaiyandi:within a file at different points in time, cuz it's not like you're taking a
Prasanna Malaiyandi:snapshot and then doing a backup, right?
Prasanna Malaiyandi:When you do a
Prasanna Malaiyandi:normal file
W. Curtis Preston:exactly.
W. Curtis Preston:And.
W. Curtis Preston:And even if you did take a snapshot, um, that might not be consistent.
W. Curtis Preston:Right.
W. Curtis Preston:Um, you
Prasanna Malaiyandi:Uh,
W. Curtis Preston:because
Prasanna Malaiyandi:Do
Prasanna Malaiyandi:you wanna talk about that now?
W. Curtis Preston:might not.
W. Curtis Preston:I do.
Prasanna Malaiyandi:Yes.
Prasanna Malaiyandi:So, so, so yeah.
Prasanna Malaiyandi:Having worked
Prasanna Malaiyandi:at storage vendors, I, yeah.
Prasanna Malaiyandi:Having worked at storage vendors, I know some database companies
Prasanna Malaiyandi:are very particular to say that we do not guarantee recovery.
Prasanna Malaiyandi:From a snapshot based copy.
Prasanna Malaiyandi:However, in based on experience, 95, 90 9% of the time it works.
Prasanna Malaiyandi:But there's no guarantee
Prasanna Malaiyandi:because of
W. Curtis Preston:not.
Prasanna Malaiyandi:I know.
Prasanna Malaiyandi:I know, I
W. Curtis Preston:Well, uh, again, you're, wait, wait, wait.
W. Curtis Preston:I'm specifically talking about taking a snapshot without doing
W. Curtis Preston:anything special with the database.
Prasanna Malaiyandi:Yes.
Prasanna Malaiyandi:That's what I'm talking
W. Curtis Preston:That's, that's the scenario that I'm, I'm talking about
W. Curtis Preston:what is referred to as, uh, well, darn it, we're getting ahead of ourselves.
W. Curtis Preston:Okay.
W. Curtis Preston:Um, so we're getting, we're getting ahead of ourselves.
W. Curtis Preston:All right.
W. Curtis Preston:So the, the first, uh, type of database backup is called a cold backup.
W. Curtis Preston:Do you want to talk about that?
Prasanna Malaiyandi:Yeah, so for a cold backup, it's.
Prasanna Malaiyandi:Probably the safest way to do a backup, right?
Prasanna Malaiyandi:Because you're basically shutting down the database completely.
Prasanna Malaiyandi:You're guaranteed to have no writes happening to your database at all
Prasanna Malaiyandi:during this time, and then you're basically taking your copy once you're
Prasanna Malaiyandi:done or you take your snapshot and then you back up from there, whatever
Prasanna Malaiyandi:mechanism you want to use, right?
Prasanna Malaiyandi:Once that's done, then you bring the database back online and then
Prasanna Malaiyandi:writes start again until you're.
Prasanna Malaiyandi:Hundred percent guaranteed that nothing should be going and
Prasanna Malaiyandi:modifying that database file while you're doing your backup.
W. Curtis Preston:It has
Prasanna Malaiyandi:The downside is
W. Curtis Preston:downside,
Prasanna Malaiyandi:
Speaker:big, yeah, it's great.
Prasanna Malaiyandi:
Speaker:As long as no one needs to use the database during this process, right?
Prasanna Malaiyandi:
Speaker:Because you literally are bringing the database down, which means
Prasanna Malaiyandi:
Speaker:any applications, any clients who are connecting to that
Prasanna Malaiyandi:
Speaker:database, they're like, bye-bye.
W. Curtis Preston:And you know, I've been doing backups a long time and I can't
W. Curtis Preston:remember a single client that did this have, have you ever seen anybody do this?
Prasanna Malaiyandi:I've never seen anyone do this.
W. Curtis Preston:Yeah, because it, it means, it's just, it, it
W. Curtis Preston:goes back again back in the day.
W. Curtis Preston:Um, the, the Unix, so you, so, you know, I grew up around Unix systems, right?
W. Curtis Preston:And the, the command that we used to back up the file systems back then was dump.
Prasanna Malaiyandi:Mm-hmm.
W. Curtis Preston:Do, do you know what the Dump Man page recommended
W. Curtis Preston:when you dump a file system?
Prasanna Malaiyandi:no.
W. Curtis Preston:This.
W. Curtis Preston:It recommended that you unmount the file system
Prasanna Malaiyandi:Tit
W. Curtis Preston:and then run dump on it.
W. Curtis Preston:No one did that.
Prasanna Malaiyandi:No.
W. Curtis Preston:No one did that.
W. Curtis Preston:But this is a very safe way and if you have a, um, you know, if you have a world
W. Curtis Preston:where nothing is happening, It at night.
W. Curtis Preston:This is a completely viable way to get a solid backup of the database.
W. Curtis Preston:Right.
W. Curtis Preston:Um, it's just like, you know, like you said, it comes with, with, uh, one
Prasanna Malaiyandi:Just a, a small, small curve, a small downside.
W. Curtis Preston:Yeah.
W. Curtis Preston:Small, small.
W. Curtis Preston:Now here.
W. Curtis Preston:Now this next one, um, it, it is, should be one that you might remember from.
W. Curtis Preston:Your former employer, and I'm generically calling it Split Replica,
Prasanna Malaiyandi:you wanna talk about
W. Curtis Preston:you know, to which I'm referring to
Prasanna Malaiyandi:This is
W. Curtis Preston:former employer
Prasanna Malaiyandi:my former, former, former, former employer,
W. Curtis Preston:starts with an e.
Prasanna Malaiyandi:Yes, so this is, yeah, where you basically have a replica
Prasanna Malaiyandi:copy, which of course everyone is gonna have a replica of their database because
Prasanna Malaiyandi:you need that for availability, or they might be using for availability.
Prasanna Malaiyandi:And whenever you need to do a backup, right, you basically stop replicating.
Prasanna Malaiyandi:And you basically split the replica.
Prasanna Malaiyandi:So the replica is at a point in time you do the backup off of the replica.
Prasanna Malaiyandi:Once the backup is done, then you resynchronize and then
Prasanna Malaiyandi:continue doing your availability
W. Curtis Preston:yeah.
W. Curtis Preston:The.
W. Curtis Preston:E
W. Curtis Preston:EMC had the term b c V for this, right?
W. Curtis Preston:The business continuance volume.
W. Curtis Preston:Um,
Prasanna Malaiyandi:Business
W. Curtis Preston:is a, another great way.
W. Curtis Preston:Yeah.
W. Curtis Preston:It's, yeah.
W. Curtis Preston:What did I say?
W. Curtis Preston:Business.
W. Curtis Preston:Oh, business con.
W. Curtis Preston:Yeah.
W. Curtis Preston:Um, yeah, I thought it was continuance on, on the BBC V, but,
W. Curtis Preston:uh, whatever, it doesn't matter, you
Prasanna Malaiyandi:On the symmetric?
Prasanna Malaiyandi:Yes.
W. Curtis Preston:on the symmetric, yeah.
W. Curtis Preston:Been a while.
W. Curtis Preston:It's been, it's been a minute since I've done this.
W. Curtis Preston:Um.
W. Curtis Preston:And this is essentially the same thing as a cold backup, but just
W. Curtis Preston:how you get that cold backup, right?
W. Curtis Preston:You stop writes for a second.
W. Curtis Preston:You, you, you split the replica, et cetera, right?
W. Curtis Preston:Um, that's
Prasanna Malaiyandi:though, Curtis?
W. Curtis Preston:viable way.
W. Curtis Preston:Well, the downside, uh, is cost, right?
W. Curtis Preston:You know, you actually said everybody's got a replica.
W. Curtis Preston:Everybody doesn't have a replica, right?
W. Curtis Preston:Everybody has a replica maybe in Salesforce.
W. Curtis Preston:Uh, so I'm sorry.
W. Curtis Preston:Uh, maybe in.
W. Curtis Preston:EMC Dreamland sales, you know, EMC salesperson, dreamland.
W. Curtis Preston:But, uh, that's the big, that's the big cost.
W. Curtis Preston:And also complexity, right?
W. Curtis Preston:I remember lots of scripts.
W. Curtis Preston:Um, you know, if the, the split can fail, the rejoin can fail, you know, all
W. Curtis Preston:these kind of things, just complexity.
Prasanna Malaiyandi:but isn't also another downside, the fact that when
Prasanna Malaiyandi:you're, do you now have to decide, do I want a replica copy or do I want a backup?
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:For a period of time, it could be one or the other, right?
Prasanna Malaiyandi:Because when you split it, you're no longer getting updates, which
Prasanna Malaiyandi:means that your protection of that, of your production is now
Prasanna Malaiyandi:restricted until your backup is done.
W. Curtis Preston:Yep.
W. Curtis Preston:Yeah, exactly.
W. Curtis Preston:Um, so you're, so, you're,
Prasanna Malaiyandi:
Speaker:What's more important?
W. Curtis Preston:you're, yeah.
W. Curtis Preston:Yeah, I, I'd say at that point you're no worse off than people
W. Curtis Preston:that don't have a replica, but, which is I think most people.
W. Curtis Preston:But, um, I, so I really just go back to the, the downside
W. Curtis Preston:of that is cost and complexity.
W. Curtis Preston:Most people, at least most people that I've worked with, didn't have
W. Curtis Preston:the money to pay for A, B, C V just for the purposes of backup.
W. Curtis Preston:Right?
W. Curtis Preston:Because then also they would show you another, Right.
W. Curtis Preston:Another symmetric to replicate that too.
W. Curtis Preston:And, um, yeah.
W. Curtis Preston:And, and if, if you needed synchronous and you could, you
W. Curtis Preston:could end up with three symmetric by the time you were done, right?
W. Curtis Preston:Four copies of the data.
W. Curtis Preston:You had the local, the, the primary copy, the B c v, which
W. Curtis Preston:was physically attached to that.
W. Curtis Preston:Then you had another symmetric that you were replicating to,
W. Curtis Preston:uh, symmetrical synchronously.
W. Curtis Preston:Yeah, thank you.
W. Curtis Preston:Replicating too synchronously.
W. Curtis Preston:And then you could replicate asynchronously to another one.
W. Curtis Preston:Uh, man that sold a lot of disk back in the day.
Prasanna Malaiyandi:srd, F s, and Srd.
Prasanna Malaiyandi:S a.
Prasanna Malaiyandi:Yep.
W. Curtis Preston:Yeah, exactly right.
W. Curtis Preston:Um, so the next one is what I would say, my usual way that
W. Curtis Preston:I'm backing up databases.
W. Curtis Preston:Um, at least in a case of, um, Like Oracle,
Prasanna Malaiyandi:Hmm.
W. Curtis Preston:Oracle and Informix, which is still around, um, Sybase
W. Curtis Preston:to a lesser degree, not SQL server it didn't have this functionality.
W. Curtis Preston:But that is this concept of hot backup
Prasanna Malaiyandi:Does SAP support it?
W. Curtis Preston:I don't know, um, I never had to admin sap, so I don't
W. Curtis Preston:know the answer to that question, but
Prasanna Malaiyandi:I stumped Backup.
W. Curtis Preston:So I'm glad I'm I'll, I'll make that a social post.
W. Curtis Preston:Um, the, um, You know, if, if you've ever Oracle admin and Oracle database, there
W. Curtis Preston:is this, there is this command, it used to you, it used to be you had to say
W. Curtis Preston:alter table space, begin backup, and you had to do this a table space at a time.
W. Curtis Preston:And if you don't know what a table space is, it's literally the space
W. Curtis Preston:where you put the tables, right?
W. Curtis Preston:Um, you know, you have, you have some data files that form a table space and
W. Curtis Preston:you put tables in those table spaces.
W. Curtis Preston:We talked about that on last week's episode.
W. Curtis Preston:Um, But now there's an alter database.
W. Curtis Preston:Uh, begin backup command.
W. Curtis Preston:And what it does in the case of Oracle, I can speak specifically,
W. Curtis Preston:it doesn't halt writes.
W. Curtis Preston:I actually thought, I used to think that it halt halted writes.
W. Curtis Preston:It doesn't halt writes.
W. Curtis Preston:What it does is it changes what it logs in the redo logs and it,
W. Curtis Preston:it, it logs, the changed blocks rather than the vector, right?
W. Curtis Preston:Because basically it's, it's storing all of the blocks that are changing while the
W. Curtis Preston:backups are happening so that when you, uh, go to try to use these oracle knows
W. Curtis Preston:that, oh, this, this backup occurred during a ti a period when it was, um,
Prasanna Malaiyandi:Inconsistent.
W. Curtis Preston:backup mode.
W. Curtis Preston:And so it can use those BA blocks to then, um, to then fix the blocks that
W. Curtis Preston:change during the backup in case you got them before they were changed.
W. Curtis Preston:Right.
W. Curtis Preston:Um, and it's a, it's a really cool way in that you just need a pretty basic script.
W. Curtis Preston:That you run at the time of backup, you just say, alter database, begin
W. Curtis Preston:backup, and then you can back up that database however you want.
W. Curtis Preston:We've talked about a couple ways.
W. Curtis Preston:You can, you know, just split shot or snapshots.
W. Curtis Preston:You can do a regular, uh, file system backup, which is the way
W. Curtis Preston:that I've historically done it.
W. Curtis Preston:Um, and, and the other nice thing about this is historically you, you quite
W. Curtis Preston:often would pay extra for database backup
Prasanna Malaiyandi:functional, yeah.
W. Curtis Preston:Um, that's, that's become a little bit
W. Curtis Preston:less in vogue, um, these days.
W. Curtis Preston:But we've kind of gone with capacity base, um, pricing.
W. Curtis Preston:But the, it used to be that you would pay a lot extra.
W. Curtis Preston:The other, um, The, the di, the disadvantage of this was that the DBAs
W. Curtis Preston:couldn't really control backups, right?
W. Curtis Preston:So, uh, this was always a war between, you know, those of us that wanted to
W. Curtis Preston:have the backup system control the backups or the DBAs control the backups.
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Oh man, it's so, I never administered databases, but I visited many, many
Prasanna Malaiyandi:customers and it would always be interesting getting the backup folks
Prasanna Malaiyandi:and the DBAs into this room at the same time, and as we talk about solutions or
Prasanna Malaiyandi:as I would talk about solutions, they'd be like, no, I want control of this.
Prasanna Malaiyandi:No, I want control because I don't trust the other guy.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:It's just that back and forth that goes on, and I would say it
W. Curtis Preston:Yeah.
Prasanna Malaiyandi:It was notorious, especially for Oracle deployments, right?
Prasanna Malaiyandi:I think.
W. Curtis Preston:It was, I dunno what that was about, but it was, yeah.
W. Curtis Preston:And, and, and yet Oracle has, I think, the best answer that makes both sides happy.
W. Curtis Preston:Right.
W. Curtis Preston:Uh, we're, we're gonna get to that in a minute, but
Prasanna Malaiyandi:the one thing
Prasanna Malaiyandi:I think that you need to consider as a disadvantage, or just to be careful
Prasanna Malaiyandi:of when you are using Hot backup is, I know Curtis, you mentioned sort of
Prasanna Malaiyandi:logging those change blocks, right?
Prasanna Malaiyandi:This causes additional load on your database, so you wanna make sure that.
Prasanna Malaiyandi:Right, that you're not, that you've sized your storage systems
Prasanna Malaiyandi:to account for this as well.
Prasanna Malaiyandi:So wherever you're storing your logs, your archive, do we do logs?
W. Curtis Preston:Yeah, that's a good point.
W. Curtis Preston:And it brings up a story.
W. Curtis Preston:Um, I know I've told this story on here before, but it's apropo to this moment.
W. Curtis Preston:So I was at a very large oil and gas company.
W. Curtis Preston:This was very early in my career.
W. Curtis Preston:I.
W. Curtis Preston:And they had this, this database server that had never been backed up.
W. Curtis Preston:And, uh, it was a d s s system decision support system.
W. Curtis Preston:Uh, I don't even know what it did, but that's what it was called.
W. Curtis Preston:And it got a nightly upload of data from the mainframe.
W. Curtis Preston:And then the new data would be there the next day.
W. Curtis Preston:Well, because the upload occurred at night, we couldn't do a cold
W. Curtis Preston:backup, so I, I found out that it had never been backed up and I'm like,
W. Curtis Preston:guys, we have to get this backed up.
W. Curtis Preston:And the D B A, he had a couple of really, Weird conceptions
W. Curtis Preston:about the way hot backups worked.
W. Curtis Preston:Basically, the, the, the, in order to do hot backups in Oracle, the database has
W. Curtis Preston:to be running in archive log mode, right?
W. Curtis Preston:He had all his databases running in no archive log mode.
W. Curtis Preston:Don't even get me started.
W. Curtis Preston:So he had this weird belief that if I put the database in archive log
W. Curtis Preston:mode, that it would cause corruption.
W. Curtis Preston:I'm like, can you please go call Oracle support cuz you know you're crazy talk.
W. Curtis Preston:Um, and Oracle assured him that this feature that everyone uses is fine.
W. Curtis Preston:But then he had this other thing, what, what you were alluding to was
W. Curtis Preston:that putting the database in backup mode was going to cause an increase.
W. Curtis Preston:A, a performance decrease on his database.
W. Curtis Preston:And so he was going to, uh, the, the, the nightly loads, which were occurring
W. Curtis Preston:at the same time as the, the backup would, uh, take significantly longer.
W. Curtis Preston:And I ended up betting him that, um, For, for some reason what I'm reme
W. Curtis Preston:remembering was that it was 5%, right?
W. Curtis Preston:I, I said the backups won't, or the, the, the uploads or ingest of
W. Curtis Preston:this data wouldn't take more than 5% longer than it, cuz it was a very
W. Curtis Preston:documented process that happened.
W. Curtis Preston:Uh, and we, we did it, we put it, we put it in archive log mode.
W. Curtis Preston:We ran the first ever backup.
W. Curtis Preston:Um, oh, and by the way, um, the, the, the backup product that I was using,
W. Curtis Preston:Used, um, some features of Solaris that were well known to be buggy in
W. Curtis Preston:the version of the OS that we had.
W. Curtis Preston:So there were some patches.
W. Curtis Preston:So we had to, I had to load these patches, uh, so I had to load the patches.
W. Curtis Preston:I had to put the, uh, it was the, the jumbo patch, the Solaris jumbo patch.
W. Curtis Preston:Right.
W. Curtis Preston:We had to load the latest one, or else when I ran the database, when I ran the
W. Curtis Preston:backup, it would crashed the server.
Prasanna Malaiyandi:that would be
W. Curtis Preston:I had to put on the jumbo patch.
W. Curtis Preston:Yeah.
W. Curtis Preston:And, and I, and I had to put it on archive log mode.
W. Curtis Preston:And then, um, I got the first ever backup.
W. Curtis Preston:And we did the first ever, uh, uh, ingest of this mainframe
W. Curtis Preston:data while running a backup.
W. Curtis Preston:And it took like 5.2% longer.
W. Curtis Preston:And so I ended up buying lunch for this guy, and it, it was
W. Curtis Preston:on like a, like a Thursday.
W. Curtis Preston:And then over the weekend the system crashed and we lost five disc drives and
W. Curtis Preston:I had a backup and, you know, and so I was like, I'm cock of the walk, right?
W. Curtis Preston:So I came in Monday morning and, and he's like, I bet, I
W. Curtis Preston:bet you think you're something.
W. Curtis Preston:I'm like, yeah, I kinda do.
W. Curtis Preston:Right?
W. Curtis Preston:And he said, well, you get no credit for saving our ass.
W. Curtis Preston:I'm like, why?
W. Curtis Preston:He goes, well, for two reasons.
W. Curtis Preston:He goes, I don't know what caused, uh,
Prasanna Malaiyandi:caused the dystrophy.
W. Curtis Preston:Yeah.
W. Curtis Preston:So I think maybe the extra, the extra, you know, stuff you were
W. Curtis Preston:doing was like exercising the disk more than they were used to.
W. Curtis Preston:And so they failed because of that.
W. Curtis Preston:Or maybe it was those patches you put in either way, I think you caused a failure,
W. Curtis Preston:so you get no credit for saving it.
W. Curtis Preston:And I was like, whatever dude.
Prasanna Malaiyandi:Oh man.
W. Curtis Preston:Uh, good times.
W. Curtis Preston:Um, so, um, All right, so that's the hot backup mode.
W. Curtis Preston:Oh, by the way, the, the other downside, any backup method that involves
W. Curtis Preston:writing and maintaining custom scripts,
Prasanna Malaiyandi:Oh
W. Curtis Preston:it, it is not to be preferred over backup
W. Curtis Preston:methods that don't require, right.
W. Curtis Preston:Uh, I would prefer you not use a system that that doesn't use custom
Prasanna Malaiyandi:because just imagine that someone forgot to add a
Prasanna Malaiyandi:database table into the backup script.
Prasanna Malaiyandi:And all this time, and it runs for a year and everyone thinks, oh yeah, everything's
Prasanna Malaiyandi:backed up until a failure happens.
Prasanna Malaiyandi:And it's like, oops, we forgot about that cuz no one told me.
W. Curtis Preston:see, this is why my backup scripts queried the tables.
W. Curtis Preston:And I got a list of all the table spaces and then I put each table space and
W. Curtis Preston:back 'em up cuz I knew what I was doing.
W. Curtis Preston:But you are correct.
W. Curtis Preston:Not everybody, not
Prasanna Malaiyandi:not everyone
Prasanna Malaiyandi:does.
Prasanna Malaiyandi:Yeah.
W. Curtis Preston:Exactly.
W. Curtis Preston:All right.
W. Curtis Preston:Uh, and then we have snap and sweep and dump and sweep.
W. Curtis Preston:All right.
W. Curtis Preston:So, um, snap and sweep we've kind of covered, but basically the
W. Curtis Preston:idea is, uh, hopefully put your database in backup mode then.
W. Curtis Preston:Take a snapshot.
W. Curtis Preston:And you're right, a lot of people don't, and it works 99.9% of the time.
W. Curtis Preston:And I would say I'm even okay with that as long as we got all the transaction
W. Curtis Preston:logs going far back as we need them to go.
W. Curtis Preston:Right?
W. Curtis Preston:So if last night's backup doesn't work, then right?
W. Curtis Preston:We can go to yesterday's backup.
W. Curtis Preston:But remember, remember, remember, remember, remember the story
W. Curtis Preston:that I told last episode?
W. Curtis Preston:This is, this launched my career, so when I hear people go, it works 99%
W. Curtis Preston:of the time, I'm like, well, it didn't for me and it didn't for six weeks.
W. Curtis Preston:Right.
W. Curtis Preston:So this is what I'm saying.
W. Curtis Preston:Right.
W. Curtis Preston:Anyway, but I didn't use a snapshot.
W. Curtis Preston:I was using dump and I didn't unmount the file system.
W. Curtis Preston:But yeah, so snap and sweep.
W. Curtis Preston:And what is the, what's the sweep Prasanna
Prasanna Malaiyandi:Basically taking it and putting it somewhere else, right?
Prasanna Malaiyandi:Snapshot just gives you the point in time, but it's on the same system,
Prasanna Malaiyandi:which as we all know, is not a backup.
Prasanna Malaiyandi:So therefore take it and then put it somewhere else, right?
Prasanna Malaiyandi:Move it off box, move it to a different system, right?
Prasanna Malaiyandi:Whatever it is, whatever mechanism you use, do that.
Prasanna Malaiyandi:So when I, so one of the first projects I worked at, at my former
Prasanna Malaiyandi:employee, which starts with an ee.
Prasanna Malaiyandi:Was actually, how can you make backups more efficient for these large databases?
Prasanna Malaiyandi:Because when you started talking about 20 terabyte or 200 terabyte
Prasanna Malaiyandi:database instances, right, at the time, there was no great mechanism to
Prasanna Malaiyandi:back that up without sort of causing lots of pain for a lot of people.
Prasanna Malaiyandi:And so one of the mechanisms that we created was a product or
Prasanna Malaiyandi:a feature called Protect Point, which basically took a snapshot.
Prasanna Malaiyandi:An application consistent snapshot on your primary storage.
Prasanna Malaiyandi:Use different data movers to actually move that copy from storage to at
Prasanna Malaiyandi:the time a data domain appliance.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:So being able to efficiently move it, because the other problem
Prasanna Malaiyandi:is once you take that snapshot in, say a normal case, right?
Prasanna Malaiyandi:You have your snapshot, now you need to sweep it somewhere else.
Prasanna Malaiyandi:It's normally your client who's moving it.
Prasanna Malaiyandi:You're now impacting your storage system.
Prasanna Malaiyandi:I know we talked about this in a previous podcast, right?
Prasanna Malaiyandi:About N D M P and having it done at a storage system makes a lot more sense.
Prasanna Malaiyandi:That's what we used to do.
Prasanna Malaiyandi:So we actually built a solution to move the data from.
Prasanna Malaiyandi:Primary storage.
Prasanna Malaiyandi:Write the EMC snapshot to a data domain system directly without
Prasanna Malaiyandi:requiring a client in the picture.
W. Curtis Preston:Hmm.
W. Curtis Preston:It's both pieces of that arch architecture were sold by emc.
W. Curtis Preston:That's fascinating.
W. Curtis Preston:Um, so, so we'll get the next one is what I think is probably
W. Curtis Preston:the most common backup method for databases, which is dump and sweep.
W. Curtis Preston:You wanna talk about that?
Prasanna Malaiyandi:Yeah, so this is, so most database applications support
Prasanna Malaiyandi:a functionality called dump, right?
Prasanna Malaiyandi:Which is take my database files and dump it to a location, right?
Prasanna Malaiyandi:So rman supports this, right?
Prasanna Malaiyandi:Uh, which we'll talk about in a little bit, right?
Prasanna Malaiyandi:Other tools do it, and similar to sort of a snapshot and sweep, right?
Prasanna Malaiyandi:Dump and sweep is you do a database dump somewhere, and then you sweep it off
Prasanna Malaiyandi:and copy the files off somewhere else to get that backup copy, if you will.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:And so this is another mechanism, and you don't need any fancy tools.
Prasanna Malaiyandi:You don't need, like this works everywhere, right?
Prasanna Malaiyandi:So you don't need to have a storage system that supports snapshots, right?
Prasanna Malaiyandi:You could be running it on a server, right?
Prasanna Malaiyandi:With local disk, do a local dump and then sweep it off
Prasanna Malaiyandi:somewhere else to get your backup.
W. Curtis Preston:Yeah, the upsides of this is that it's very
W. Curtis Preston:flexible and anybody can do it.
W. Curtis Preston:Um, you know, the, and it gives the DBAs that control that they're looking for.
W. Curtis Preston:Uh, the downsides are many.
W. Curtis Preston:Unfortunately, no one cares, right?
W. Curtis Preston:So you have to have twice as much storage.
W. Curtis Preston:Right.
W. Curtis Preston:Um, you know, you have to have a dump location that you can back it up to.
W. Curtis Preston:Now what some people have done is they've backed up directly to a
W. Curtis Preston:deduplication appliance, right?
W. Curtis Preston:Like a data domain.
W. Curtis Preston:They get an NFS mount and they back up to that.
W. Curtis Preston:I do not like that method either.
W. Curtis Preston:I don't like the backups being visible directly as on an NFS mount
W. Curtis Preston:point, but, you know, whatever.
W. Curtis Preston:Uh, ransomware cuz of ransomware.
W. Curtis Preston:Right?
W. Curtis Preston:Um, but the, um, Uh, the other problem is again, that this is going to be done by
W. Curtis Preston:some sort of custom script, some sort of scheduling system, which it may or may not
W. Curtis Preston:have any decent reporting to it, right?
W. Curtis Preston:One of the, one of the main concerns I have with Dump and Sweep in
W. Curtis Preston:general is that, um, the, the two sides don't talk to each other.
W. Curtis Preston:Right.
W. Curtis Preston:So the, the, the sweep is happening.
W. Curtis Preston:The backup person is running the sweep and the DBA is running the dump.
W. Curtis Preston:What happens if the dump fails?
W. Curtis Preston:The backup just keeps running.
Prasanna Malaiyandi:Thinks it's all fine.
Prasanna Malaiyandi:It's great.
Prasanna Malaiyandi:Yeah.
W. Curtis Preston:Yeah.
W. Curtis Preston:So what I've done, again, it's, it is just complexity, but, you know, I liked it.
W. Curtis Preston:Um, was I, I built into the dump script.
W. Curtis Preston:I would work with the, the DBA to build into the dump script, a file that they
W. Curtis Preston:would touch that would say that the backup worked or didn't work or whatever.
W. Curtis Preston:And then I would look at that file from the other side and I would know
W. Curtis Preston:that the backup ran recently or not.
W. Curtis Preston:And if it didn't run recently, then I would error and then I could go to
W. Curtis Preston:the DBA all that stuff is all custom
Prasanna Malaiyandi:Well, well, and what percentage of DBAs
Prasanna Malaiyandi:and backup people do you think actually do that mechanism, Curtis?
Prasanna Malaiyandi:You know?
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:Most people, and I, and I've actually seen this, right?
Prasanna Malaiyandi:So when I've talked to customers, right?
Prasanna Malaiyandi:Sometimes they're like, yeah, our DBA was doing dumps.
Prasanna Malaiyandi:We were doing sweeps, and then something happened.
Prasanna Malaiyandi:It wasn't coordinated.
Prasanna Malaiyandi:And we lost some data.
W. Curtis Preston:Yeah.
W. Curtis Preston:Yeah.
W. Curtis Preston:Uh, so that's why I don't like it, even though it's the most popular method.
W. Curtis Preston:So the, the next, um, is the second, I think the second most popular method,
W. Curtis Preston:which is the stream to backup product.
W. Curtis Preston:Right?
W. Curtis Preston:So this is, I'm gonna get the, the agent, uh, For the thing, right?
W. Curtis Preston:I'm gonna get an in, you know, in the case of Oracle, it's Rman, right?
W. Curtis Preston:Our Rman by the way, RMAN can do a dump, right?
W. Curtis Preston:Uh, RMAN can do a dump to disc.
W. Curtis Preston:Rman can also basically send data to a pipe that is then read by the
W. Curtis Preston:backup product, and it can then be sent directly to wherever that
W. Curtis Preston:backup product stores its backups.
W. Curtis Preston:Whether it's a DDU device, regular disc tape.
W. Curtis Preston:The cloud, right?
W. Curtis Preston:Whatever it uh, does, but hardly anybody.
W. Curtis Preston:Does this right?
Prasanna Malaiyandi:What?
Prasanna Malaiyandi:I hope you're being sarcastic.
W. Curtis Preston:saying No, no, no, no.
W. Curtis Preston:I'm just saying I'm just, it's the second most popular, but
W. Curtis Preston:it's, but it's a distant second.
W. Curtis Preston:It's, it goes back to that.
W. Curtis Preston:To that control, right?
W. Curtis Preston:The, the, the, the DBAs just want to control it.
W. Curtis Preston:Now, in the case of RMAN, you can properly configure RMAN so that the DBA can run
W. Curtis Preston:a backup whenever they want, and you can run the backup whenever you want.
W. Curtis Preston:And then you don't have to talk to each other.
W. Curtis Preston:But I don't, I don't mean that the, the DBAs the SAs shouldn't talk to
W. Curtis Preston:each other, but you know what I'm
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:It's, it's, it's a control.
Prasanna Malaiyandi:Everyone still has control and visibility, right?
Prasanna Malaiyandi:I think that's the key is people want the control.
Prasanna Malaiyandi:They don't want to give it up because it's their job on the line.
Prasanna Malaiyandi:Like if the database fails, DBA is like, do I trust the backup team or am I
Prasanna Malaiyandi:going to take control and do it myself?
Prasanna Malaiyandi:And this is why even with these tools, Right, that the backup team
Prasanna Malaiyandi:and the DBAs can work together.
Prasanna Malaiyandi:I would still see DBAs on expensive primary tier one primary storage,
Prasanna Malaiyandi:take five, six database copies.
Prasanna Malaiyandi:Yeah, and keep it locally on production storage because they
Prasanna Malaiyandi:did not trust a backup team.
W. Curtis Preston:Yeah.
W. Curtis Preston:Good times.
W. Curtis Preston:Yeah.
W. Curtis Preston:Yeah.
W. Curtis Preston:The, the biggest advantage to this is, well, the two, one is that you don't
W. Curtis Preston:have to have that extra storage, but a lot of people end up having it anyway.
W. Curtis Preston:Uh, but the biggest advantage here is no custom scripting, right.
W. Curtis Preston:Put the agent where it's supposed to be, uh, connect it.
W. Curtis Preston:It's usually like a DLL or something, and then it talks to the, the backup
W. Curtis Preston:system, and then you get all the reporting, everything end to end.
W. Curtis Preston:That's why I like it.
W. Curtis Preston:I, I think it's a better design, but I know why the
W. Curtis Preston:other guys do The other thing.
W. Curtis Preston:Um, and then just real quick, I'm just gonna cover 'em real quick
W. Curtis Preston:cuz we're, we're gonna be too long.
W. Curtis Preston:Uh, transaction log backup, making sure that you know, if you have a transaction
W. Curtis Preston:log database, making sure that that's included in the backup, because
W. Curtis Preston:especially if you're doing a hot backup.
W. Curtis Preston:You need those transaction logs during the time of the backup where you're
W. Curtis Preston:not gonna be able to, to get it.
W. Curtis Preston:And also you need those transaction logs to roll your backup forward
W. Curtis Preston:to the current point in time.
W. Curtis Preston:Right.
W. Curtis Preston:And then the master file, right?
W. Curtis Preston:The control file and Oracle, the, the master database and sql, uh, et cetera.
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:There are all these other things that you need as well.
Prasanna Malaiyandi:Yep.
W. Curtis Preston:Yes, yes, yes.
W. Curtis Preston:Um, so the next we have is backing up PaaS and serverless databases.
W. Curtis Preston:Um, this is.
W. Curtis Preston:Um, I, I think that things times have changed, even as, as I, you
W. Curtis Preston:know, since I wrote the book.
W. Curtis Preston:There's dump and sweep there too, right?
W. Curtis Preston:Uh, there's also integrated backup as a service, right?
W. Curtis Preston:Um, so like, w when you think about like RDS or DynamoDB, how,
W. Curtis Preston:how do people back those up today?
Prasanna Malaiyandi:I think they just predominantly use AWS's native tools.
Prasanna Malaiyandi:Right?
Prasanna Malaiyandi:If I look at that, it's a cloud provider, whoever I'm using,
Prasanna Malaiyandi:right, gives me the tools I need to do the functionality to backup,
W. Curtis Preston:yeah, yeah.
W. Curtis Preston:I, I don't even think it's, many of them don't even offer the dump feature.
W. Curtis Preston:Right.
W. Curtis Preston:I don't think you can dump, uh, DynamoDB
Prasanna Malaiyandi:think Dynamo DB now supports dumping to s3, I believe.
W. Curtis Preston:Oh, okay.
W. Curtis Preston:All right.
W. Curtis Preston:Maybe, um, yeah, so quite often that feature's not available.
W. Curtis Preston:Uh, sometimes it is, it sounds like maybe, uh, Amazon was sort of
W. Curtis Preston:probably forced to come out with that.
W. Curtis Preston:They, they had customer demand for it.
W. Curtis Preston:Um, I think, I agree with you.
W. Curtis Preston:I think the most common method is the, basically the, the integrated.
W. Curtis Preston:Backup method, right?
W. Curtis Preston:In the case of aws, you take snapshots, those snapshots
W. Curtis Preston:end up getting stored in s3.
W. Curtis Preston:Um, you can ha you can have those snapshots replicated to other
W. Curtis Preston:places, uh, which you should, right?
W. Curtis Preston:That's, that's the only concern that I have.
W. Curtis Preston:If you have RDS and you're just like, RDS by default has snapshots enabled, and they
W. Curtis Preston:just run, like, I think they run once a
Prasanna Malaiyandi:They run once a day,
Prasanna Malaiyandi:but.
W. Curtis Preston:all in the same, go ahead.
Prasanna Malaiyandi:but the thing you should be careful of and read
Prasanna Malaiyandi:carefully what they offer because each one does something different.
Prasanna Malaiyandi:And even RDS gives you different flavors depending on what database
Prasanna Malaiyandi:type you're using within RDS.
Prasanna Malaiyandi:Um, so most of 'em, when they're doing that automated snapshot, like
Prasanna Malaiyandi:you're talking about, Curtis, They typically only keep it for 30 days max.
Prasanna Malaiyandi:That's it.
Prasanna Malaiyandi:You can't extend it beyond 30 days.
Prasanna Malaiyandi:That's all you get.
Prasanna Malaiyandi:So if you have retention requirements, you now have to manually manage the snapshots
Prasanna Malaiyandi:on your own or the backups on your own.
Prasanna Malaiyandi:Um, the other
W. Curtis Preston:Yeah.
Prasanna Malaiyandi:oh, go ahead.
W. Curtis Preston:go ahead.
Prasanna Malaiyandi:The other point I was gonna make is that be careful
Prasanna Malaiyandi:also because some tools allow you to do a backup, but they don't
Prasanna Malaiyandi:actually allow you to do the restore.
W. Curtis Preston:Yeah.
W. Curtis Preston:OuRMAN on RDS perfect example of
Prasanna Malaiyandi:Yeah, so make sure
W. Curtis Preston:The last time I checked anyway.
Prasanna Malaiyandi:So carefully read what it offers.
Prasanna Malaiyandi:Don't assume.
W. Curtis Preston:I am gonna throw an idea out there, Prasanna,
W. Curtis Preston:and tell me what you think.
W. Curtis Preston:Why in the hell do you need backups Over 30 days of a database?
W. Curtis Preston:Of a database?
W. Curtis Preston:What good is a backup of your database from two months ago?
W. Curtis Preston:This not the file system,
Prasanna Malaiyandi:CA.
W. Curtis Preston:right?
Prasanna Malaiyandi:I can.
Prasanna Malaiyandi:I have a, so here's where I think it could be useful.
Prasanna Malaiyandi:Now, I've never administered backup systems.
Prasanna Malaiyandi:I've never actually had to deal with recoveries like you have.
W. Curtis Preston:Mm-hmm.
Prasanna Malaiyandi:But one potential I could see is, say there was a corruption,
Prasanna Malaiyandi:you have all the transaction logs.
Prasanna Malaiyandi:Going back, say 60 days, you could theoretically, Recover the
Prasanna Malaiyandi:database back to 60 days and replay all the transactions to recover.
W. Curtis Preston:I think that is the edge condition of all edge conditions.
Prasanna Malaiyandi:I said, but, but did you notice that I did
Prasanna Malaiyandi:not say anything about archive?
Prasanna Malaiyandi:Because that's typically why people should be
Prasanna Malaiyandi:keeping it
W. Curtis Preston:Well, they, they're like, well, they, they're like, well,
W. Curtis Preston:we keep it for, um, for, um, um, For retention or compliance or whatever.
W. Curtis Preston:And I'm like, yeah, but you're keeping a backup for compliance reasons.
W. Curtis Preston:If you actually need that in three years, you're gonna be in a world of hurt.
W. Curtis Preston:Cuz you're, we're gonna be on Oracle version 78 and you're gonna be on version
W. Curtis Preston:13 and you're gonna be in a world of hurt if you need stuff for compliance reasons
W. Curtis Preston:that should be archived, which would be in like full text, not in a database format.
W. Curtis Preston:Uh, you know, that's a, that's a whole other discussion.
W. Curtis Preston:So, um, the, the final section and, and it, and it's gonna be admittedly
W. Curtis Preston:much shorter than the previous, right?
W. Curtis Preston:And, and that is, and it's, it's equally as important, which
W. Curtis Preston:is recovering the databases.
W. Curtis Preston:But there's, um, there are, um, h Hang on, sorry, I'm just looking here.
W. Curtis Preston:Um,
W. Curtis Preston:um, I, I do in the book, go into.
W. Curtis Preston:Preferred, at least my preferred backup methods for, for a
W. Curtis Preston:handful of different databases.
W. Curtis Preston:If, if, if you want those, you know, those are available in the book.
W. Curtis Preston:Uh, but as far as recovering traditional databases, There's
W. Curtis Preston:essentially four steps, right?
W. Curtis Preston:I'm just gonna go through 'em real quick.
W. Curtis Preston:Right?
W. Curtis Preston:Identify what's wrong, restore the data files, apply media recovery,
W. Curtis Preston:and then start the database, right?
W. Curtis Preston:So the, this is for regular, you know, databases hosted on a, on a server.
W. Curtis Preston:Um, you, you have to figure out what's wrong because you might
W. Curtis Preston:not have to do a full recovery.
W. Curtis Preston:Right?
Prasanna Malaiyandi:And hopefully you can avoid that.
Prasanna Malaiyandi:Yeah.
W. Curtis Preston:Like in the case of, again, I'm gonna use Oracles a lot cuz
W. Curtis Preston:it's where most of my experience is.
W. Curtis Preston:You might just find out that the control file is missing, right?
W. Curtis Preston:You might just find out that one data file is, was deleted or corrupted and
W. Curtis Preston:you could restore just that data file.
W. Curtis Preston:Um, anything you can do like that, um, you know, you know to figure out what's wrong.
W. Curtis Preston:Before you do any recovery, it, it's just like, you know, we talked about ransomware
W. Curtis Preston:recovery, just like ransomware recovery, the more you can do upfront to figure
W. Curtis Preston:out the, the more time it'll save you.
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:Every, you don't need a hammer for everything.
Prasanna Malaiyandi:Full recovery.
Prasanna Malaiyandi:You don't need to full restore everything.
Prasanna Malaiyandi:Anytime something happens with your database.
Prasanna Malaiyandi:A lot of these database backups, right, and the database applications
Prasanna Malaiyandi:themselves are smart enough to recover with just smaller granularities.
Prasanna Malaiyandi:So once you figured out what caused the issue, right, most of these
Prasanna Malaiyandi:database, then you can actually restore whatever needs to be recovered, right?
Prasanna Malaiyandi:So like you mentioned, Curtis, your second step was recover the data
Prasanna Malaiyandi:file or whatever that object is.
Prasanna Malaiyandi:Most of the database recovery tools sort of deal with a lot of that for you.
Prasanna Malaiyandi:So say you've.
Prasanna Malaiyandi:Found Oracle has a corrupt data file, right?
Prasanna Malaiyandi:You can go to Oracle RMAN and say, recover this particular data file, and
Prasanna Malaiyandi:it'll just pull back that one data file.
W. Curtis Preston:Right.
W. Curtis Preston:And if you used, if you used dump and sweep, it's a little bit different.
W. Curtis Preston:But if you used, uh, snap and Sweep or you know, one of the others where
W. Curtis Preston:basically you have the, the data files right there, you can just grab
W. Curtis Preston:'em and restore 'em, or you can, you know, use your database product.
W. Curtis Preston:The, the really important step, the database won't open at that point because
W. Curtis Preston:now you've got, You've either got a, all the files are from one point in time from
W. Curtis Preston:last night or maybe even two days ago, or you've got some files from right now
W. Curtis Preston:and some data files which are behind, and then you have to apply media recovery.
W. Curtis Preston:So this is essentially using those transaction logs to redo
W. Curtis Preston:logs to, to redo everything that happened since that backup.
W. Curtis Preston:And you, you basically bring all the data files up to the same point in time.
W. Curtis Preston:And then you can open the database.
Prasanna Malaiyandi:And I,
W. Curtis Preston:go ahead.
Prasanna Malaiyandi:and I remember just going back talking about it, right?
Prasanna Malaiyandi:Only once I started talking to like the application experts who are really good at
Prasanna Malaiyandi:backups for databases, and I realized it's a two step process, like you said, right?
Prasanna Malaiyandi:One is restore and the other is recovery.
Prasanna Malaiyandi:You're restoring your data files or whatever the media is, you're
Prasanna Malaiyandi:recovering the database into a well-known point in time so it can, in
Prasanna Malaiyandi:the footstep, be brought back online.
W. Curtis Preston:And it is possible.
W. Curtis Preston:For example, if the reason that you're restoring the database is that somebody
W. Curtis Preston:dropped a table, uh, or did a DBA or, or a threat actor did something
W. Curtis Preston:to your database, deleted a bunch of records, deleted some tables, whatever.
W. Curtis Preston:When you do media recovery, you can typically say, do media recovery.
W. Curtis Preston:Up to this point in time, you know that the date that the table
W. Curtis Preston:was dropped at, you know, 1745.
W. Curtis Preston:And so you, you just restore, you know, recover up to 1744.
W. Curtis Preston:Um, you might even do 17 44, 59, what, you know, whatever it is that, you
W. Curtis Preston:know, when that table was dropped.
W. Curtis Preston:You can do media recovery up to that point and it'll bring it up to a consistent
W. Curtis Preston:point in time at that point in time.
W. Curtis Preston:Right.
W. Curtis Preston:Um, Yeah, it, it, it's not an easy thing and, and uh, you know, hopefully
W. Curtis Preston:it's something that you practice on a regular basis, just like everything else.
W. Curtis Preston:Hopefully it's something you test and you practice, um, et cetera.
W. Curtis Preston:So I've got so recovering modern databases, um, I.
W. Curtis Preston:You know, I, I've got a handful of different ways, right?
W. Curtis Preston:So I've got the point in time recovery without media recovery.
W. Curtis Preston:So, um, some of them just don't provide it, right?
W. Curtis Preston:So, like when we talked about backing up, um, What are we talking
W. Curtis Preston:about backing up DynamoDB, right?
W. Curtis Preston:So it doesn't really have the concept of media recovery.
W. Curtis Preston:If you want better, if you want more granularity in the restore, you just take
W. Curtis Preston:More uh, snapshots more often, right?
W. Curtis Preston:That gives you a better rpo, right?
W. Curtis Preston:Recovery point objective.
W. Curtis Preston:Uh, so that, that could be one option.
W. Curtis Preston:The other option would be table based recovery.
W. Curtis Preston:Um, why might I want to do that?
Prasanna Malaiyandi:because you only care about one particular thing in the
Prasanna Malaiyandi:tape or in the entire database instance.
Prasanna Malaiyandi:Not everything.
W. Curtis Preston:Well, I hint, I mentioned something
W. Curtis Preston:that could happen a few minutes
Prasanna Malaiyandi:Oh, someone dropped a table
Prasanna Malaiyandi:or uh, or you have a malicious person who goes and deletes things.
W. Curtis Preston:Yes.
W. Curtis Preston:Yes, exactly right.
W. Curtis Preston:So it may be that the only thing wrong with your, with your, um,
W. Curtis Preston:system is, uh, is ta is is the table.
W. Curtis Preston:But the other reason that you might want to do this is that
W. Curtis Preston:this is the only way to do this.
W. Curtis Preston:Some, some of the backup agents or some of, I'm sorry, some of the
W. Curtis Preston:database products, the only way to back up is at the table level, right?
W. Curtis Preston:Um, there's also node level recovery.
W. Curtis Preston:Right.
Prasanna Malaiyandi:you're talking
W. Curtis Preston:a multi node database, go ahead.
Prasanna Malaiyandi:I was thinking like when you think about like Cassandra
Prasanna Malaiyandi:or MongoDB or other things like this where you have multiple nodes, which.
Prasanna Malaiyandi:Then make up the database.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:You might have a node fail.
Prasanna Malaiyandi:Right.
Prasanna Malaiyandi:And so what's the process for bringing up and recovering a particular node?
W. Curtis Preston:Right, and and most of them, it looks like you don't really
W. Curtis Preston:have to recover the note as much as you just have to create a new replica.
W. Curtis Preston:Say, Hey, uh, this one's dead.
W. Curtis Preston:We're gonna do this one now.
W. Curtis Preston:And they just sort of figure that out.
W. Curtis Preston:That is the beauty of those type, you know, of a scale out database, right?
W. Curtis Preston:Um, the, the, the more common I think is gonna be the, you know, the one
W. Curtis Preston:that we, it, it, I'm, I'm saying it twice, but the, I have cloud level
W. Curtis Preston:snapshot as a backup and restore method.
W. Curtis Preston:That's a super easy restore method though, right?
Prasanna Malaiyandi:Yeah, can you just bring it back?
W. Curtis Preston:Yeah.
Prasanna Malaiyandi:assuming that it's supporting, assuming it's supporting,
W. Curtis Preston:Yeah.
W. Curtis Preston:Assuming, supported.
W. Curtis Preston:Yeah.
W. Curtis Preston:Um, don't assume any of this stuff right.
W. Curtis Preston:To make
Prasanna Malaiyandi:go test it.
W. Curtis Preston:you're testing this upfront.
W. Curtis Preston:Yeah, exactly right.
W. Curtis Preston:Um, the.
W. Curtis Preston:So, uh, I'm just gonna read this because it's, it's outside, really
W. Curtis Preston:outside of my world here, but, so there's some restored, uh, scenarios.
W. Curtis Preston:Depending on how the data is restored will need to be brought consistent
W. Curtis Preston:with the rest of the database.
W. Curtis Preston:So you may have to restore.
W. Curtis Preston:This is, again, this is with an eventually consistent database.
W. Curtis Preston:That's why we wanted to mention that term back in the last, uh, recording.
W. Curtis Preston:Um, if you've got an eventually consistent database, You may restore
W. Curtis Preston:only part of that database and that it then needs to be brought consistent
W. Curtis Preston:with the rest of the database.
W. Curtis Preston:It's a bit, honestly, it's a bit, uh, outside of my realm of experience.
W. Curtis Preston:So I, um, so I just have, I just have to say that is a type of restore and
W. Curtis Preston:when you talk about like Cassandra and MongoDB and these, these giant, uh, you
W. Curtis Preston:know, massive databases, That I, I talk to, um, like the folks over at datos.io,
W. Curtis Preston:that, that they, you know, they back up these type of things and they're
W. Curtis Preston:the ones that talk, that, talk to me about this, where that some of the stuff
W. Curtis Preston:that's documented in the, in the manual that I, if you follow it, it could end
W. Curtis Preston:up having to do an eventual, like an eventual recovery and that it can take
W. Curtis Preston:a really, really long time to do that.
W. Curtis Preston:So that, that, that's sort of what I'm alluding to there.
W. Curtis Preston:Honestly, I barely understand what, what that is myself if you don't understand it.
W. Curtis Preston:But here's what I'm saying is if you've got a, if you've got one of these massive
W. Curtis Preston:scale out databases, the, the big thing, the big takeaway for me from those is
W. Curtis Preston:please do not confuse availability.
Prasanna Malaiyandi:Yep.
W. Curtis Preston:durability, right?
W. Curtis Preston:Um, that, uh, someone can still drop a table, you can still lose a number
W. Curtis Preston:of a, a number of nodes, right?
W. Curtis Preston:So the question is, how do we recover?
W. Curtis Preston:If somebody lost, if somebody dropped a table, how do we recover?
W. Curtis Preston:If we lost whatever the number of nodes is, that would take out the system.
W. Curtis Preston:What's that solution?
W. Curtis Preston:Because if you don't have a solution for that, Uh, then you're gonna be in
W. Curtis Preston:a world of herd if that ever happens.
W. Curtis Preston:Right?
W. Curtis Preston:Some final thoughts on backing up databases.
W. Curtis Preston:Just a few things that come to my mind.
W. Curtis Preston:You, you've mentioned one of 'em, I, if you are scripting things, make sure you
W. Curtis Preston:built some intelligence into that script so that it will identify new databases,
W. Curtis Preston:new table spaces, new data files, whatever it is that you're don't, please don't
W. Curtis Preston:have a configuration file that you have to update when you make a new database
Prasanna Malaiyandi:Well, because things are changing and no one's
Prasanna Malaiyandi:gonna reach out to you either.
W. Curtis Preston:Yeah, exactly what I used.
W. Curtis Preston:Uh, shoot, it's been a while.
W. Curtis Preston:Was it the, the, is it like.com?
W. Curtis Preston:Like it was the, it was the configuration file for Oracle itself.
W. Curtis Preston:Right?
W. Curtis Preston:So if you had a new database in Oracle, you would had, you
W. Curtis Preston:would add a new instance, right.
W. Curtis Preston:And that instance would have an instance Id, that instance Id would
W. Curtis Preston:go into the Oracle configuration file.
W. Curtis Preston:It's basically the Oracle version of the, of the file system table, right?
W. Curtis Preston:And so I would start there.
W. Curtis Preston:I would read the Oracle configuration file, and I would say, what, you
W. Curtis Preston:know, what instances are on this?
W. Curtis Preston:And then I would put all those instances in backup mode back in the day.
W. Curtis Preston:I had to put the table spaces in backup mode.
W. Curtis Preston:So I would, log into that instance, query the instance of what the table
W. Curtis Preston:spaces were, you know, then loop through and, and, and do all of
W. Curtis Preston:tho put all those in backup mode.
W. Curtis Preston:And then of course, very importantly, take them out of backup mode when you're done.
W. Curtis Preston:Um, and, uh, and then add some reporting, add lots of error
W. Curtis Preston:checking for, for things.
W. Curtis Preston:You know, like, you know, the, the or.com file isn't where
W. Curtis Preston:I thought it was gonna be.
W. Curtis Preston:Right.
W. Curtis Preston:Um, or the, the sequel, I remember the, they would change.
W. Curtis Preston:Yeah.
W. Curtis Preston:Stuff would change.
W. Curtis Preston:So just have error conditions built into when the, when the script doesn't do
W. Curtis Preston:what you think you're gonna do, have some kind of way of letting other people know.
W. Curtis Preston:Back in the day, the only way I had was, was email.
W. Curtis Preston:Um, And we would just, you know, uh, send out a note, say, Hey dude, you
W. Curtis Preston:know, something happened over here.
W. Curtis Preston:Come take a look at it.
W. Curtis Preston:What, what other sort of
W. Curtis Preston:general things to can you think of?
Prasanna Malaiyandi:I think the other thing to take away is, Whatever
Prasanna Malaiyandi:solution you're using for backup, right?
Prasanna Malaiyandi:Even if you do have these automated abilities in the script, make
Prasanna Malaiyandi:sure the backup and the database people talk to each other.
Prasanna Malaiyandi:You guys don't have to be enemies.
Prasanna Malaiyandi:You don't have to be best buds either, right?
Prasanna Malaiyandi:But at least talk to each other and make sure you're coordinated.
Prasanna Malaiyandi:Because when you start to talk about recovery and the process,
Prasanna Malaiyandi:right, it all starts from what did we decide upfront for backup?
W. Curtis Preston:Um, man, we covered a lot in this episode, dude.
W. Curtis Preston:What do you think?
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:No, I think
Prasanna Malaiyandi:this is a, I think
W. Curtis Preston:Yeah.
Prasanna Malaiyandi:I always learned something new thinking, and I think
Prasanna Malaiyandi:I talked about this on the last database chapter or episode as well.
Prasanna Malaiyandi:It's databases are complex.
Prasanna Malaiyandi:If some of these things go over your head, don't worry.
Prasanna Malaiyandi:Right?
Prasanna Malaiyandi:There's a lot of
W. Curtis Preston:Hey, it goes over my head sometimes.
W. Curtis Preston:Right, right.
W. Curtis Preston:Because I'm trying to talk about, you know, we talked about if you go
W. Curtis Preston:to db-engines.com, we talked about that there are, um, it was like
W. Curtis Preston:30 different database products.
W. Curtis Preston:There's
Prasanna Malaiyandi:I think
Prasanna Malaiyandi:it's more than
W. Curtis Preston:different types.
Prasanna Malaiyandi:Yeah.
Prasanna Malaiyandi:30
W. Curtis Preston:well, no, it was like 30 types and then like a couple
W. Curtis Preston:of hundred different databases out there, and they're all different.
W. Curtis Preston:And they all.
W. Curtis Preston:You know, they all back up different and restore different, so just
W. Curtis Preston:make sure you know how yours works.
W. Curtis Preston:Right.
W. Curtis Preston:Um, and please don't let the vendor go, oh, this doesn't need to be backed up.
W. Curtis Preston:Please don't.
W. Curtis Preston:It hurts my ears when I hear that.
W. Curtis Preston:Uh, so hopefully this was helpful.
W. Curtis Preston:Um, you know, if you've got other database questions, throw 'em out.
W. Curtis Preston:Throw it out to us.
W. Curtis Preston:I hope you enjoyed the episode.
W. Curtis Preston:Um, it's always fun, um, talking about my favorite subject
Prasanna Malaiyandi:Yeah, and no, I, I'm excited to hear about your
Prasanna Malaiyandi:experiment, Curtis, more than anything, honestly, I wanna see what happens.
W. Curtis Preston:Yeah, it'll be interesting.
W. Curtis Preston:It'll be interesting and, uh, thanks everybody for listening and remember to
Listen On
