Some time ago, I started helping the guys from ApexSQL at their boot on events like SQLSaturday Oslo and SQLSaturday Paris. They contacted me because they wanted some assistance with their technical demonstrations on a tool they have developed which is called ApexSQL Log. I thought that would be fun, so I accepted and indeed – it was totally worth it, but not only because of the experience on the boot, but because of the tool itself. Let me tell you a bit more about ApexSQL Log because if there is a tool that you have to know about that was made by those guys, it’s definitely this one!
Before we jump up to the “demo part” of this blog post, let me share with you what you can actually expect from ApexSQL Log. ApexSQL Log is the tool that is able to read your transaction log (or transaction log backup) and generate an undo or redo scripts for any INSERT, UPDATE, DELETE or DDL operation you ran against your database. Yeah… It is that cool! Now let’s see more…
Once you complete the installation(a normal Next -> Next -> type of wizard) and fire up the tool you will see something like this:
Enter your credentials and select the database for which you will be “asking” to generate an undo or redo script(or both) and click Next. What you will see on the next screen is the below and on this step you have to choose which will be the source from where you are going to read the information about the transactions that were issued and the transactions that you probably want to rollback now. You can read from both the online transaction log of the database or from a transaction old backup (depending on when the issue happened, etc.).
Once you choose that (or selected every single file / backup) that you have because you are not sure when exactly the problem happened, you have to move forward and apply some filters – again you can guess when the issue happened and apply a time filter, choose what event you want the tool to “search for”, limit the results in terms of which tables have been involved or even customise what information (columns) you want to see. Here are some screenshots:
I will choose the online transaction log file and go ahead and “monitor” for all operations possible (meaning both DML and DDL) and move forward. This is the last step before it gets really interesting – how do you want the results to be displayed. My personal choice is to always stick with the “Display to Grid” option because it’s the most user friendly and full of features. However, you may want to choose one of the other 2 options, so go ahead and take a look at what they can do for you.
What you see now is the main grid window from where you can choose and control everything – filters, options, operations to display, scripts to be generated and much more. Now is the time to go back to SQL Server Management Studio and issue a random DML or DDL statement against the database you connected to. In this blog post I will issue one DML and one DDL because as you can imagine this blog post will become a bit, but just ‘a bit’ long if we want to test out every single event. Let’s start with a DDL. I will go ahead and drop a stored procedure. Let’s say I accidentally dropped dbo.uspPrintError from a copy of the AdventureWorks2012 database. If I then go back at the ApexSQL Log and hit the Refresh button to trigger the scan of the transaction log see what happens:
There it is, our change(don’t focus on the INSERT event – that’s because of a trigger that records every DDL event at the DatabaseLog table). So what can I do now? I can just mark the event and click Create Undo Script.
What you will see in the new window is the stored procedure definition and the option to click Execute(or copy the script and execute it from within SSMS). This will execute the script that the tool generated for you and in general it will rollback our change, so it will recreate the stored proc back. Let’s check if we have it back…
Cool, right? Let’s see a similar example with a DML event! Let say I deleted some rows from a table and now I want them back(sounds familiar?)! Let’s scan the transaction log and see if ApexSQL Log can do something for us. Delete the rows and then click refresh…
Notice 2 things here – I can see who deleted the rows and also what values were there in the row that I accidentally removed. Pretty nice, so now is the time to roll that back. However, because you can select what you want to rollback, you can actually choose which rows you want back (probably you don’t want to rollback everything?). The rows I removed were with ID 8, 12 and 14. Let’s see if I can make them come back. Generate Undo Script -> Execute and back to SSMS.
ApexSQL Log to the rescue!
I hope you are now a bit more interested to just download and play with ApexSQL’s tool. I think it’s a tool that can save your life in a very crucial moment and if you go to their web site, you will see quite some testimonials from their customers about them being able to recover lots and lots of “deleted” data! One last thing you need to know – the licensing is on a per instance basis, so you need 1 license per SQL Server instance. You cannot buy one license and just point it to whichever instance you decide.
Let me know what you think and if you are able to test the tool – would love to hear your thoughts(I promise I will not share them with ApexSQL 🙂 ).
Cheers! I am out to explore Paris now…
Update: One advice here – make sure you always run with the latest version of the product (automatic update is by default turned on, but still) because I noticed an issue with rolling back changes that were done to an object in a different than the dbo schema(it was just not working). After reporting it to the dev team(and those guys are really opened for feedback, trust me), that was fixed and now everything works as you would expect(version 2014.02.1125).