{"id":680,"date":"2010-06-18T09:21:00","date_gmt":"2010-06-18T09:21:00","guid":{"rendered":"\/blogs\/paul\/post\/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx"},"modified":"2017-04-13T09:54:42","modified_gmt":"2017-04-13T16:54:42","slug":"disaster-recovery-101-backing-up-the-tail-of-the-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/","title":{"rendered":"Disaster recovery 101: backing up the tail of the log"},"content":{"rendered":"

(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption<\/a>\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques<\/a>. We can also\u00a0help you<\/a>\u00a0with disaster recovery.)<\/p>\n

One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail of the log.<\/p>\n

The tail of the log\u00a0contains the transaction log that’s been generated since the most recent log backup was taken. If you want to be able to recover right up to the point of the disaster, you need to be able to get those log records so they can be part of the restore sequence.<\/p>\n

There are two cases to consider here 1) the server is still available 2) the server is not available. For case 1, you can just perform a regular tail-of-the-log backup. For case 2, you’ll need to hack-attach the log into another server to be able to back it up.<\/p>\n

Case 1: tail-of-the-log backup when server is available<\/strong><\/p>\n

If the database is damaged but the server is still available, taking a tail-of-the-log backup is simple. The only exception is when there has been a minimally-logged operation performed in the BULK_LOGGED<\/em> recovery model since the previous log backup – in that case a tail-of-the-log backup is not possible at all, and you’ll have lost all transaction log generated since the last log backup. See A SQL Server DBA myth a day: (28\/30) BULK_LOGGED recovery model<\/a> for more details.<\/p>\n

When the data files are damaged and you want to take a log backup, you’ll get an error if you try to back up the log normally.<\/p>\n

As an example, I’ll create a database and put some transactions in it:<\/p>\n

