{"id":936,"date":"2009-03-07T08:57:00","date_gmt":"2009-03-07T08:57:00","guid":{"rendered":"\/blogs\/paul\/post\/How-can-a-log-backup-fail-but-a-full-backup-succeed.aspx"},"modified":"2017-04-13T12:47:37","modified_gmt":"2017-04-13T19:47:37","slug":"how-can-a-log-backup-fail-but-a-full-backup-succeed","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/","title":{"rendered":"How can a log backup fail but a full backup succeed?"},"content":{"rendered":"

This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn’t work (with the error below):<\/span><\/p>\n

Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 ‘F:\\SQLLOGS\\XYZ\\FakeDBName_Log.ldf’ VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400
\n2009-03-06 10:00:02.61 Backup\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Error: 3041, Severity: 16, State: 1.
\n2009-03-06 10:00:02.61 Backup\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BACKUP failed to complete the command BACKUP LOG\u00a0FakeDBName Check the backup application log for detailed messages.
\n2009-03-06 10:00:03.61 Backup\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Error: 3041, Severity: 16, State: 1.<\/span> <\/span><\/p><\/blockquote>\n

However a full backup succeeds, as does a DBCC CHECKDB<\/span>. What’s going on?<\/span><\/p>\n

The answer comes with understanding what portions of the transaction log are required for these operations. (For a good understanding of the transaction log itself, along with logging and recovery, see my <\/span>article in the February TechNet Magazine<\/span><\/a>.)<\/span><\/p>\n

A transaction log backup, by its very nature, has to backup *all* transaction log generated since the last log backup – so it will try to backup the corrupt portion and fail.<\/span><\/p>\n

A full database backup only has to backup enough transaction log to allow the database to be restored and recovered to a transactionally consistent point. In other words, it only requires the transaction log back to the beginning of the oldest active transaction at the point that the data-reading section of the full backup completes. This is a source of immense confusion – many people don’t believe that a full (or differential) backup needs to also backup some transaction log. For a more in-depth study of this, see my previous blog posts <\/span>Debunking a couple of myths around full database backups<\/span><\/a>\u00a0and <\/span>More on how much transaction log a full backup includes<\/span><\/a>.<\/span><\/p>\n

A DBCC CHECKDB<\/span> operation uses a database snapshot to get a transactionally consistent view of the database on which to run consistency checks. When the database snapshot is created, crash recovery is run on it to make it transactionally consistent. That requires the same amount of log as if a full backup was taken – back to the beginning of the oldest active transaction at the time the database snapshot is created. See <\/span>CHECKDB From Every Angle: Complete description of all CHECKDB stages<\/span><\/a>\u00a0for more info.<\/span><\/p>\n

So – it’s entirely possible for the situation reported above to exist. The question then becomes, how to recover from it?<\/span><\/p>\n

Assuming that the database files are intact, there is a simple solution. This solution will break the log backup chain, but given that the log is corrupt so a log backup cannot be taken, the log backup chain is *already* broken. Here’s what to do:<\/span><\/p>\n

    \n
  1. \n
    Stop all user activity in the database<\/span><\/div>\n<\/li>\n
  2. \n
    Switch to the SIMPLE<\/span> recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up)<\/span><\/div>\n<\/li>\n
  3. \n
    Switch to the FULL<\/span> recovery model<\/span><\/div>\n<\/li>\n
  4. \n
    Take a full database backup (thus starting a new log backup chain)<\/span><\/div>\n<\/li>\n
  5. \n
    Start taking log backups<\/span><\/div>\n<\/li>\n<\/ol>\n

    You might want to manually shrink and grow the log file in between steps 2 and 3 too – in case the log file is on a damaged portion of disk – or maybe even shrink it right down and add another log file on an undamaged disk. You also will need to do some root-cause analysis to determine why the corruption occured in the first place, and to take preventative measures to stop it happening again.<\/span><\/p>\n

    Hope this helps<\/span><\/p>\n

    PS In my previous post, Testing a new survey method: backup validation<\/a>, the answer with the largest number of responses so far\u00a0is that\u00a0people never verify their backups – very disturbing!<\/span><\/p>\n

    PPS If a subsequent log backup actually succeeds, you’ve likely got some kind of transient I\/O subsystem problem. See here<\/a> for more details.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn’t work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 ‘F:\\SQLLOGS\\XYZ\\FakeDBName_Log.ldf’ VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,30,31,52,98],"tags":[],"class_list":["post-936","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-corruption","category-database-maintenance","category-involuntary-dba","category-transaction-log"],"yoast_head":"\nHow can a log backup fail but a full backup succeed? - 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\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can a log backup fail but a full backup succeed? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn’t work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 ‘F:SQLLOGSXYZFakeDBName_Log.ldf’ VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-07T08:57:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:47:37+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=\"3 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\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/\",\"name\":\"How can a log backup fail but a full backup succeed? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-03-07T08:57:00+00:00\",\"dateModified\":\"2017-04-13T19:47:37+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can a log backup fail but a full backup succeed?\"}]},{\"@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":"How can a log backup fail but a full backup succeed? - 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\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/","og_locale":"en_US","og_type":"article","og_title":"How can a log backup fail but a full backup succeed? - Paul S. Randal","og_description":"This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn’t work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 ‘F:SQLLOGSXYZFakeDBName_Log.ldf’ VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-07T08:57:00+00:00","article_modified_time":"2017-04-13T19:47:37+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/","name":"How can a log backup fail but a full backup succeed? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-03-07T08:57:00+00:00","dateModified":"2017-04-13T19:47:37+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-can-a-log-backup-fail-but-a-full-backup-succeed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"How can a log backup fail but a full backup succeed?"}]},{"@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\/936","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=936"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/936\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=936"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=936"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=936"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}