Backup & Restore

Using fn_dblog, fn_dump_dblog and restoring with STOPBEFOREMARK

I’ve found this very useful article wrote by Paul Randal and decide to rewrite it on my own blog.  You can find full and the original article in this link.

Someone dropped a table and you want to find out when it happened and maybe who did it. The default trace has also wrapped so you can’t get the DDL trace from there anymore. If the transaction log for DROP hasn’t yet been cleared from the active portion of the log then you’d be able to use fn_dblog to search through the log for the information you need. You might even be able to look in the inactive portion of the log by using trace flag 2536, which instruct the log leader to ignore the log truncation point and dump all possible log records from the log.

But what do yo do if the pertinent log records just don’t exist in the log anymore? They are only in your transaction log backup. You could tediously inch your way through restoring the log backup a few seconds at a time until you find a point at which the DROP took place, and then restore to just before that point so you can get the data back.

Or you could save a whole ton of time and use fn_dump_dblog which allow you to dump and search log records from a log backup file without having to restore the database!

Finding a DROP in the log

USE [master];
GO

CREATE DATABASE [FNDBLogTest];
GO
USE [FNDBLogTest];
GO
SET NOCOUNT ON;
GO
 
-- Create tables to play with
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] DATETIME DEFAULT GETDATE (),
    [c3] CHAR (25) DEFAULT 'a');
 
CREATE TABLE [ProdTable2] (
    [c1] INT IDENTITY,
    [c2] DATETIME DEFAULT GETDATE (),
    [c3] CHAR (25) DEFAULT 'a');
GO
 
INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1000
 
-- Take initial backups
BACKUP DATABASE [FNDBLogTest] TO DISK = N'F:\DB_BACKUP\FNDBLogTest_Full.bak' WITH INIT;
GO
BACKUP LOG [FNDBLogTest] TO DISK = N'F:\DB_BACKUP\FNDBLogTest_Log1.bak' WITH INIT;
GO
 
INSERT INTO [ProdTable2] DEFAULT VALUES;
GO 1000

Now I’ll drop the table  and add some more log records :

DROP TABLE [ProdTable];
GO
 
INSERT INTO [ProdTable2] DEFAULT VALUES;
GO 1000

Now, how can I find the point at which the table was dropped?

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Transaction SID],
    [Description]
FROM
    fn_dblog (NULL, NULL),
    (SELECT
        [Transaction ID] AS [tid]
    FROM
        fn_dblog (NULL, NULL)
    WHERE
        [Transaction Name] LIKE '%DROPOBJ%') [fd]
WHERE
    [Transaction ID] = [fd].[tid];
GO

fn_dblog-result

Now I’ll take another log backup, which clear the log, and contains the log I just looked at.

BACKUP LOG [FNDBLogTest] TO DISK = N'F:\DB_BACKUP\FNDBLogTest_Log2.bak' WITH INIT;
GO

Who did the DROP ?

If you want to figure out who ran the DROP command, look at the transaction SID field for DROP transaction’s LOP_BEGIN_XACT log record and then pass the value into the SUSER_SNAME() function. Simple!

which-user-did-drop

Restoring using STOPBEFOREMARK

The LSN for LOP_BEGIN_XACT log record is where I need to restore to just before.

To do that you can just plug in the LSN to STOPBEFOREMARK option for RESTORE. The option is documented but the format is not how helpful!!!

The LSN need to be specified with ‘0x’ in front of it, and then the format is exactly as returned by fn_dblog.

The restore sequence to restore to just before DROP is therefore :

RESTORE DATABASE [FNDBLogTest2]
    FROM DISK = N'F:\DB_BACKUP\FNDBLogTest_Full.bak'
WITH
    MOVE N'FNDBLogTest' TO N'F:\SQLServerInstance\FNDBLogTest2.mdf',
    MOVE N'FNDBLogTest_log' TO N'F:\SQLServerInstance\FNDBLogTest2_log.ldf',
    REPLACE, NORECOVERY;
GO
 
RESTORE LOG [FNDBLogTest2]
    FROM DISK = N'F:\DB_BACKUP\FNDBLogTest_Log1.bak'
WITH
    NORECOVERY;
GO
 
RESTORE LOG [FNDBLogTest2]
FROM
    DISK = N'F:\DB_BACKUP\FNDBLogTest_Log2.bak'
WITH
    STOPBEFOREMARK = 'lsn:0x00000022:000008ac:0001',
    NORECOVERY;
GO
 
RESTORE DATABASE [FNDBLogTest2] WITH RECOVERY;
GO

And the table is there again, right before the point it was dropped. You can see where I used the constructed LSN string in the final log restore.

Using fn_dump_dblog

So what if the log records are no longer in the log? I can use the fn_dump_dblog function. For instance, here is how I can use to look in the FNDBLogTest_Log2.bak backup :

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'F:\DB_BACKUP\FNDBLogTest_Log2.bak',
        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);
GO

I have to specify all the DEFAULT parameter (63 of them)! or it won’t work. The other parameters are :

  • Starting LSN (usually just NULL)
  • Ending LSN (again, usually just NULL)
  • Type of file (DISK or TAPE)
  • Backup number within the backup file (for multi-backup media sets)
  • File name

So I could do the same query as I did above :

SELECT
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [Transaction SID],
    [Description]
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'F:\DB_BACKUP\FNDBLogTest_Log2.bak',
        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),
    (SELECT
        [Transaction ID] AS [tid]
    FROM
        fn_dump_dblog (
            NULL, NULL, N'DISK', 1, N'F:\DB_BACKUP\FNDBLogTest_Log2.bak',
            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 Name] LIKE '%DROPOBJ%') [fd]
WHERE [Transaction ID] = [fd].[tid];
GO

Which works perfectly, but takes much longer to run.

So maybe you’re wondering what all other parameters to fn_dump_dblog for? They are for specifying the media families of a media sets that has more than one family.

Here is an example using a log backup striped across two files :

BACKUP LOG [FNDBLogTest] TO
    DISK = N'F:\DB_BACKUP\FNDBLogTest_Log3_1.bak',
    DISK = N'F:\DB_BACKUP\FNDBLogTest_Log3_2.bak'
WITH INIT;
GO

If I try to use fn_dump_dblog and only specify a single file, I get an error :

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'F:\DB_BACKUP\FNDBLogTest_Log3_1.bak',
        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);
GO

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.

So I have to specify both media families :

SELECT
    COUNT (*)
FROM
    fn_dump_dblog (
        NULL, NULL, N'DISK', 1, N'F:\DB_BACKUP\FNDBLogTest_Log3_1.bak',
        N'F:\DB_BACKUP\FNDBLogTest_Log3_2.bak', 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);
GO

Summary

There you go – some more powerful tools to add to your disaster recovery arsenal.

Enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s