{"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":"
\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":"
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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pgc_sgb_lightbox_settings":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[24],"tags":[],"class_list":["post-473","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server"],"yoast_head":"\nCheck 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('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 … Continue reading →\" \/>\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\":\"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\",\"author\":{\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\"},\"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\",\"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\",\"@id\":\"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"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 … Continue reading →","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":"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","author":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"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","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","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","sameAs":["http:\/\/www.dbafox.com"]}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3ecMb-7D","_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}]}}