{"id":376,"date":"2020-01-13T10:48:54","date_gmt":"2020-01-13T00:48:54","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=376"},"modified":"2020-01-13T10:56:57","modified_gmt":"2020-01-13T00:56:57","slug":"how-to-check-mssql-backups","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2020\/01\/13\/how-to-check-mssql-backups\/","title":{"rendered":"How to check MSSQL Backups"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>SELECT \nCONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \nmsdb.dbo.backupset.database_name, \nmsdb.dbo.backupset.backup_start_date, \nmsdb.dbo.backupset.backup_finish_date, \nmsdb.dbo.backupset.expiration_date, \nCASE msdb..backupset.type \nWHEN 'D' THEN 'Database' \nWHEN 'L' THEN 'Log' \nEND AS backup_type, \nmsdb.dbo.backupset.backup_size, \nmsdb.dbo.backupmediafamily.logical_device_name, \nmsdb.dbo.backupmediafamily.physical_device_name, \nmsdb.dbo.backupset.name AS backupset_name, \nmsdb.dbo.backupset.description \nFROM msdb.dbo.backupmediafamily \nINNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id \nWHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) \nORDER BY \nmsdb.dbo.backupset.database_name, \nmsdb.dbo.backupset.backup_finish_date <\/code><\/pre>\n\n\n\n<p>Most recent backup for each database<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT  \n   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \n   msdb.dbo.backupset.database_name,  \n   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date \nFROM   msdb.dbo.backupmediafamily  \n   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  \nWHERE  msdb..backupset.type = 'D' \nGROUP BY \n   msdb.dbo.backupset.database_name  \nORDER BY  \n   msdb.dbo.backupset.database_name <\/code><\/pre>\n\n\n\n<p>Detailed list of backups<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT  \n   A.&#91;Server],  \n   A.last_db_backup_date,  \n   B.backup_start_date,  \n   B.expiration_date, \n   B.backup_size,  \n   B.logical_device_name,  \n   B.physical_device_name,   \n   B.backupset_name, \n   B.description \nFROM \n   ( \n   SELECT   \n       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \n       msdb.dbo.backupset.database_name,  \n       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date \n   FROM    msdb.dbo.backupmediafamily  \n       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  \n   WHERE   msdb..backupset.type = 'D' \n   GROUP BY \n       msdb.dbo.backupset.database_name  \n   ) AS A \n   LEFT JOIN  \n   ( \n   SELECT   \n   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \n   msdb.dbo.backupset.database_name,  \n   msdb.dbo.backupset.backup_start_date,  \n   msdb.dbo.backupset.backup_finish_date, \n   msdb.dbo.backupset.expiration_date, \n   msdb.dbo.backupset.backup_size,  \n   msdb.dbo.backupmediafamily.logical_device_name,  \n   msdb.dbo.backupmediafamily.physical_device_name,   \n   msdb.dbo.backupset.name AS backupset_name, \n   msdb.dbo.backupset.description \nFROM   msdb.dbo.backupmediafamily  \n   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  \nWHERE  msdb..backupset.type = 'D' \n   ) AS B \n   ON A.&#91;server] = B.&#91;server] AND A.&#91;database_name] = B.&#91;database_name] AND A.&#91;last_db_backup_date] = B.&#91;backup_finish_date] \nORDER BY  \n   A.database_name <\/code><\/pre>\n\n\n\n<p>Databases Missing a Data Back-Up Within Past 24 Hours<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \n   msdb.dbo.backupset.database_name, \n   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, \n   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS &#91;Backup Age (Hours)] \nFROM    msdb.dbo.backupset \nWHERE     msdb.dbo.backupset.type = 'D'  \nGROUP BY msdb.dbo.backupset.database_name \nHAVING      (MAX(msdb.dbo.backupset.backup_finish_date) &lt; DATEADD(hh, - 24, GETDATE()))  \nUNION  \n--Databases without any backup history \nSELECT      \n   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  \n   master.dbo.sysdatabases.NAME AS database_name,  \n   NULL AS &#91;Last Data Backup Date],  \n   9999 AS &#91;Backup Age (Hours)]  \nFROM \n   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset \n       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name \nWHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name &lt;> 'tempdb' \nORDER BY  \n   msdb.dbo.backupset.database_name <\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Most recent backup for each database Detailed list of backups Databases Missing a Data Back-Up Within Past 24 Hours<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[50],"class_list":["post-376","post","type-post","status-publish","format-standard","hentry","category-ms-sql","tag-backup"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/376","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=376"}],"version-history":[{"count":3,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/376\/revisions"}],"predecessor-version":[{"id":379,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/376\/revisions\/379"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=376"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}