{"id":110,"date":"2012-11-12T19:48:38","date_gmt":"2012-11-13T01:48:38","guid":{"rendered":"http:\/\/s1095387061.online-home.ca\/?p=110"},"modified":"2013-01-09T21:16:10","modified_gmt":"2013-01-10T03:16:10","slug":"compression-enable-checksum","status":"publish","type":"post","link":"https:\/\/databaserefresh.com\/blog\/compression-enable-checksum\/","title":{"rendered":"Does compression enable checksum for the backups automatically?"},"content":{"rendered":"<p>I am not sure if this is a bug or an issue with BOL documentation, but BOL says here <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms186865.aspx\">http:\/\/msdn.microsoft.com\/en-us\/library\/ms186865.aspx<\/a>: &#8220;(CHECKSUM) Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup. This is the default behavior for a compressed backup&#8221;.<\/p>\n<p>So, let\u2019s run some tests.<\/p>\n<p>&nbsp;<\/p>\n<p>Create three backups: backup a database without compression, then backup a database with compression and finally backup it with CHECKSUM:<\/p>\n<p style=\"padding-left: 30px;\">\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">BACKUP<\/span> <span style=\"color: #0000ff;\">DATABASE<\/span> msdb <span style=\"color: #0000ff;\">TO DISK<\/span> = <span style=\"color: #ff0000;\">&#8216;K:\\BACKUPS\\msdb_compressed.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">WITH COMPRESSION, COPY_ONLY<\/span><\/p>\n<p style=\"padding-left: 30px;\">\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">BACKUP DATABASE<\/span> msdb <span style=\"color: #0000ff;\">TO DISK<\/span> = <span style=\"color: #ff0000;\">&#8216;K:\\BACKUPS\\msdb_NOT_compressed.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">WITH  COPY_ONLY<\/span><\/p>\n<p style=\"padding-left: 30px;\">\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">BACKUP DATABASE<\/span> msdb <span style=\"color: #0000ff;\">TO DISK<\/span> =<span style=\"color: #ff0000;\"> &#8216;K:\\BACKUPS\\msdb_CHECKSUM.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">WITH  <span style=\"color: #ff00ff;\">CHECKSUM<\/span>, COPY_ONLY<\/span><\/p>\n<p style=\"padding-left: 30px;\">\n<p>Run RESTORE HEADERONLY for the backups created without CHECKSUM:<\/p>\n<p style=\"padding-left: 30px;\">\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">RESTORE HEADERONLY FROM DISK<\/span> = <span style=\"color: #ff0000;\">&#8216;K:\\BACKUPS\\msdb_compressed.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">RESTORE HEADERONLY FROM DISK<\/span> = <span style=\"color: #ff0000;\">&#8216;K:\\BACKUPS\\msdb_NOT_compressed.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\">\n<p><strong>Actual results:<\/strong> Both results have &#8220;HasBackupChecksums&#8221; column equal to 0.<\/p>\n<p><strong>Expected Results:<\/strong> Expecting &#8220;HasBackupChecksums&#8221; column to have value equal to 1 for the compressed backup (based on documentation).<\/p>\n<p>&nbsp;<\/p>\n<p>Run RESTORE HEADERONLY for the backup created with CHECKSUM:<\/p>\n<p>&nbsp;<\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #0000ff;\">RESTORE HEADERONLY FROM DISK<\/span> =<span style=\"color: #ff0000;\"> &#8216;K:\\BACKUPS\\msdb_CHECKSUM.bak&#8217;<\/span><\/p>\n<p style=\"padding-left: 30px;\">\n<p>&#8220;HasBackupChecksums&#8221; column is equal to 1 as expected.<\/p>\n<p>It does not look that CHECKSUM enabled automatically when COMPRESSION is enabled for the backup.<\/p>\n<p>&nbsp;<\/p>\n<p>So, the questions are:<\/p>\n<ul>\n<li>Is this the documentation issue?<\/li>\n<li>Does compression really automatically enables checksum for the backups?<\/li>\n<\/ul>\n<p>I have sent these questions to Microsoft, so if you want to know the answers as well &#8211; come back later. I will publish thier response as soon as I get it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am not sure if this is a bug or an issue with BOL documentation, but BOL says here http:\/\/msdn.microsoft.com\/en-us\/library\/ms186865.aspx: &#8220;(CHECKSUM) Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and &hellip; <a href=\"https:\/\/databaserefresh.com\/blog\/compression-enable-checksum\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[8,24,3],"tags":[25,26],"class_list":["post-110","post","type-post","status-publish","format-standard","hentry","category-sql_back_recovery","category-bugs","category-sql_server","tag-backup-compression","tag-checksum"],"_links":{"self":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/comments?post=110"}],"version-history":[{"count":13,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/110\/revisions"}],"predecessor-version":[{"id":139,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/posts\/110\/revisions\/139"}],"wp:attachment":[{"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/media?parent=110"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/categories?post=110"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/databaserefresh.com\/blog\/wp-json\/wp\/v2\/tags?post=110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}