{"id":2867,"date":"2015-12-02T07:23:47","date_gmt":"2015-12-02T12:23:47","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2867"},"modified":"2023-10-17T05:23:54","modified_gmt":"2023-10-17T09:23:54","slug":"log-sequence-number","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/log-sequence-number\/","title":{"rendered":"Log Sequence Number"},"content":{"rendered":"

Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occur after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN<\/em> and LastLSN<\/em> values. To find out all backup’s LSN values\u00a0you need to look at its header using the following syntax:<\/p>\n

RESTORE HEADERONLY FROM DISK<\/span> = 'full.bak'\u00a0<\/span><\/pre>\n
<\/p>\n
To understand what these LSNs mean in\u00a0each database backup let’s consider a\u00a0backup sequence created (the database uses full or bulk-logged recovery model) using the following commands:<\/p>\n
BACKUP DATABASE<\/span> your_database<\/em> TO DISK<\/span> = 'full.bak'<\/span>\r\nBACKUP<\/span> LOG<\/span> your_database<\/em> TO DISK<\/span> = 'log.bak'<\/span>\r\nBACKUP<\/span> LOG<\/span> your_database<\/em> TO DISK <\/span>= 'log1.bak'<\/span>\r\nBACKUP DATABASE<\/span> your_database<\/em> TO DISK<\/span> = 'diff.bak'<\/span> WITH DIFFERENTIAL\r\nBACKUP<\/span> LOG<\/span> your_database<\/em> TO DISK<\/span> = 'log2.bak'<\/span>\r\nBACKUP <\/span>LOG<\/span> your_database<\/em> TO DISK <\/span>= 'log3.bak'<\/span>\r\nBACKUP DATABASE<\/span> your_database<\/em> TO DISK<\/span> = 'diff1.bak'<\/span> WITH DIFFERENTIAL\r\nBACKUP DATABASE <\/span>your_database<\/em> TO DISK<\/span> = 'full1.bak'<\/span>\r\nBACKUP<\/span> LOG<\/span> your_database<\/em> TO DISK<\/span> = 'log4.bak'<\/span><\/pre>\n
This shows that\u00a0we have created three different types of database backups: Full backup, Differential backup and Transaction Log backup. Now let’s check their\u00a0LSNs and compare them:<\/p>\n
RESTORE HEADERONLY FROM DISK<\/span> = 'full.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'log.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'log1.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'diff.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'log2.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'log3.bak'\r\n<\/span>RESTORE HEADERONLY FROM DISK<\/span> = 'diff1.bak'<\/span> \r\nRESTORE HEADERONLY FROM DISK<\/span> = 'full1.bak'\r\n<\/span>RESTORE HEADERONLY FROM DISK<\/span> = 'log4.bak'\u00a0<\/span><\/pre>\n
These commands produce the following results:
\n\"LogSequenceNumber\"<\/span><\/p>\n
where:<\/p>\n
    \n
  • FirstLSN<\/em> – Log sequence number\u00a0of the first transaction in the backup set<\/li>\n
  • LastLSN<\/em> – Log sequence number of\u00a0the next log record after the backup set<\/li>\n
  • CheckpointLSN<\/em> – Log sequence number of the last\u00a0checkpoint<\/li>\n
  • DatabaseBackupLSN<\/em> –\u00a0Log sequence number of the last\u00a0full database backup<\/li>\n<\/ul>\n
    How do\u00a0FirstLSN, LastLSN,\u00a0CheckpointLSN and DatabaseBackupLSN Relate to Each Other<\/h2>\n
    The DatabaseBackupLSN <\/em>of the first full backup is always equal to zero:\"Full(DatabaseBackupLSN)\"<\/p>\n
    The FirstLSN<\/em> of the first full backup is the same as the CheckpointLSN\"Full<\/em><\/p>\n
    Full database backup is the main type on which all other backups (especially differential backups) are dependent. In the restore sequence, a differential backup is always restored after the full backup. In order to determine the full backup to which the differential backup relates, you need to check the values of DatabaseBackupLSN<\/em> of differential backup and CheckpointLSN<\/em> of the full backup – their values should be identical.:\u00a0\"Full<\/p>\n
    \"Full<\/p>\n
    But what if there is no differential backup, and there is a need to restore the database with the full and transaction log backups? To find out which transaction log backup should be restored right\u00a0after the\u00a0full backup is restored\u00a0take a look at the LastLSN<\/em> of the full backup and the FirstLSN<\/em> and the LastLSN<\/em> of the transaction log backup. The LastLSN<\/em> of the full backup should be between the FirstLSN<\/em> and the LastLSN<\/em> of the transaction log backup.\u00a0\"Full<\/p>\n
    Likewise, in order to find out which transaction log backup should\u00a0be restored after the differential backup you need to pay attention to the LastLSN<\/em> of the differential backup and the FirstLSN<\/em> and the LastLSN<\/em> of the transaction log backup. The LastLSN<\/em> of the differential backup should be between FirstLSN<\/em> and LastLSN<\/em> of the transaction log backup followed right after it:\u00a0\"DifferentialThe CheckpointLSN<\/em> of the differential backup maps to the CheckpointLSN<\/em> of the first transaction log backup created after this differential backup:<\/p>\n
    \"Differential<\/p>\n
    First transaction log backup has the same FirstLSN<\/em> and CheckpointLSN<\/em> as\u00a0the first full database backup’s CheckpointLSN<\/em>:\"Full
    \nFor a transaction log backup, the LastLSN<\/em> value is the same as the FirstLSN<\/em> of the next transaction log backup:\"Transaction
    \n\"Transaction<\/p>\n
    Note that LastLSN<\/em> value of the previous transaction log backup is equal to LSN of\u00a0the first log record stored in the following transaction log backup, as shown on\u00a0the picture above.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occur after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN and LastLSN […]<\/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":"\nLog Sequence Number - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Log Sequence Number\" \/>\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\/log-sequence-number\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Log Sequence Number - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Log Sequence Number\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/log-sequence-number\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-02T12:23:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:23:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/LSN.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/log-sequence-number\/\",\"url\":\"https:\/\/academy.sqlbak.com\/log-sequence-number\/\",\"name\":\"Log Sequence Number - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-02T12:23:47+00:00\",\"dateModified\":\"2023-10-17T09:23:54+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Log Sequence Number\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/log-sequence-number\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/log-sequence-number\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/log-sequence-number\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Log Sequence Number\"}]},{\"@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":"Log Sequence Number - Sql Server Backup Academy","description":"Learn about SQL Server Log Sequence Number","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\/log-sequence-number\/","og_locale":"en_US","og_type":"article","og_title":"Log Sequence Number - Sql Server Backup Academy","og_description":"Learn about SQL Server Log Sequence Number","og_url":"https:\/\/academy.sqlbak.com\/log-sequence-number\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-02T12:23:47+00:00","article_modified_time":"2023-10-17T09:23:54+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/LSN.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/log-sequence-number\/","url":"https:\/\/academy.sqlbak.com\/log-sequence-number\/","name":"Log Sequence Number - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-02T12:23:47+00:00","dateModified":"2023-10-17T09:23:54+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Log Sequence Number","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/log-sequence-number\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/log-sequence-number\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/log-sequence-number\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Log Sequence Number"}]},{"@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\/2867"}],"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=2867"}],"version-history":[{"count":37,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2867\/revisions"}],"predecessor-version":[{"id":3868,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2867\/revisions\/3868"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2867"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2867"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2867"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}