{"id":135,"date":"2015-01-20T09:54:57","date_gmt":"2015-01-20T09:54:57","guid":{"rendered":"http:\/\/www.exploreax.com\/blog\/?p=135"},"modified":"2015-01-20T10:11:35","modified_gmt":"2015-01-20T10:11:35","slug":"sql-database-restore-history","status":"publish","type":"post","link":"http:\/\/www.exploreax.com\/blog\/blog\/2015\/01\/20\/sql-database-restore-history\/","title":{"rendered":"SQL Database Restore History"},"content":{"rendered":"<p>From time to time it is useful to determine the history of restores that a database has gone through. For example if you maintain a local copy of a client database \/ environment and would like to determine the exact date it was last refreshed. The following script is useful for getting this restore history from SQL:<\/p>\n<p>SELECT [rs].[destination_database_name],<br \/>\n[rs].[restore_date],<br \/>\n[bs].[backup_start_date],<br \/>\n[bs].[backup_finish_date],<br \/>\n[bs].[database_name] as [source_database_name],<br \/>\n[bmf].[physical_device_name] as [backup_file_used_for_restore]<br \/>\nFROM msdb..restorehistory rs<br \/>\nINNER JOIN msdb..backupset bs<br \/>\nON [rs].[backup_set_id] = [bs].[backup_set_id]<br \/>\nINNER JOIN msdb..backupmediafamily bmf<br \/>\nON [bs].[media_set_id] = [bmf].[media_set_id]<br \/>\nORDER BY [rs].[restore_date] DESC<\/p>\n<p>Sample Output<\/p>\n<p><a href=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/01\/Screen-Shot-2015-01-20-at-12.03.02-PM.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-142\" src=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/01\/Screen-Shot-2015-01-20-at-12.03.02-PM.png\" alt=\"Screen Shot 2015-01-20 at 12.03.02 PM\" width=\"1152\" height=\"462\" srcset=\"http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/01\/Screen-Shot-2015-01-20-at-12.03.02-PM.png 1152w, http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/01\/Screen-Shot-2015-01-20-at-12.03.02-PM-300x120.png 300w, http:\/\/www.exploreax.com\/blog\/wp-content\/uploads\/2015\/01\/Screen-Shot-2015-01-20-at-12.03.02-PM-1024x410.png 1024w\" sizes=\"(max-width: 1152px) 100vw, 1152px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>From time to time it is useful to determine the history of restores that a database has gone through. For example if you maintain a local copy of a client database \/ environment and would like to determine the exact date it was last refreshed. The following script is useful for getting this restore history&hellip;<a href=\"http:\/\/www.exploreax.com\/blog\/blog\/2015\/01\/20\/sql-database-restore-history\/\">Read more <span class=\"screen-reader-text\">SQL Database Restore History<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2,45,1],"tags":[109,46,48],"_links":{"self":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/135"}],"collection":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/comments?post=135"}],"version-history":[{"count":4,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/135\/revisions"}],"predecessor-version":[{"id":143,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/posts\/135\/revisions\/143"}],"wp:attachment":[{"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/media?parent=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/categories?post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.exploreax.com\/blog\/wp-json\/wp\/v2\/tags?post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}