{"id":486,"date":"2012-05-17T03:05:00","date_gmt":"2012-05-17T03:05:00","guid":{"rendered":"\/blogs\/paul\/post\/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx"},"modified":"2026-03-30T19:04:07","modified_gmt":"2026-03-31T02:04:07","slug":"using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/","title":{"rendered":"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN"},"content":{"rendered":"

(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log<\/a>.)<\/em><\/p>\n

I’ve blogged a bunch about using the undocumented fn_dblog<\/em> function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog<\/em> (although I have talked about it at conferences last year).<\/p>\n

Here’s a scenario: 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.<\/p>\n

If the transaction log for the DROP<\/em> hasn’t yet been cleared from the active portion of the log then you’d be able to use fn_dblog<\/em> 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 instructs the log reader to ignore the log truncation point and dump all possible log records from the log.<\/p>\n

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

Or you could save a whole ton of time and use fn_dump_dblog<\/em> which allows you to dump and search log records from a log backup file, without having to restore the database!<\/p>\n

Edit 8\/15\/13: Beware – Jonathan just found out from a customer system that uses this extensively that every time fn_dump_dblog<\/em> is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It’s a bug that the SQL team is going to fix now we’ve alerted them to it. Use with caution.<\/strong><\/p>\n

Edit 5\/15\/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be backported any earlier.<\/strong><\/p>\n

Finding a DROP in the log<\/strong><\/p>\n

Here’s an example – I’m going to create a table, populate it, back it up, then drop it.<\/p>\n

\r\nUSE [master];\r\nGO\r\n\r\nCREATE DATABASE [FNDBLogTest];\r\nGO\r\nUSE [FNDBLogTest];\r\nGO\r\nSET NOCOUNT ON;\r\nGO\r\n\r\n-- Create\u00a0tables to play with\r\nCREATE TABLE [ProdTable] (\r\n    [c1] INT IDENTITY,\r\n    [c2] DATETIME DEFAULT GETDATE (),\r\n    [c3] CHAR (25) DEFAULT 'a');\r\n\r\nCREATE TABLE [ProdTable2] (\r\n    [c1] INT IDENTITY,\r\n    [c2] DATETIME DEFAULT GETDATE (),\r\n    [c3] CHAR (25) DEFAULT 'a');\r\nGO\r\n\r\nINSERT INTO [ProdTable] DEFAULT VALUES;\r\nGO 1000\r\n\r\n-- Take initial backups\r\nBACKUP DATABASE [FNDBLogTest] TO DISK = N'D:\\SQLskills\\FNDBLogTest_Full.bak' WITH INIT;\r\nGO\r\nBACKUP LOG [FNDBLogTest] TO DISK = N'D:\\SQLskills\\FNDBLogTest_Log1.bak' WITH INIT;\r\nGO\r\n\r\nINSERT INTO [ProdTable2] DEFAULT VALUES;\r\nGO 1000\r\n<\/pre>\n
Now I’ll drop the table and add some more log records:<\/p>\n
\r\nDROP TABLE [ProdTable];\r\nGO\r\n\r\nINSERT INTO [ProdTable2] DEFAULT VALUES;\r\nGO 1000\r\n<\/pre>\n
Now how can I find the point at which the table was dropped?<\/p>\n
\r\nSELECT\r\n    [Current LSN],\r\n    [Operation],\r\n    [Context],\r\n    [Transaction ID],\r\n    [Description]\r\nFROM\r\n    fn_dblog (NULL, NULL),\r\n    (SELECT\r\n        [Transaction ID] AS [tid]\r\n    FROM\r\n        fn_dblog (NULL, NULL)\r\n    WHERE\r\n        [Transaction Name] LIKE '%DROPOBJ%') [fd]\r\nWHERE\r\n    [Transaction ID] = [fd].[tid];\r\nGO\r\n<\/pre>\n
 <\/p>\n
\r\nCurrent LSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Context\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Transaction ID Description\r\n---------------------- --------------- ----------------- -------------\u00a0 --------------------------------\r\n0000009d:0000021e:0001 LOP_BEGIN_XACT\u00a0 LCX_NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 DROPOBJ; <snip>\r\n0000009d:0000021e:0002 LOP_LOCK_XACT\u00a0\u00a0 LCX_NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:0003 LOP_LOCK_XACT\u00a0\u00a0 LCX_NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:0008 LOP_MODIFY_ROW\u00a0 LCX_IAM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:0009 LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009b\r\n0000009d:0000021e:000a LOP_MODIFY_ROW\u00a0 LCX_IAM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:000b LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009c\r\n0000009d:0000021e:000c LOP_MODIFY_ROW\u00a0 LCX_IAM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:000d LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009d\r\n0000009d:0000021e:000e LOP_MODIFY_ROW\u00a0 LCX_IAM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:000f LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009e\r\n0000009d:0000021e:0010 LOP_MODIFY_ROW\u00a0 LCX_IAM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n0000009d:0000021e:0011 LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009f\r\n0000009d:0000021e:0012 LOP_MODIFY_ROW\u00a0 LCX_PFS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Deallocated 0001:0000009a\r\n0000009d:0000021e:0013 LOP_HOBT_DDL\u00a0\u00a0\u00a0 LCX_NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\u00a0 Action 3 on HoBt 0xd:100 <snip>\r\n0000009d:0000021e:0014 LOP_DELETE_ROWS LCX_MARK_AS_GHOST 0000:00001ff7\r\n0000009d:0000021e:0032 LOP_LOCK_XACT\u00a0\u00a0 LCX_NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0000:00001ff7\r\n...snip...\r\n<\/pre>\n
Cool eh?<\/p>\n
Now I’ll take another log backup, which clears the log, and contains the log I just looked at.<\/p>\n
\r\nBACKUP LOG [FNDBLogTest] TO DISK = N'D:\\SQLskills\\FNDBLogTest_Log2.bak' WITH INIT;\r\nGO\r\n<\/pre>\n
Who Did the DROP?<\/strong><\/p>\n
If you want to figure out who ran the DROP<\/em> command, look at the Transaction SID<\/em> field for the DROP<\/em> transaction’s LOP_BEGIN_XACT<\/em> log record and then pass that value into the SUSER_SNAME ()<\/em> function. Simple!<\/p>\n
Restoring using STOPBEFOREMARK<\/em><\/strong><\/p>\n
The LSN for the LOP_BEGIN_XACT<\/em> log record is where I need to restore to just before.<\/p>\n
To do that you can just plug in the LSN to\u00a0the STOPBEFOREMARK<\/em> option for RESTORE<\/em>. The option is documented but the format is not – how helpful!!<\/p>\n
 <\/p>\n
The LSN needs to be specified with ‘0x’ in front of it, and then the format is exactly as returned by\u00a0fn_dblog<\/em>.<\/p>\n
The restore sequence to restore to just before the DROP<\/em> is therefore:<\/p>\n
\r\nRESTORE DATABASE [FNDBLogTest2]\r\n    FROM DISK = N'D:\\SQLskills\\FNDBLogTest_Full.bak'\r\nWITH\r\n    MOVE N'FNDBLogTest' TO N'C:\\SQLskills\\FNDBLogTest2.mdf',\r\n    MOVE N'FNDBLogTest_log' TO N'C:\\SQLskills\\FNDBLogTest2_log.ldf',\r\n    REPLACE, NORECOVERY;\r\nGO\r\n\r\nRESTORE LOG [FNDBLogTest2]\r\n    FROM DISK = N'D:\\SQLskills\\FNDBLogTest_Log1.bak'\r\nWITH\r\n    NORECOVERY;\r\nGO\r\n\r\nRESTORE LOG [FNDBLogTest2]\r\nFROM\r\n    DISK = N'D:\\SQLskills\\FNDBLogTest_Log2.bak'\r\nWITH\r\n    STOPBEFOREMARK = 'lsn:0x0000009d:0000021e:0001',\r\n    NORECOVERY;\r\nGO\r\n\r\nRESTORE DATABASE [FNDBLogTest2] WITH RECOVERY;\r\nGO\r\n<\/pre>\n
And the table is there again, right before the point it was dropped.\u00a0You can see where I used the constructed LSN string in the final log restore.<\/p>\n
Using fn_dump_dblog<\/em><\/strong><\/p>\n
So what if the log records are no longer in the log? I can use the fn_dump_dblog<\/em> function.<\/p>\n
Edit 8\/15\/13: Beware – Jonathan just found out from a customer system that uses this extensively that every time\u00a0fn_dump_dblog<\/em>\u00a0is called, it creates a new hidden SQLOS scheduler and up to three threads, which will\u00a0not go away (and will not be reused) until a server restart<\/strong>. It’s a bug that the SQL team is going to fix now we’ve alerted them to it. Use with caution.<\/strong><\/p>\n
Edit 5\/15\/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be backported any earlier.<\/strong><\/p>\n
For instance, here is how I can use it to look in the FNDBLogTest_Log2.bak<\/em> backup:<\/p>\n
\r\nSELECT\r\n    COUNT (*)\r\nFROM\r\n    fn_dump_dblog (\r\n        NULL, NULL, N'DISK', 1, N'D:\\SQLskills\\FNDBLogTest_Log2.bak',\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);\r\nGO\r\n<\/pre>\n
I have to specify all the DEFAULT<\/em> parameters (63 of them!) or it won’t work. The other parameters are:<\/p>\n
    \n
  • Starting LSN (usually just NULL)<\/li>\n
  • Ending LSN (again, usually just NULL)<\/li>\n
  • Type of file (DISK or TAPE)<\/li>\n
  • Backup number within the backup file (for multi-backup media sets)<\/li>\n
  • File name<\/li>\n<\/ul>\n
    So I could do the same query as I did above:<\/p>\n
    \r\nSELECT\r\n    [Current LSN],\r\n    [Operation],\r\n    [Context],\r\n    [Transaction ID],\r\n    [Description]\r\nFROM\r\n    fn_dump_dblog (\r\n        NULL, NULL, N'DISK', 1, N'D:\\SQLskills\\FNDBLogTest_Log2.bak',\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT),\r\n    (SELECT\r\n        [Transaction ID] AS [tid]\r\n    FROM\r\n        fn_dump_dblog (\r\n            NULL, NULL, N'DISK', 1, N'D:\\SQLskills\\FNDBLogTest_Log2.bak',\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n            DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)\r\n    WHERE\r\n        [Transaction Name] LIKE '%DROPOBJ%') [fd]\r\nWHERE [Transaction ID] = [fd].[tid];\r\nGO\r\n<\/pre>\n
    Which works perfectly, but takes much longer to run.<\/p>\n
    So maybe you’re wondering what all the other parameters to fn_dump_dblog<\/em> are for? They are for specifying the media families of a media set that has more than one media family.<\/p>\n
    Here’s an example using a log backup striped across two files:<\/p>\n
    \r\nBACKUP LOG [FNDBLogTest] TO\r\n    DISK = N'D:\\SQLskills\\FNDBLogTest_Log3_1.bak',\r\n    DISK = N'D:\\SQLskills\\FNDBLogTest_Log3_2.bak'\r\nWITH INIT;\r\nGO\r\n<\/pre>\n
    If I try to use fn_dump_dblog<\/em> and only specify a single file, I get an error:<\/p>\n
    \r\nSELECT\r\n    COUNT (*)\r\nFROM\r\n    fn_dump_dblog (\r\n        NULL, NULL, N'DISK', 1, N'D:\\SQLskills\\FNDBLogTest_Log3_1.bak',\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);\r\nGO\r\n<\/pre>\n
    \r\nMsg 3132, Level 16, State 1, Line 1\r\nThe media set has 2 media families but only 1 are provided. All members must be provided.\r\n<\/pre>\n
    So I have to specify both media families:<\/p>\n
    \r\nSELECT\r\n    COUNT (*)\r\nFROM\r\n    fn_dump_dblog (\r\n        NULL, NULL, N'DISK', 1, N'D:\\SQLskills\\FNDBLogTest_Log3_1.bak',\r\n        N'D:\\SQLskills\\FNDBLogTest_Log3_2.bak', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,\r\n        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);\r\nGO\r\n<\/pre>\n
    Summary<\/strong><\/p>\n
    So there you go – some more powerful tools to add to your disaster recovery arsenal.<\/p>\n
    Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
    (Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) I’ve blogged a bunch about using the undocumented fn_dblog function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog (although I have talked about it at […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,35,98,100],"tags":[],"class_list":["post-486","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-disaster-recovery","category-transaction-log","category-undocumented-commands"],"yoast_head":"\nUsing fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) I’ve blogged a bunch about using the undocumented fn_dblog function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog (although I have talked about it at […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-17T03:05:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-03-31T02:04:07+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/\",\"name\":\"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2012-05-17T03:05:00+00:00\",\"dateModified\":\"2026-03-31T02:04:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/","og_locale":"en_US","og_type":"article","og_title":"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal","og_description":"(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) I’ve blogged a bunch about using the undocumented fn_dblog function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog (although I have talked about it at […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/","og_site_name":"Paul S. Randal","article_published_time":"2012-05-17T03:05:00+00:00","article_modified_time":"2026-03-31T02:04:07+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/","name":"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2012-05-17T03:05:00+00:00","dateModified":"2026-03-31T02:04:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/486","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=486"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/486\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}