Using log reader in Toad for SQL Server

Hello, folks. My name is Robert Pound, and I'm a product technologist from Quest. This video will cover the use of log reader in Toad for SQL Server. When data gets lost, tables get inadvertently dropped, or your environment just gets in a weird state, it can be upsetting. Wouldn't it be great if there was a Control-Z type of option in SQL Server? Well, with quest's log reader, it can be just that simple. Looking at Management Studio, we can see that I have dropped the where clause off of my statement during execution, and it has affected many more rows than I wanted it to. Since I have the plug-in installed in Management Studio, I can get to the log reader by right-clicking and choosing manage log reader. Or if I was working in Toad, I could come to tools, administer, and log reader. This will open up a new log reader tab and open up a wizard to help me populate the log reader tab. Once I choose next, it will ask me which type of log I want to use. In this case, I'm using online transaction log, but you can also use a native or lightspeed backup file as well as an offline transactional log. I'm then prompted to enter in my connection information and my authentication. These are both the default, so I don't need to change these. And I was working with my Toad demo database, so let me go ahead and select the database, and choose next. At this point, I can add any additional files if I want to expand my search, but in this case, I know that it's on the transactional log that's online. And the log reader has gone out and pulled the information from the log, so I will click finish. So before I get into fixing my problem, let me first look through the toolbar. If I choose this log reader wizard, it will go through the same log reader wizard again. If I wanted to go back through and refine the data that I'm looking at, I could do that. There's also a refresh start and stop, so if I wanted to stop checking the log for updates, I could do that now. I could started back up so that any changes will be updated in this log reader tab. There is an undo redo wizard, which is what we're going to use in a second. There's also a wizard to help recover tables, as well as a wizard about table history. The next couple have to do with the data itself, so you can group the data and filter the data, and I will use both of these, as well as export in several different formats. So on to the problem at hand. I could come to the transactions tab, and look to see the transaction ID for the transaction that I'm interested in. This is obviously the transaction that I ran that, basically, wiped out the entire table, with the ID 371. But if I wanted to search through the DML DDL, I have a couple of options. So first of all, you can use grouping, and when I select that here, it will bring up this field to drop columns into. If I wanted to do a search by date, I could drag and drop date into this field. So now everything is rolled up by date. But I might want to further group it by the type of command. So then looking at today's date, because I know that's when it occurred, there's only a few different updates. But I also want to look at transaction, because I want to find specifically which transaction was the one that I'm interested in, so I'll drop transaction as well. So now when I look at update, there are several transactions. And of course, when I come to the bottom one, that's actually the one that was kicked off. If I look at some of these others, they're single updates to the table. So now I'm fairly confident that this is the one that I want to redo. So now if I reconstruct the record, I can look to see, yes OK, new value guest, new value guest. Yep, this is definitely the transaction that I'm interested in. So from here, what I can do is right-click and choose filter transaction. So this is going to only give me this transaction ID. If I was being more sophisticated and looking for different object types etc., I could choose the filter tab. What this allows me to do is refine my search with several different parameters, but for this case, it was not needed. So from here, I can right-click again, and choose check all. And I can right-click to start the undo redo wizard, but I can also come to the top, which is what I'm going to do. So now I'm going to walk through another wizard, and I have the option of redoing or undoing. I'm going to choose undo. Next. And now it's giving me a preview of the undo script. Click next, and I could open it in Toad and modify it if I so choose, or I can run immediately, which is what I'm going to do now. And everything was successful. So I'm going to go ahead and click finish. So now if I go back to the editor, I can run this again. and we can see currently, everybody is named guest, so if I run this again, it is restored and everything's back to normal, which is awesome. And real quick, if I go back to the log reader and look at transactions, we can see there was a log reader for SQL Server update in the log itself, undoing all my changes made previously. I hope this video has helped you understand a little bit more about log reader and how it can help you.