“What are you smiling about?” my friend asked.
“I’m just giddy…” I replied.
“Why?”
“You won’t understand.”
“Try me.”
“Well, last night, I found out about fn_dump_dblog.”
“Uh-huh. So what are those? Shoes?”
Hooooo-kay! 
Moving on…
With a transaction log backup, you gain the ability to restore your database to a point-in-time contained in the backup. For example, if a table is accidentally emptied via a DELETE operation that’s not within an explicit transaction, you can:
1) If possible, stop all database activities as soon as you learn about the accident then take a transaction log backup.
2) Restore the most recent full database backup and most recent differential backup (if any) to a new database (WITH NORECOVERY).
3) Apply your transaction logs up to the point prior to the delete.
4) When you have your deleted records back (in the new database), re-insert them to the table in the original database. Once you have inserted the data back, you would have to do some work to ensure that your database is how it really should be (i.e. if the DELETE that you’re trying to undo didn’t happen, what would the database state be?).
The tricky part really is figuring out up to what TIME to restore.
1) If you know the exact time, just apply each transaction log backup and specify STOPAT=TIME, WITH RECOVERY. As long as the time you specified is not met, the database will remain unrecovered. For example,
RESTORE LOG NewDB FROM DISK='C:BackupAdventureWorks.trn' WITH RECOVERY,
STOPAT = 'Aug 12, 2009 10:00:00 AM'
GO
2) If you know just the time range (for example, from 3:15PM-3:30PM), it’s a bit more tricky. You need to restore your log backups to different times (3:15PM, 3:16PM, 3:17PM, and so on) using STOPAT and you need to be able to look at the data each time before restoring more backups, and before changing the STOPAT time. SQL Server allows you to do this using an option called STANDBY. It allows you to recover your database, read it (you cannot modify), and restore more log backups. Check Tibor Karaszi’s Minimizing Data Loss for more information on how to do this.
The less you know about the time the accident happened, the more tedious the recovery gets. Imagine doing this if all you know is that the delete occurred yesterday and no time is specified…12 midnight…1am…2am…it’s not a good situation to be in.
Is there an alternative?
Yes. You can restore to a log sequence number instead–or to a point before it. You can look at restoring to an LSN as an alternative or a complement to the point-in-TIME restore. There’s some guesswork involved though. What you need are:
1) The name of the table
2) The number of records deleted (or at least, an estimate)
3) The time of the disaster/accident. You will see later that this is still a nice-to-have.
To illustrate, let’s delete all records in the AdventureWorks’s Production.TransactionHistory table. I’m using SQL Server 2005.
--delete all records
delete from Production.TransactionHistory
This deletes 113443 records.
Finding the LSN of the Point of Disaster
fn_dblog is a system UDF that’s undocumented by Microsoft. Information about it can be found online (yeah, it’s very well documented for something that’s supposedly undocumented). fn_dblog exposes to you some information about the database’s transaction log.
The plan is to use fn_dblog first to find the LSN of the point of disaster, and, if we don’t find it, to use fn_dump_dblog instead. fn_dump_dblog shows similar information as fn_dblog and more. fn_dump_dblog can read transaction log backups.
If the transaction that we’re looking for is not in the database’s transaction log, most likely it’s been overwritten and is already in a transaction log backup.
Reminder: Use fn_dblog, fn_dbdump_dblog, and other undocumented functions with caution, especially in production DBs. Actually, when I ran the scripts I used in this post on my development DB, they were extremely slow.
Also note that future versions or service packs may change how these functions behave. Make sure to test the scripts where you use them every time you make a change to your SQL Server installation.
1) Find the transaction’s Transaction ID. All the delete operations within the transaction will belong to one Transaction ID. We can find this using fn_dblog and the estimated/actual number of deletions.
USE AdventureWorks
GO
SELECT [Transaction ID], count(*)
FROM fn_dblog(DEFAULT, DEFAULT)
where AllocUnitName LIKE '%Production.TransactionHistory%'--table name
GROUP BY [Transaction ID]
HAVING COUNT(*) >= 113443 --(estimated/actual) number of deleted records
The result is this:
Transaction ID REC_COUNT
0000:0000130c 340329
(1 row(s) affected)
Deletions that are done on heaps will have a count that’s closer to the actual number of records deleted. The presence of indexes will result to a higher count but just keep in mind that the number of records deleted will be the minimum count.
If above query results to more than one Transaction ID, knowing the time (or even just a time range) that the delete happened really helps. We will discuss this later.
2) Next, get all the operations that have the Transaction ID we found in the previous step to verify if it is the transaction we are looking for.
SELECT
[Current LSN],
Operation,
Context,
[Transaction ID],
[AllocUnitId],
[AllocUnitName],
[UID],
[SPID],
[Begin Time],
[Transaction Name]
FROM fn_dblog(DEFAULT, DEFAULT)
WHERE [Transaction ID] = '0000:0000130c'
ORDER BY [Current LSN]
The results are as shown below:
Verifying the Results
The delete we performed will show as Operations = LOP_DELETE_ROWS in the transaction log. That’s what we see in (1). The table we deleted from has the following indexes:
and this coincides with (2).
The date in (3) is what we would have to use if we have several Transaction ID candidates and we’re not exactly sure which transaction to select. If we have the time of the accident (or at least, an estimate) we can select the transaction that has a [BEGIN TIME] closest to the time we have. This is the reason I said earlier that the date is nice to have.
So, based on the results, it looks like we have the correct Transaction ID.
4) Look for the LSN of the point of disaster.
This would be the first LSN of the transaction and the value of Operation should be LOP_BEGIN_XACT. In this example, it’s 00000026:00000458:0002. BINGO! This is our point of disaster. We want to restore not to this LSN but right before it.
Performing the Restore
Restore the most recent database backup and differential backup (if present) and apply the transaction log backups specifying the LSN where the restore should stop (right before).
--execute this after restoring the full database backup, the differential backup (if any), and the subsequent transaction log backups
RESTORE LOG [NewDB] FROM DISK = N'C:BackupAdventureWorks.trn' --NewDB is the new database we're restoring to
WITH STOPBEFOREMARK= 'lsn:00000026:00000458:0002' --this is the LSN of the beginning of the transaction which is our point of disaster
GO
RESTORE DATABASE NewDB
WITH RECOVERY;
Once the restore is completed, extract the deleted data from the new db and re-insert them to the original table.
What if we can’t find the delete transaction using fn_dblog?
Turn on trace flag 2537 and try again. If you still can’t find it, a transaction log backup may have been performed already and the delete transaction in the log may have already been overwritten. If this is the case, then we need to do the same steps we did above but this time, we want to use the fn_dump_dblog command. I discovered fn_dump_dblog via…what else…GOOGLE! Yeah, finding it made me GIDDY and no, fn_dump_dblog “ain’t” shoes.
As I said earlier, it shows pretty much the same info as fn_dblog BUT it has the added ability to read transaction log backups. Because we no longer have the transactions in the database’s transaction log, we will attempt to read the transaction log backup file that was created right after the accident happened using fn_dump_dblog.
SELECT [Transaction ID], count(*)
FROM fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'C:BackupAdventureWorks.trn', DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
GROUP BY [Transaction ID]
HAVING COUNT(*) >= 113443 --(estimated/actual) number of deleted records
In this test case, we only get one Transaction ID that meets the conditions we specified (it’s the same Transaction ID that we got earlier using fn_dblog). If there are more, we need to look at the operations of each Transaction ID and make an (educated) guess as to what Transaction ID corresponds to the accidental delete. The problem with fn_dump_dblog is for some undocumented reason, the AllocUnitName is empty. This makes it difficult to know what object/entity the DELETE operation was performed on. Regardless, there are still ways we can extract the information we need.
To check the operations of the Transaction ID that results from above code, we run the following:
SELECT
[Current LSN],
Operation,
Context,
[Transaction ID],
[AllocUnitId],
[AllocUnitName],
[UID],
[SPID],
[Begin Time],
[Transaction Name]
FROM fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'C:BackupAdventureWorks.trn', DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
WHERE [Transaction ID] = '0000:0000130c' --change this to WHERE [Transaction ID] IN ('0000:0000130c', ...) if there's more than 1 Transaction ID candidate
ORDER BY [Current LSN]
Here’s the result:
I have highlighted the fields you can look at to verify the LSN of the point of disaster. Pick the transaction that has a BEGIN TIME that’s closest to the time of accident. The time information here becomes more important since we don’t have the AllocUnitName. For this reason, I would recommend that you try to find the LSN while the transaction is still in the database’s log.
Tips
a) If you’re in full recovery model and have done a full database backup yet have never taken transaction log backups, you should be. 
If a minor accident like the example discussed in this post happens to you, you can still use above steps. I recommend executing the fn_dblog command first to get the LSN of the point of disaster and then perform a transaction log backup (you would need it to do a point-in-time restore.) And of course, going forward, continue doing transaction log backups.
b) Third-party transaction log reading tools can help you eliminate all the guesswork so also consider these options when trying to find the LSN of the point of disaster.
c) Going forward, secure your database to make sure users are not allowed to make these disastrous changes to the production database. Also consider using transaction log shipping and configure it such that the logs are restored to your destination only after some delay. This gives you quick access to an older state of your database should a disaster like the one discussed in this post happens. I’ll be discussing this more in detail in a future post. Tibor Karaszi’s Minimizing Data Loss also has some tips on avoiding this disaster in the future.
More Information
Recovering to a Log Sequence Number
And they will really say hi!
). When I woke up from the surgery (yay! I survived!), I felt lucky, yet, I also felt irrelevant. I started asking, how many people have I helped? How many people have I made an impact on?
