{"id":473,"date":"2015-09-07T04:44:23","date_gmt":"2015-09-07T04:44:23","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=473"},"modified":"2016-11-24T09:40:09","modified_gmt":"2016-11-24T09:40:09","slug":"check-sql-server-backups","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=473","title":{"rendered":"Check SQL Server Backups"},"content":{"rendered":"<pre>\r\nSELECT \r\nCONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \r\nmsdb.dbo.backupset.database_name, \r\nmsdb.dbo.backupset.backup_start_date, \r\nmsdb.dbo.backupset.backup_finish_date, \r\nmsdb.dbo.backupset.expiration_date, \r\nCASE msdb..backupset.type \r\nWHEN 'D' THEN 'Database' \r\nWHEN 'L' THEN 'Log' \r\nWHEN 'I' THEN 'Differential Database' \r\nWHEN 'F' THEN 'File or filegroup'\r\nWHEN 'G' THEN 'Differential file' \r\nWHEN 'P' THEN 'Partial'\r\nWHEN 'Q' THEN 'Differential Partial'\r\nELSE msdb..backupset.type \r\nEND AS backup_type, \r\nmsdb.dbo.backupset.backup_size, \r\nmsdb.dbo.backupmediafamily.logical_device_name, \r\nmsdb.dbo.backupmediafamily.physical_device_name, \r\nmsdb.dbo.backupset.name AS backupset_name, \r\nmsdb.dbo.backupset.description \r\nFROM msdb.dbo.backupmediafamily \r\nINNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id \r\nWHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 2) \r\nORDER BY \r\nmsdb.dbo.backupset.database_name, \r\nmsdb.dbo.backupset.backup_finish_date desc\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SELECT CONVERT(CHAR(100), SERVERPROPERTY(&#8216;Servername&#8217;)) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN &#8216;D&#8217; THEN &#8216;Database&#8217; WHEN &#8216;L&#8217; THEN &#8216;Log&#8217; WHEN &#8216;I&#8217; THEN &#8216;Differential Database&#8217; WHEN &#8216;F&#8217; THEN &#8216;File or filegroup&#8217; WHEN &#8216;G&#8217; THEN &#8216;Differential file&#8217; WHEN &#8216;P&#8217; THEN &#8216;Partial&#8217; WHEN &#8216;Q&#8217; THEN &#8216;Differential Partial&#8217; ELSE msdb..backupset.type END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name,&hellip;&nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[24],"tags":[],"class_list":["post-473","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Check SQL Server Backups - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=473\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Check SQL Server Backups - dbafox\" \/>\n<meta property=\"og:description\" content=\"SELECT CONVERT(CHAR(100), SERVERPROPERTY(&#039;Servername&#039;)) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN &#039;D&#039; THEN &#039;Database&#039; WHEN &#039;L&#039; THEN &#039;Log&#039; WHEN &#039;I&#039; THEN &#039;Differential Database&#039; WHEN &#039;F&#039; THEN &#039;File or filegroup&#039; WHEN &#039;G&#039; THEN &#039;Differential file&#039; WHEN &#039;P&#039; THEN &#039;Partial&#039; WHEN &#039;Q&#039; THEN &#039;Differential Partial&#039; ELSE msdb..backupset.type END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name,&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=473\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2015-09-07T04:44:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-11-24T09:40:09+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473\"},\"author\":{\"name\":\"Ray Fox\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"headline\":\"Check SQL Server Backups\",\"datePublished\":\"2015-09-07T04:44:23+00:00\",\"dateModified\":\"2016-11-24T09:40:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473\"},\"wordCount\":4,\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"articleSection\":[\"MS SQL Server\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473\",\"url\":\"https:\\\/\\\/dbafox.com\\\/?p=473\",\"name\":\"Check SQL Server Backups - dbafox\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\"},\"datePublished\":\"2015-09-07T04:44:23+00:00\",\"dateModified\":\"2016-11-24T09:40:09+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=473\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=473#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dbafox.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Check SQL Server Backups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\",\"url\":\"https:\\\/\\\/dbafox.com\\\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dbafox.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"logo\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"sameAs\":[\"http:\\\/\\\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Check SQL Server Backups - dbafox","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:\/\/dbafox.com\/?p=473","og_locale":"en_GB","og_type":"article","og_title":"Check SQL Server Backups - dbafox","og_description":"SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Differential Database' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Differential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential Partial' ELSE msdb..backupset.type END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name,&hellip;&nbsp;","og_url":"https:\/\/dbafox.com\/?p=473","og_site_name":"dbafox","article_published_time":"2015-09-07T04:44:23+00:00","article_modified_time":"2016-11-24T09:40:09+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbafox.com\/?p=473#article","isPartOf":{"@id":"https:\/\/dbafox.com\/?p=473"},"author":{"name":"Ray Fox","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"headline":"Check SQL Server Backups","datePublished":"2015-09-07T04:44:23+00:00","dateModified":"2016-11-24T09:40:09+00:00","mainEntityOfPage":{"@id":"https:\/\/dbafox.com\/?p=473"},"wordCount":4,"publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"articleSection":["MS SQL Server"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=473","url":"https:\/\/dbafox.com\/?p=473","name":"Check SQL Server Backups - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2015-09-07T04:44:23+00:00","dateModified":"2016-11-24T09:40:09+00:00","breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=473#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=473"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=473#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"Check SQL Server Backups"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","logo":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.dbafox.com"]}]}},"_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/473","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=473"}],"version-history":[{"count":2,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/473\/revisions"}],"predecessor-version":[{"id":770,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/473\/revisions\/770"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=473"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=473"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=473"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}