{"id":352,"date":"2012-11-05T06:00:25","date_gmt":"2012-11-05T13:00:25","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=352"},"modified":"2013-10-22T17:10:19","modified_gmt":"2013-10-23T00:10:19","slug":"last-restore-date-and-time-sql-server-query","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/11\/last-restore-date-and-time-sql-server-query\/","title":{"rendered":"Last Restore Date And Time &#8211; SQL Server Query"},"content":{"rendered":"<p>\"How old is this data?\"<\/p>\n<p>\"When was this database restored?\"<\/p>\n<p>\"How old is this backup?\"<\/p>\n<p>These are common question in development environments.\u00a0 A fast little t-sql query can tell you the following:<\/p>\n<ol>\n<li>The date and time of the last restore.<\/li>\n<li>The date and time the backup which was restored was originally taken.<\/li>\n<li>The user who took the backup.<\/li>\n<li>The user who restored the backup.<\/li>\n<li>The name of the backup.<\/li>\n<li>The machine or SQL instance the backup was taken from.<\/li>\n<\/ol>\n<p>Use this information to impress your DBAs (and to impress upon your team that it's time to refresh your data!)<!--more--><\/p>\n<pre>WITH\u00a0\u00a0\u00a0 restore_date_cte\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT\u00a0\u00a0 d.name AS DatabaseName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rh.restore_date AS BackUpRestoredDatetime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , ISNULL(rh.user_name, 'No Restore') AS RestoredBy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , bs.name AS BackUpName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , bs.user_name AS BackupCreatedBy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , bs.backup_finish_date AS backupCompletedDatetime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , bs.database_name AS BackupSourceDB\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , bs.server_name AS BackupSourceSQLInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , ROW_NUMBER() OVER --get the most recent\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( PARTITION BY d.name ORDER BY rh.restore_date DESC ) AS RestoreOrder\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0 sys.databases AS d\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT JOIN msdb.dbo.restorehistory AS rh\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON d.name = rh.destination_database_name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT JOIN msdb.dbo.BackupSet AS bs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON rh.backup_set_id = bs.backup_set_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 rdc.DatabaseName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.BackUpRestoredDatetime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.RestoredBy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.BackUpName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.BackupCreatedBy\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.backupCompletedDatetime\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.BackupSourceDB\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.BackupSourceSQLInstance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , rdc.RestoreOrder\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 restore_date_cte AS rdc\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 RestoreOrder = 1\r\n\u00a0\u00a0\u00a0 ORDER BY rdc.DatabaseName<\/pre>\n<p><em>Note: Removing the \"WHERE RestoreOrder = 1\" will give you the entire restore history from the restoreHistory table.<br \/>\n<\/em><\/p>\n<p>Another note: There was an older version of this script in this post previously. I believe the script above is much easier to understand and modify than the old version. If you prefer more complicated code the old version is included below for posterity's sake.<\/p>\n<pre>USE MSDB\r\nGO\r\n\r\nSELECT TOP 1 WITH TIES\r\n        d.name\r\n      , rh.restore_date AS BackUpRestoredDatetime\r\n      , ISNULL(rh.user_name, 'No Restore') AS RestoredBy\r\n      , bs.name AS BackUpName\r\n      , bs.user_name AS BackupCreatedBy\r\n      , bs.backup_finish_date AS backupCompletedDatetime\r\n      , bs.database_name AS BackupSourceDB\r\n      , bs.server_name AS BackupSourceSQLInstance\r\nFROM    sys.databases AS d\r\n        LEFT JOIN dbo.restorehistory AS rh\r\n            ON d.name = rh.destination_database_name\r\n        LEFT JOIN BackupSet AS bs\r\n            ON rh.backup_set_id = bs.backup_set_id\r\nORDER BY RANK() OVER --get the most recent\r\n        ( PARTITION BY d.name ORDER BY rh.restore_date DESC )<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\"How old is this data?\" \"When was this database restored?\" \"How old is this backup?\" These are common question in development environments.\u00a0 A fast little t-sql query can tell you the following: The date and time of the last restore. The date and time the backup which was restored was originally taken. The user who [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[86,43,3,57],"tags":[87,10,89,88,6,9,12,11,173],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-dba","category-microsoft-sql-server","category-scripts","category-t-sql","tag-backup","tag-ms-sql","tag-refresh","tag-restore","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":11,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":626,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/352\/revisions\/626"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}