{"id":3229,"date":"2015-12-08T02:59:16","date_gmt":"2015-12-08T07:59:16","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3229"},"modified":"2023-10-17T05:20:50","modified_gmt":"2023-10-17T09:20:50","slug":"backup-chain","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/backup-chain\/","title":{"rendered":"Backup chain"},"content":{"rendered":"
A backup chain is a specific sequence in which all database backups should be restored. If a backup chain is broken, the restore process will be not possible, and some data will be lost. Let us consider the following example with all three backup types to understand the backup chain better.<\/div>\n

<\/p>\n

Example of Database Recovery<\/h2>\n
Many know that in order to minimize the risk of data loss, it is advisable to use the full recovery model, which allows making transaction log backups. In their turn, transaction log backups enable database restoration to a particular point-in-time. Let’s consider the following sequence of database backups that form a database backup chain. It is assumed that the full database backup is made every 24 hours at 00:00, differential – every 6 hours, and transaction log backup is created every hour. \u00a0\"Backup<\/div>\n

Full Backup<\/h3>\n
Suppose some crucial data were deleted at 13:30. The best way out of this situation is to restore the database to the statement at 13:29. You should begin the restore process of this database from the last full database backup\u00a0restore operation:<\/div>\n
RESTORE DATABASE<\/span>\u00a0your_database\u00a0<\/em>FROM DISK<\/span> = 'full_00_00.bak'<\/span> WITH NORECOVERY<\/span>, REPLACE<\/span><\/pre>\n
\n
Differential Backup<\/h3>\n<\/div>\n
After the full backup was restored the next necessary step would be restoring the last differential backup. If you are not sure which differential database backups belong to the restored full backup, you can first restore the headers of backups. Please use the following\u00a0syntax to do it:<\/div>\n
RESTORE HEADERONLY FROM DISK<\/span> = 'full_00_00.bak'<\/span>\r\nRESTORE HEADERONLY FROM DISK<\/span> = 'diff_06_00.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'diff_12_00.bak' \u00a0<\/span><\/pre>\n
The following result will be received:<\/div>\n
<\/div>\n
\"Full&Diff\"<\/div>\n
<\/div>\n
In this scenario you need to pay attention to the full backup CheckpointLSN value and compare it with Differential backups DatabaseBackupLSN value – they should be identical (marked as blue square). It means\u00a0that these two differential backups belong to this full backup and can be restored after it. To find out what differential backup was made earlier, compare\u00a0their FirstLSN numbers (marked as orange square). The last differential backup will have bigger value.<\/div>\n
<\/div>\n
If you try to restore a differential backup before restoring its full\u00a0backup, you will get the following error message:<\/div>\n
Msg 3136, Level 16, State 3, Line 1<\/span>\r\nThis differential backup cannot be restored because the database has not been restored to the correct earlier state.<\/span>\r\nMsg 3013, Level 16, State 1, Line 1<\/span>\r\nRESTORE DATABASE is terminating abnormally.<\/span><\/pre>\n
\n
That means that the backup chain\u00a0was broken and any further recovery process is not possible. Therefore, pay attention to what differential backup should be restored after the full backup. In our example it is necessary to restore a differential database backup from 12:00 using the following command:<\/p>\n
RESTORE DATABASE<\/span>\u00a0your_database\u00a0<\/em>FROM DISK<\/span> = 'diff_12_00.bak'<\/span> WITH NORECOVERY<\/span><\/pre>\n<\/div>\n
\n
Transaction Log Backups<\/h3>\n<\/div>\n
Now that we’ve restored the full and differential backups, let’s\u00a0restore the database to its state as of 14:29. This is where we will require the transaction log backups. In this case it is also necessary to follow a certain sequence to avoid breaking transaction log backup chain.<\/div>\n
If you need to determine what transaction log backup follows after another, you need to get backup’s headers\u00a0(same as we did with differential backups):<\/div>\n
\"T.Log<\/div>\n
<\/div>\n
This picture shows a transaction log backup chain, each backup from the chain has its own FirstLSN\u00a0and LastLSN. Every LastLSN value of the transaction log must be the same as FirstLSN values of the next transaction log backup. These values help to restore transaction log backup files in the right sequence. To find out what transaction log backup should\u00a0be restored after the differential backup, compare their DatabaseBackupLSN values – they should be identical:<\/div>\n
<\/div>\n
\"T.Log-and-Diff\"<\/div>\n
<\/div>\n
To understand which transaction log backup should be restored first after differential backup, please check LastLSN value of the differential backup. This value should be somewhere between the FirstLSN and the LastLSN values of the transaction log backup. Otherwise, if the wrong transaction log backup is\u00a0applied to the differential backup, you’ll get\u00a0the following error:<\/div>\n
Msg 4305, Level 16, State 1, Line 17<\/span>\r\nThe log in this backup set begins at LSN 74000000071200001, which is too recent to apply to the database. An earlier log backup that includes LSN 74000000065600001 can be restored.<\/span>\r\nMsg 3013, Level 16, State 1, Line 17<\/span>\r\nRESTORE LOG is terminating abnormally.<\/span><\/pre>\n
To restore transaction log use the following syntax:<\/div>\n
RESTORE <\/span>LOG<\/span> your_database<\/em> FROM DISK<\/span> = 'log_13_00.bak'<\/span> WITH NORECOVERY\u00a0<\/span><\/pre>\n
Then to restore the database up to the state at 13:29:59 run the foloowing command for transaction log from 14:00 using “WITH STOPAT” option:<\/div>\n
\n
RESTORE<\/span> LOG<\/span> your_database<\/em> FROM DISK<\/span> = 'log_14_00.bak' <\/span>WITH STOPAT<\/span> = '2015-11-19 13:29:59.000'<\/span>, RECOVERY<\/span><\/pre>\n<\/div>\n
<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
A backup chain is a specific sequence in which all database backups should be restored. If a backup chain is broken, the restore process will be not possible, and some data will be lost. Let us consider the following example with all three backup types to understand the backup chain better.<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[157],"tags":[],"yoast_head":"\nBackup chain - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Backup chain\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/academy.sqlbak.com\/backup-chain\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Backup chain - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Backup chain\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/backup-chain\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-08T07:59:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:20:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Backup-sequence-648x320.png\" \/>\n<meta name=\"author\" content=\"Alexandr Omelchenko\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandr Omelchenko\" \/>\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:\/\/academy.sqlbak.com\/backup-chain\/\",\"url\":\"https:\/\/academy.sqlbak.com\/backup-chain\/\",\"name\":\"Backup chain - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-08T07:59:16+00:00\",\"dateModified\":\"2023-10-17T09:20:50+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Backup chain\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/backup-chain\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/backup-chain\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/backup-chain\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Backup chain\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/academy.sqlbak.com\/#website\",\"url\":\"https:\/\/academy.sqlbak.com\/\",\"name\":\"Sql Server Backup Academy\",\"description\":\"All you need to know about Sql Server database backup\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/academy.sqlbak.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\",\"name\":\"Alexandr Omelchenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"caption\":\"Alexandr Omelchenko\"},\"sameAs\":[\"http:\/\/sqlbak.com\"],\"url\":\"https:\/\/academy.sqlbak.com\/author\/alexandr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Backup chain - Sql Server Backup Academy","description":"Learn about SQL Server Backup chain","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:\/\/academy.sqlbak.com\/backup-chain\/","og_locale":"en_US","og_type":"article","og_title":"Backup chain - Sql Server Backup Academy","og_description":"Learn about SQL Server Backup chain","og_url":"https:\/\/academy.sqlbak.com\/backup-chain\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-08T07:59:16+00:00","article_modified_time":"2023-10-17T09:20:50+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Backup-sequence-648x320.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/backup-chain\/","url":"https:\/\/academy.sqlbak.com\/backup-chain\/","name":"Backup chain - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-08T07:59:16+00:00","dateModified":"2023-10-17T09:20:50+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Backup chain","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/backup-chain\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/backup-chain\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/backup-chain\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Backup chain"}]},{"@type":"WebSite","@id":"https:\/\/academy.sqlbak.com\/#website","url":"https:\/\/academy.sqlbak.com\/","name":"Sql Server Backup Academy","description":"All you need to know about Sql Server database backup","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/academy.sqlbak.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8","name":"Alexandr Omelchenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","caption":"Alexandr Omelchenko"},"sameAs":["http:\/\/sqlbak.com"],"url":"https:\/\/academy.sqlbak.com\/author\/alexandr\/"}]}},"_links":{"self":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3229"}],"collection":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/comments?post=3229"}],"version-history":[{"count":42,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3229\/revisions"}],"predecessor-version":[{"id":3866,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3229\/revisions\/3866"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}