\r\nCREATE DATABASE [DBMaint2008];\r\nGO\r\nUSE [DBMaint2008];\r\nGO\r\n\r\nCREATE TABLE [TestTable] ([C1] INT IDENTITY, [C2] CHAR (100));\r\nGO\r\n\r\n-- Take a full backup\r\nBACKUP DATABASE [DBMaint2008] TO DISK = N'D:\\SQLskills\\DemoBackups\\DBMaint_Full.bck' WITH INIT;\r\nGO\r\n\r\n-- Insert some rows\r\nINSERT INTO [TestTable] VALUES ('Transaction 1');\r\nINSERT INTO [TestTable] VALUES ('Transaction 2');\r\nGO\r\n\r\n-- Take a log backup\r\nBACKUP LOG [DBMaint2008] TO\u00a0DISK = N'D:\\SQLskills\\DemoBackups\\DBMaint_Log1.bck'\u00a0WITH INIT;\r\nGO\r\n\r\n-- Insert some more rows\r\nINSERT INTO [TestTable] VALUES ('Transaction 3');\r\nINSERT INTO [TestTable] VALUES ('Transaction 4');\r\nGO\r\n<\/pre>\n
Now disaster will strike – I’ll do the following to simulate a disaster:<\/p>\n
    \n
  1. ALTER DATABASE [DBMaint2008] SET OFFLINE;<\/em><\/li>\n
  2. Delete the data file<\/li>\n<\/ol>\n
    If\u00a0I try to then access the database, I’ll get:<\/p>\n
    \r\nALTER DATABASE DBMaint2008 SET ONLINE;\r\nGO\r\n<\/pre>\n
    \r\nMsg 5120, Level 16, State 101, Line 1\r\nUnable to open the physical file "C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)".\r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\nMsg 5069, Level 16, State 1, Line 1\r\nALTER DATABASE statement failed.\r\n<\/pre>\n
    So I’ll try a normal log backup:<\/p>\n
    \r\nBACKUP LOG [DBMaint2008] TO\u00a0DISK = N'D:\\SQLskills\\DemoBackups\\DBMaint_Log_Tail.bck' WITH INIT;\r\nGO\r\n<\/pre>\n
    \r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\nMsg 3013, Level 16, State 1, Line 1\r\nBACKUP LOG is terminating abnormally.\r\n<\/pre>\n
    It doesn’t work – as the data files aren’t all accessible.<\/p>\n
    The trick is to use the NO_TRUNCATE<\/em> option, which allows the log backup even if the database files aren’t there:<\/p>\n
    \r\nBACKUP LOG [DBMaint2008] TO\u00a0DISK = N'D:\\SQLskills\\DemoBackups\\DBMaint_Log_Tail.bck'\u00a0WITH INIT, NO_TRUNCATE;\r\nGO\r\n<\/pre>\n
    \r\nProcessed 2 pages for database 'DBMaint2008', file 'DBMaint2008_log' on file 1.\r\nBACKUP LOG successfully processed 2 pages in 0.010 seconds (0.972 MB\/sec).\r\n<\/pre>\n
    I can then use the tail-of-the-log backup as the final backup in the restore sequence, saving transactions 3 and 4 from above. Try it for yourself.<\/p>\n
    Case 2: tail-of-the-log backup when server is no longer available<\/strong><\/p>\n
    This is the case where the server crashed and cannot be brought online. If you have access to all the data and log files for the database, you can attach it to another server and crash recovery will run automatically. If you only have access to the log file, you’ll need to perform some extra steps to allow a tail-of-the-log backup to be performed – basically performing what I call a hack-attach.<\/p>\n
    Assuming I’ve run the script above to setup the database, this time I’ll do the following to simulate a server-crash disaster:<\/p>\n
      \n
    1. ALTER DATABASE [DBMaint2008] SET OFFLINE;<\/em><\/li>\n
    2. Delete the data file<\/li>\n
    3. Copy the log file somewhere else<\/li>\n
    4. DROP DATABASE [DBMaint2008];<\/em><\/li>\n<\/ol>\n
      Now all I have is some backups and a log file. I’ll need to attach the log file to SQL Server somehow so that I can perform the vital tail-of-the-log backup. The way to do it is:<\/p>\n
        \n
      1. Create a dummy database with the same name as the one that we’re interested in (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post)<\/a><\/li>\n
      2. Set the database offline (or shutdown the server)<\/li>\n
      3. Delete all the files from the dummy database<\/li>\n
      4. Drop in the log file from our real database<\/li>\n<\/ol>\n
        Like so for steps 1 and 2:<\/p>\n
        \r\nCREATE DATABASE [DBMaint2008];\r\nGO\r\nALTER DATABASE [DBMaint2008] SET OFFLINE;\r\nGO\r\n<\/pre>\n
        Now I’ll perform steps 3 and 4.<\/p>\n
        I\u00a0need to attempt to bring the database online again:<\/p>\n
        \r\nALTER DATABASE [DBMaint2008] SET ONLINE;\r\nGO\r\n<\/pre>\n
        \r\nMsg 5120, Level 16, State 101, Line 1\r\nUnable to open the physical file "C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)".\r\nMsg 945, Level 14, State 2, Line 1\r\nDatabase 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.\u00a0 See the SQL Server errorlog for details.\r\nMsg 5069, Level 16, State 1, Line 1\r\nALTER DATABASE statement failed.\r\n<\/pre>\n
        And then I can perform the tail-of-the-log backup as before, and use it to recover everything up to the point of the disaster.<\/p>\n
        Note<\/strong>: This procedure does not work if\u00a0I try to perform the hack-attach to a more recent version of SQL Server. I tried hacking a 2005 log into a 2008 server and taking the tail-of-the-log backup\u00a0– which worked fine, but the tail-of-the-log backup could not be used in conjunction with the first set of backups from the 2005 server. The reason for this is that the database version number in the tail-of-the-log backup is 655 (SQL Server 2008) and those for the 2005 backups are 611 (SQL Server 2005). The database doesn’t get upgraded when restoring on the 2008 server until the end of the restore sequence – so as far as the 2008 server is concerned, my 2008 tail-of-the-log backup can’t be used in the restore sequence of a still-2005-really database. Hope that makes sense!<\/p>\n
        Summary<\/strong><\/p>\n
        Checking whether a tail-of-the-log backup is required is essential during a disaster recovery, and it’s not hard to do. As with any disaster recovery procedures, make sure you’ve practiced doing it in advance!<\/p>\n","protected":false},"excerpt":{"rendered":"
        (Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail […]<\/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],"tags":[],"class_list":["post-680","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-disaster-recovery","category-transaction-log"],"yoast_head":"\nDisaster recovery 101: backing up the tail of the log - 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\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Disaster recovery 101: backing up the tail of the log - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-06-18T09:21:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:54:42+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=\"6 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\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\",\"name\":\"Disaster recovery 101: backing up the tail of the log - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-06-18T09:21:00+00:00\",\"dateModified\":\"2017-04-13T16:54:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Disaster recovery 101: backing up the tail of the log\"}]},{\"@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":"Disaster recovery 101: backing up the tail of the log - 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\/disaster-recovery-101-backing-up-the-tail-of-the-log\/","og_locale":"en_US","og_type":"article","og_title":"Disaster recovery 101: backing up the tail of the log - Paul S. Randal","og_description":"(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques. We can also\u00a0help you\u00a0with disaster recovery.) One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/","og_site_name":"Paul S. Randal","article_published_time":"2010-06-18T09:21:00+00:00","article_modified_time":"2017-04-13T16:54:42+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/","name":"Disaster recovery 101: backing up the tail of the log - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-06-18T09:21:00+00:00","dateModified":"2017-04-13T16:54:42+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-backing-up-the-tail-of-the-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Disaster recovery 101: backing up the tail of the log"}]},{"@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\/680","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=680"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/680\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}