{"id":3352,"date":"2015-12-17T05:35:40","date_gmt":"2015-12-17T10:35:40","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3352"},"modified":"2023-10-17T04:53:51","modified_gmt":"2023-10-17T08:53:51","slug":"point-in-time-recovery","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/","title":{"rendered":"Point-in-time recovery"},"content":{"rendered":"

Point-in-time recovery allows to restore a\u00a0database into a state it was in any point of time. This type\u00a0of recovery is applicable\u00a0only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not possible.<\/p>\n

To understand how point-in-time recovery works let’s review the use case with a sample database (under the full recovery model) where a full backup is made every 24 hours and a differential database backup that is created\u00a0every six hours, and transaction log backups are created every hour. Please see the picture below:\"Point-in-time
\nAssume some crucial data were deleted at 13:30 and the last transaction log\u00a0backup was made at 14:00. The transaction log backup from 14:00 gives us the opportunity to restore the database to its state at 13:29:59. In this case, only 30 minutes of updates will be lost.<\/p>\n

Below we will consider two ways of restoring a database to a point-in-time:<\/p>\n

Using Transact-SQL<\/h2>\n

To recover a database to a point-in-time it is necessary to start from restoring the full database backup using the following syntax:<\/p>\n

RESTORE DATABASE<\/span>\u00a0your_database\u00a0<\/em>FROM DISK<\/span> = 'full_00_00.bak'<\/span> WITH NORECOVERY<\/span>, REPLACE<\/span><\/pre>\n
After the full backup is restored it is time to restore the last differential backup. It our\u00a0case – the last differential backup was made at 12:00. Differential backup can be recovered with the help of this syntax:<\/p>\n
RESTORE DATABASE<\/span> your_database<\/em> FROM DISK<\/span> = 'diff_12_00.bak'<\/span> WITH NORECOVERY<\/span><\/pre>\n
This differential backup\u00a0includes all changes that were made to the database since\u00a0the last full backup.<\/p>\n
And the last step is to apply all transaction log backups that were made after the differential backup. Please apply all transaction log backups in the same sequence in which they were created. It is crucial to remember to specify the time to which database should be restored with the help of STOPAT option when you restore the last log backup (in our\u00a0case the database should be restored as of 13:29:59). Please also note that all backups,\u00a0except the last one, are restored with NORECOVERY option, while the last one should be restored with RECOVERY option recovering the database into its working state:<\/p>\n
RESTORE<\/span> LOG<\/span> your_database<\/em> FROM DISK<\/span> = 'log_13_00.bak'<\/span> WITH NORECOVERY\u00a0<\/span>\r\nRESTORE<\/span> LOG<\/span> your_database<\/em> FROM DISK<\/span> = 'log_14_00.bak'<\/span> WITH STOPAT<\/span> = '2015-11-19 13:29:59.000', RECOVERY<\/span><\/span><\/pre>\n
Using SSMS (SQL Server Management Studio)<\/h2>\n
    \n
  1. Right click on the database that should be restored from the list, select “Tasks” – “Restore” – “Database…”\"Restore<\/a><\/li>\n
  2. In the window that appeared press “Timeline” button to set up the time for database restoration\"Restore<\/a><\/li>\n
  3. Check in “Specific date and time” to set up the time for database restoration (13:29:59), press “OK” to save changes, and press “OK” button in the previous\u00a0window:\"Restore<\/a><\/li>\n
  4. When the restoration process is completed, the following message will appear on the screen:\"Restore<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    Point-in-time recovery allows to restore a\u00a0database into a state it was in any point of time. This type\u00a0of recovery is applicable\u00a0only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not […]<\/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":"\nPoint-in-time recovery - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Point-in-time recovery\" \/>\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\/point-in-time-recovery\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Point-in-time recovery - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Point-in-time recovery\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-17T10:35:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T08:53:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Point-in-time-recovery-1-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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/\",\"url\":\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/\",\"name\":\"Point-in-time recovery - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-17T10:35:40+00:00\",\"dateModified\":\"2023-10-17T08:53:51+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Point-in-time recovery\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Point-in-time recovery\"}]},{\"@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":"Point-in-time recovery - Sql Server Backup Academy","description":"Learn about SQL Server Point-in-time recovery","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\/point-in-time-recovery\/","og_locale":"en_US","og_type":"article","og_title":"Point-in-time recovery - Sql Server Backup Academy","og_description":"Learn about SQL Server Point-in-time recovery","og_url":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-17T10:35:40+00:00","article_modified_time":"2023-10-17T08:53:51+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Point-in-time-recovery-1-648x320.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/","url":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/","name":"Point-in-time recovery - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-17T10:35:40+00:00","dateModified":"2023-10-17T08:53:51+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Point-in-time recovery","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/point-in-time-recovery\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/point-in-time-recovery\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Point-in-time recovery"}]},{"@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\/3352"}],"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=3352"}],"version-history":[{"count":26,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3352\/revisions"}],"predecessor-version":[{"id":3846,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3352\/revisions\/3846"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}