{"id":6363,"date":"2019-03-11T07:40:33","date_gmt":"2019-03-11T07:40:33","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6363"},"modified":"2019-11-28T07:02:10","modified_gmt":"2019-11-28T07:02:10","slug":"sql-server-backup-check","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-backup-check\/","title":{"rendered":"SQL Server Backup Check"},"content":{"rendered":"<p>Hi,<\/p>\n<p>To summarize the backup we can say, Copy of Stored Data to Disk or Tape In case of unexpected errors or disasters, this copy can be defined as Restoring Data and preventing data loss.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2793\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/backup.jpg\" alt=\"\" width=\"400\" height=\"243\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/backup.jpg 400w, https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/backup-300x182.jpg 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>In large-scale companies or in important projects, the loss of data causes material and reputation-causing events, so companies must always keep more than one copy of the Databases or Files for one or more.<\/p>\n<p>&nbsp;<\/p>\n<p>The causes of data loss that may occur in companies and systems are as follows.<\/p>\n<ul>\n<li>Media Failure<\/li>\n<li>User-Based Errors. Drop table<\/li>\n<li>Disk Errors. Disk corruption<\/li>\n<li>Natural disasters. Earthquake floods.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Backup should be checked everyday by SQL Server DBA with following script.<\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT DB.name AS Database_Name\r\n,MAX(DB.recovery_model_desc) AS Recovery_Model\r\n,MAX(BS.backup_start_date) AS Last_Backup\r\n,MAX(CASE WHEN BS.type = 'D'\r\nTHEN BS.backup_start_date END)\r\nAS Last_Full_backup\r\n,SUM(CASE WHEN BS.type = 'D'\r\nTHEN 1 END)\r\nAS Count_Full_backup\r\n,MAX(CASE WHEN BS.type = 'L'\r\nTHEN BS.backup_start_date END)\r\nAS Last_Log_backup\r\n,SUM(CASE WHEN BS.type = 'L'\r\nTHEN 1 END)\r\nAS Count_Log_backup\r\n,MAX(CASE WHEN BS.type = 'I'\r\nTHEN BS.backup_start_date END)\r\nAS Last_Differential_backup\r\n,SUM(CASE WHEN BS.type = 'I'\r\nTHEN 1 END)\r\nAS Count_Differential_backup\r\n,MAX(CASE WHEN BS.type = 'F'\r\nTHEN BS.backup_start_date END)\r\nAS LastFile\r\n,SUM(CASE WHEN BS.type = 'F'\r\nTHEN 1 END)\r\nAS CountFile\r\n,MAX(CASE WHEN BS.type = 'G'\r\nTHEN BS.backup_start_date END)\r\nAS LastFileDiff\r\n,SUM(CASE WHEN BS.type = 'G'\r\nTHEN 1 END)\r\nAS CountFileDiff\r\n,MAX(CASE WHEN BS.type = 'P'\r\nTHEN BS.backup_start_date END)\r\nAS LastPart\r\n,SUM(CASE WHEN BS.type = 'P'\r\nTHEN 1 END)\r\nAS CountPart\r\n,MAX(CASE WHEN BS.type = 'Q'\r\nTHEN BS.backup_start_date END)\r\nAS LastPartDiff\r\n,SUM(CASE WHEN BS.type = 'Q'\r\nTHEN 1 END)\r\nAS CountPartDiff\r\nFROM sys.databases AS DB\r\nLEFT JOIN\r\nmsdb.dbo.backupset AS BS\r\nON BS.database_name = DB.name\r\nWHERE ISNULL(BS.is_damaged, 0) = 0-- exclude damaged backups \r\nGROUP BY DB.name\r\nORDER BY Last_Backup desc;\r\n\r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, To summarize the backup we can say, Copy of Stored Data to Disk or Tape In case of unexpected errors or disasters, this copy can be defined as Restoring Data and preventing data loss.<\/p>\n","protected":false},"author":1,"featured_media":2793,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[1028,1031,1515,1577,1600,1610,2231,1620,2372,2374,1621,2373,2375,2230,2229],"class_list":["post-6363","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-salih-deveci","tag-sql-server","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-backup","tag-sql-server-backup-check","tag-sql-server-backup-monitor","tag-sql-server-backup-restore","tag-sql-server-backups","tag-sql-server-monitor-backup","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2012\/10\/backup.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6363"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6363\/revisions"}],"predecessor-version":[{"id":12960,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6363\/revisions\/12960"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/2793"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}