Backups: Reading from Databases During Restores

Store and Restore walk into a Store. Store walks out. Who’s left?

Twinkies. They’ll outlive us all.

In this video, you’ll learn how to use the Restore command with Standby. It’s fun, I promise.

Full Transcript:

Hey there, is this thing on? I think it’s on. Better be on, otherwise, I’m just talking to myself for no good reason.

Welcome, my name is Erik, I work for Brent Ozar Unlimited, and today we’re going to talk about how to restore transaction logs using the Standby syntax and how that can help you in emergencies, where you need to recover some data, figure out which log file has the data that you need to restore.

Now, it’s a DBA must to understand a lot of use of syntax and be able to – well, I don’t want to say do it off the top of your head because remembering syntax is hard, hard work. Scripting things out a few times does help you remember them. We’re going to look at a pretty simple example of how it works and how it can help you in an emergency. There are more advanced uses using syntax with stop at, stop at mark and stop before mark, which can take you to a date time or an LSN or a transaction name, and you can use that to stop at a particular point within a transaction log. That is covered in our senior DBA class. Since you’re watching this video, you maybe you’re not there yet, maybe you just need to get some junior mid-level DBA advice on how to restore a transaction log.

This is a very popular thing to do with log shipping because you can offload queries to a log shipped server, and you can restore logs with standby and you can allow people to report and run queries on that server because the database will be readable. Just keep in mind that when you restore logs like that, even when you use standby when you start restoring the logs, people will get kicked out of that database. They won’t be able to run their queries, their queries will get canceled, log restores will start piling up, and when you do that, when you use standby and you let people off over reports to log ship servers, you have to pay for it. You have to license that server as a completely licensed SQL Server. There is no software assurance that allows you to have that list off where assurance you get the one standby server has to standby server. You do not get a readable server, and that’s true for availability groups, that’s true for if you use mirroring and you use Snapshot backups to allow people to run point in time queries of a Snapshot back of a mirrored database, or if you use replication. As long as people are reading that data, you must pay for it.

So let’s hop on to our example. Demos are great fun. So we’re going to do some stuff here, we’re going to use master, it’s a good thing to do, use a master database to make sure we’re not doing anything goofy. I’m going to create a database, pretty simple called log restore test, and this should happen pretty quickly because pretty good computer, and I’m going to set recovery model to full because if you don’t, that’s not going to help you very much. My model database is really happily set up for my demo server to just think everything in simple recovery model because it knows that I’m not going to take log backups because it’s all just demo databases.

So now we’re going to head into our log restore database, I’m going to create a table, just call it T1 with one column in it, and there’s not going to be anything special. Now, we have to take our first full backup and I am making sure to overwrite any existing backups here, with it in format and I am going to compress my backup because I care for the sanctity of my disks. It would take up more space than I need to.

Now, here comes the fun part, you’re going to make a change, we’re going to insert one row into our table and then take a log backup. And now, we’re going to do the same thing again, we’re going to insert another row. This time I’m going to insert the number two, and then we’re going to take another log backup, and in case you couldn’t guess by the script that’s coming, we’re going to insert a third row, that is the number three, and I’m going to take another log backup. Now, keep in mind I am not writing all my log backups or all my backups to the same file. I like all my backups to be in different files because if anything happens to the disk, if anything happens to the file, the file goes corrupt, I don’t want to lose all of my backups in one shot. I would like to maybe, hopefully, keep some of my backups because bad things happen.

Now, we have our backup, we have our database, we have four backups, a full three logs, and we have some transaction, right? We have some stuff that we did in there. Now, I’m going to go back out of this database, I’m going to use master and I’m going to restore the full backup. Now, pay careful attention here, because on the first go around, unless you are restoring your database to a database with a new name, which I am not doing, I am restoring over the log restore test database, usually in production you will want to restore your database to a different database name, figure out where your data is and then figure out a way to migrate it into – for whatever happened, where there was a bad insert update or a delete that you need to recover from, you need to – you’ll typically restore to a new database name and then migrate data over to correct the problem. I’m not doing that here, I am restoring over my database. So my first restore command is going to replace a log restore test database, and it’s also going to use the standby syntax, and I have to give a file for SQL to use here. The TUF extension just comes from I don’t know, just being really tough to do under the gun.

So we’re going to run that and I’m going to do my restore, hopefully, that works, because there’s nothing worse than having to re-record these things. Now, I get questions from clients a lot like, but if I lose a log backup, I can just restore the most recent log backup, right? Wrong, no, you can’t do that. SQL is smarter than you, it knows damn well you can’t – damn well what happens on your servers. So just to prove it, here’s what happens if you try to restore the third log file. SQL immediately throws an error and says no way Jose, you do not get any joy from this. Now, it doesn’t get any better if I try to restore the second one first, it still knows, it still tells me that LSN is at a different place, and the log file and it isn’t the full backup and they’re just not compatible, they’re not “simpatico,” as you might say.

So we have to start with the first log backup, and this – so now that we have our database and we’ve restored, we’ve used the standby file, we can look and see what data is in there, and we see that we have ID1, which is the log backup that we took after we inserted one. If I go back to two, and I run this restore, then I run my query again, we do not see IDs one and two. Magical, right? And you can probably guess what happens if I go and I restore this log backup. All of a sudden, we are on ID number three. We have all of our IDs back. Now, usually what happens here is you say, three, that’s the data – that’s what the data happened after I needed to get to. Alright, let’s start over again, let’s just go back to two because you get the same problem.

So this is why you have to be a little careful, why you should script things out and make sure that you have all of your commands ready to go, because if we truly had to stop our restore process at ID2, say that was the point where something bad happened, we just wanted to have our data to that point, we can’t just go back in time an extra log file. We would have to start the restore process again. So the only way to do that would be to restore, to start over with the full, and then restore one and then restore two because you can’t just go back one log file. You can’t roll anything backwards, and now we would have IDs one and two, and lo and behold, you would be, according to anyone, fully recovered.

Like I said, there is more complex syntax if you use stop at, stop at mark, stop before mark, we could stop in the middle of a transaction log file if we had a very busy server and you had to somehow rescue more data. Anyway, what we do next is just bring the database back online, we restore database log restore test with recovery. Now, it’s funny but it’s sad. Every time I have to bring a database online, I always start in alter database, like because alter database is something in my head that never clicks, but it’s restore database, and then your database name with recovery and that should just about do it, and once this is back up online, I’m going to do something horrible here and I’m just going to drop the database because I don’t care anymore. I don’t need you, you’re not my friend, log restore test, your demo days are through. Anyway, that is a basic outline of the simplest way to restore log files using the standby command. I hope you learned something, I hope you enjoyed the video. Thank you for watching, bye.