|
|
|
|
|
Log Explorer 2.0 1 2 3 4 5 6 7 8 9 10 11 12 13 The salvage data option is a new feature to Log Explorer that extends the functionality from version 1.0. As I noted previously in this review, dropping a table is one of the tests that I threw at version 1.0 only to watch it fail, because that capability wasn't built in. Many people will tell you that once you delete a table, you can only get it back by restoring the database. That's not technically accurate. To understand why and how Log Explorer can do this, you need to understand exactly what happens when you drop a table. SQL Server maintains a list of pointers to pages that it considers as free. This is called the free pool. When you drop or truncate a table, all of the pages associated to that table are returned to the free pool. You now have empty space on a data page as far as SQL Server is concerned that used to be occupied by a row. However, SQL Server does not reclaim this space automatically. That space will remain on the data page forever unless you do something to cause SQL Server to clear it out like shrinking the database, reindexing the table, or if SQL Server needs to allocate a data page and happens to choose one from the free pool that was associated to that table. Now this is empty space as far as SQL Server is concerned, BUT the data does still physically exist in that same location. When you drop a table, you are removing entries from the system tables that tell SQL Server to find that table and its associated data pages. What you aren't doing is writing the actual delete of that row into the transaction log which is why dropping a table is much faster than deleting all of the data rows. You do write an entry into the transaction log that is the drop table command along with the associated commands that remove the data from the system tables. The data associated with that dropped table is still physically present on the disk. It is just not accessible because none of the entries exist in the system tables for the table that you dropped. Based on that information, how do you recover a dropped table and its associated data without restoring the database? You simply have to be able to put the entries back into the system tables that were related to that dropped table and then remove the associated data pages from the free pool. That information does exist in the transaction log and so Log Explorer can reconstruct that information and bring the table back in with all data intact. If you have shrunk the database, this is no longer possible, because SQL Server will reclaim any unused space on the data pages and physically remove them. If SQL Server has allocated a page from the free pool that was associated to that table, all data will be lost. In this case, you will be able to recover all data in a dropped/truncated table except the data on pages that were allocated to another purpose.If you have a current database backup and all transaction logs, Log Explorer will use the information in the backups plus the active transaction log and can make a 100% reliable recovery of the entire table. This is a simple one click operation that effectively restores a dropped table. Without Log Explorer, you are in for a LOT of work to recover just a single dropped table using your existing tools. The Salvage Data screen allows you to specify the table name along with several options. You can recover from a truncated table or a table that was dropped.
Another piece of functionality missing in version 1.0 was the ability to run a script. You could generate scripts to undo or redo data, but then you had to start up Query Analyzer, open the script, and execute it. The ability to run the recovery scripts you generate without leaving Log Explorer reduces the recovery time from what it originally was.
A couple of years ago, I wrote the product specifications and justification for a vendor to build a product just like Log Explorer. That product never materialized even though I told them that there isn't a SQL Server installation that couldn't benefit from the product and also that there were no competitors. Imagine my surprise when I was walking through the lobby at VSLive! in Orlando last year and caught a glimpse of some product from a company I had never heard of that had most of the features I put into that design specification. That brought me to version 1.0 of Log Explorer. At that time Log Explorer had most of the features that I needed. Even though the interface was a little difficult to work with, the fact is that it worked and did what it needed to do. With version 2.0, not only is Log Explorer a must have product, but it is also feature complete and flexibility allows you to recover data quickly and easily while leaving your database online. I can't recommend Log Explorer highly enough for your company/client. The first time you have to track down data issues or recover lost data, you will be glad that Log Explorer exists. Instead of spending nights and weekends attempting to recover lost data, you can be at home relaxing instead because Log Explorer just turned a very labor intensive and virtually impossible task into something that is point and click. Log Explorer is produced by Lumigent Technologies and is available with a variety of licensing options. |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.
