Hey, everyone, welcome back. In this video, part 12 of topic six in our database class, I'm going to provide an overview of database backup and recovery. So, despite all of our best efforts to secure our database and the data that it contains, it is still possible for bad things to happen. So, we need to assume going in that something bad could happen to the database, and with that assumption in place, our natural response is to create some kind of backup and recovery strategy. So, if something goes wrong, we hope it doesn't, but if something goes wrong, we need to be able to recover from the database failure. Because data, remember, the data are so valuable, right? These are often the organization's most valuable assets. So, even if something goes wrong, we need to be able to recover. Things that can cause problems, right? Hardware failures, right? Maybe power supply, fan, fails, and the system overheats. I think, or processor fails or disk fails, these types of things like physical failures can happen. There might be problems with the software that relies on the database. Inadverantly deletes a bunch of valuable data or makes necessary or incorrect changes. Humans, unfortunately, are a major cause of problems with databases and malicious parties. And just as a reminder, these are not necessarily all external, right? More damage is done and more data are stolen from internal malicious users than external malicious parties. So, this is again why we rely on things like the principle of least privilege. So, things can happen. And it's not possible to avoid these things entirely. If we want our database to be useful at all, if you want to have the benefits of the database, then you have to allow people to use it and it has to be running. It has to be susceptible to these problems. So, we can't avoid them entirely. And for that reason, we need to implement good backup and recovery procedures. Okay, so let's talk about how we can recover from a, say, a crash database. Right, something went wrong and we need to recover it. Now, the first point that you need to understand is that we need to make regular backups of the data. Okay, so all of these strategies for recovering a database after a crash or after something goes really wrong are based on the assumption that we have a backup of the database. And now it's not going to be up to date because maybe we do a backup like once a week or once a month or once a day. So, that means we're going to have a recent backup, but changes are likely to have been made to the database since the time of that backup. Okay, but nevertheless, we need to have that backup in place in order to be able to recover the data. Now, with that idea in mind, let's take a look at our first recovery strategy and that is one of reprocessing. And this one's very simple. It's not typically very useful or it's useful only in very narrow circumstances. But the idea here is we, if a database crashes, we restore it based on the last backup and then we ask all of the human beings to repeat everything that they did, all of their interactions with the database since the time of the last backup. So, you could imagine that if you have more than just one or two users or if it's been a long time since the last backup that this is not going to work particularly well. And what I mean by that is your chances of getting the database back to a state where it was identical to the state of the database when it crashed is very low. Right, because people make mistakes, they're going to do things in different orders, they're going to forget about something that they did, etc. So, as it says here, this is a risky and it's really only useful in very narrow situations. If you have maybe you run a your own little small business and it's just you and you have a database and maybe you take two orders per week, you're database crashes, well, it's probably pretty easy for you to recover it and through reprocess and get the database back to the point where it was before it crashed. But if we're talking about a big company with thousands of employees using a database then the strategy is not going to work very well. So, in those cases where reprocessing is not viable, which is most of the time, we are going to need to rely on recovery via rollback or roll forward. And all modern enterprise level databases have these capabilities in even smaller level ones that are not meant for like massive companies typically will have these types of mechanisms in there as well. So, in order for this to work, in order for rollback or roll forward to work, in addition to having a backup of the database, we need another thing and that thing is a log file. Now, the log file records in temporal order that is in the order in which events originally occurred, all of the transactions are all the changes that are made to the database. So, these are stored in the log file from the time of the most recent backup. Okay, so if I made a backup to say yesterday, I can start the log file, a fresh log file, and then any changes that are made to the database from the time of that backup until now are recorded in the log file. So, any inserts, updates, deletes, any of these kinds of things will be in the log file and they will be stored in there and this is important in the order in which they were executed by the database server. So, with a recent backup and a log file, we can then engage in rollback and roll forward. And this allows us to do some great things with respect to protecting our data or at least allowing us to recover from unfortunate events. Okay, so I say that user accidentally runs a transaction that deletes a bunch of data that we didn't want to have deleted. Well, if we have a log file in place, we can undo those inadvertent changes simply by working our way backwards through the log file. Okay, so we'll store a copy of the old value and the new value and this allows us to do an undo, just like if you were typing a document and say a word processor, you can do an undo. And if we have a log file in place, we can undo these kinds of undesirable changes. Okay, but we also with the log file can do desirable changes. And this allows us to recover from a loss. So, say for example, that we have a recent backup of our database from a week ago and we have a log file that records all of the changes that took place to that database from the time of the most recent backup until now. Well, then, hopefully it makes sense that we can recover the database. We can restore it back to the exact state that it was in when the crash occurred simply by playing all of those changes again in order. It's like we just cycle through them one after the other after the other and the same order that they were originally done and the result will be that the database is back in an identical state as it was when the crash occurred. So, here's an example of a transaction log file and we can see, just use this as an illustrative example here and we have different types of transactions happening. So, these are like transaction IDs. And remember, this is a multi user database environment. So, we can have multiple transactions running at once. So, this transaction is our IDs OT1, OT1. So, here are some other tasks that are a part of this transaction. So, here we have these five tasks that are part of the OT1 transaction. We can also say here's OT2. So, it has two tasks that are a part of that transaction or here's a third transaction. And you can see that remember, these are all happening simultaneously. This is time if you want to think of it. So, this happens first and then the database does this and then the database does that and this and so on. And you can see that the transactions are all mixed together. But what we see in here then is this idea of before images and after images that we can use to do a role forward and roll back that is to undo or redo desirable things. So, let's take a look here at this example. And this one you can see it's what we're doing is we're changing some what are we changing? Well, we're changing customer number 100 and what is the change? We have the old value and the new value. So, I don't know, maybe this is like the customer's name. So, are there email address? So, we'll have their old email address in the transaction log and their new email address. And then you can see how if we made a mistake in making that change, we now have a basis for undoing it because we have stored that old value in the transaction log. So, with the log there, we can undo that change from the old value to the new value by replacing the new value with the old value. So, we can undo that change and in that case, it would be like this change never happened. And so, we can take the new value and replace it with the old value and that is effectively an undo operation. But you can see we can also do these redo operations. So, imagine that here at, say, time zero is when we took the last full backup of our database. And then we play the transaction log forward in order. So, we would do step one, step two, step three, step four, step five, six, seven, eight, nine. And by the time we get all the way down to the bottom, the database will be in exactly the same state it was when the crash occurred. So, we will have fully recovered it from the other crash by using a combination of the most recent backup and this transaction log file. So, here it is, decool stuff. It's a very simple idea, but it works extraordinarily well. So, just in terms of some jargon, a rollback operation then is one in which we are undoing undesirable changes by working our way backwards through the log file and we're replacing the new values. That is the after images with the before images. So, we replace the new values with the previous values. And as we do that by working backwards through the file, we are undoing those changes. So, if this is done accidentally or maliciously, we can use that transaction log file to undo the undesirable changes to the database by engaging in this rollback operation. So, here's a graphical example, just to solidify these ideas conceptually in your mind. What we see here is the database with the undesirable changes in it and we have the before images that were stored in our transaction log file. So, we apply those before images to the database that contains the undesirable changes and that's effectively an undo operation. And the output then is the database with those undesirable changes being removed or undone. Cool. Now, on the opposite side of that is being able to redo desirable changes. So, again, the idea here is if a database crashes or we or first for whatever reason, we can recover it back to the exact state it was in at the time of the crash by first loading the most recent backup of the database and then playing the transaction log file forward from start to finish. So, all of the activities that are recorded in the transaction log file are recorded in temporal order. It is the order in which they were carried out originally by the database. So, as long as we go back through and replay each of those steps in the same order, the result will be that our database will be restored to the exact state that it was in when the crash occurred. And again, conceptually, we can understand this using a diagram here. So, what we have is our database that we have loaded from the most recent backup and it does not contain the desirable changes. That is all of the transactions that we ran since the database was last backed up. So, we then use the after images out of the transaction log file, basically play through those steps in the transaction log. And it effectively implements a redo operation, the output of which is the database with all of those desirable changes in it. So, it will be right back to the point where it was when it originally crashed. So, this allows us to do a redo. Pretty clever idea. The first time I encountered this back when I was a student, I was like, that's pretty smart. Now, they can just use that one transaction log file to undo undesirable changes or to redo desirable changes. And it provides a good mechanism for ensuring that we can always recover from some sort of malicious action or accident, as long as we also have a regular backup.