{"id":3278,"date":"2015-12-09T08:04:32","date_gmt":"2015-12-09T13:04:32","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3278"},"modified":"2023-10-17T05:19:16","modified_gmt":"2023-10-17T09:19:16","slug":"checksum-and-verifyonly","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/","title":{"rendered":"CHECKSUM and VERIFYONLY"},"content":{"rendered":"<p>A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you will be able to restore the database from the backups? Sometimes backups can be corrupted. This is why it is useful to test database backups for their integrity. Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let&#8217;s explore CHECKSUM and VERIFYONLY options.<!--more--><\/p>\n<h2>CHECKSUM<\/h2>\n<p>The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. To make the checksum recorded on each page of the backup file the &#8220;WITH CHECKSUM&#8221; option needs to be added to the &#8220;BACKUP DATABASE&#8221; command.<\/p>\n<p>Using a CHECKSUM option makes the database backup process longer, but if the database is really large it is better to use the CHECKSUM option and be confident that all pages are written to the disk and ready for the restore process. To check the checksum value use the following SQL Server option:<\/p>\n<h2>VERIFYONLY<\/h2>\n<p>The VERIFYONLY \u00a0option does not restore the backup, it only verifies it. The VERIFYONLY performs several checks, such as:<\/p>\n<ul>\n<li>Checksum<\/li>\n<li>Availability of free space in the destination place<\/li>\n<li>Some header fields of database pages<\/li>\n<li>The readable state of all volumes and the state of the backup set<\/li>\n<\/ul>\n<p>It is recommended to check the backup file before the restore process with the help of the VERIFYONLY option. If the file is in a proper state the SQLServer will display the following message:<\/p>\n<pre>The backup set on file 1 is valid.<\/pre>\n<p>If even a single byte is damaged the SQLServer will report the following:<\/p>\n<pre><span style=\"color: #ff0000;\">Msg 3241, Level 16, State 0, Line 5<\/span>\r\n<span style=\"color: #ff0000;\">The media family on device 'd:\\checksum.bak' is incorrectly formed. SQL Server cannot processthis media family.<\/span>\r\n<span style=\"color: #ff0000;\">Msg 3013, Level 16, State 1, Line 5<\/span>\r\n<span style=\"color: #ff0000;\">VERIFY DATABASE is terminating abnormally.<\/span><\/pre>\n<h2 class=\"listing\">How to Use CHECKSUM And VERIFYONLY Options<\/h2>\n<p>Here are some ways how to add CHECKSUM to the database backups and how to check them with the help of the VERIFYONLY option:<\/p>\n<h3>T-SQL<\/h3>\n<p>Use<strong> T-SQL Command\u00a0<\/strong>to work with\u00a0<strong>CHECKSUM<\/strong>\u00a0and <strong>VERIFYONLY<\/strong>\u00a0options<em>:<\/em><\/p>\n<p>To make a database backup simply add the &#8220;WITH CHECKSUM&#8221; clause:<\/p>\n<pre><span style=\"color: #0000ff;\">BACKUP DATABASE<\/span> <em>your_database <\/em><span style=\"color: #0000ff;\">TO DISK<\/span>\u00a0= <span style=\"color: #ff0000;\">'backup_with_checksum.bak'<\/span> <span style=\"color: #0000ff;\">WITH<\/span> <span style=\"color: #ff00ff;\">CHECKSUM<\/span><\/pre>\n<p>To check the database with the VERIFYONLY option use the following syntax:<\/p>\n<pre><span style=\"color: #0000ff;\">RESTORE VERIFYONLY FROM DISK<\/span> = <span style=\"color: #ff0000;\">'backup_with_checksum.bak'<\/span><\/pre>\n<h3><strong>SQL Server Management Studio<\/strong><\/h3>\n<p>Another way of using\u00a0<strong>CHECKSUM<\/strong>\u00a0and <strong>VERIFYONLY<\/strong>\u00a0options is to apply them in\u00a0<strong>SQL Server Management Studio (SSMS)<\/strong>:<\/p>\n<p>For that, you need to right-click on the database you want to backup, select \u201cTasks\u201d, then \u201cBack up\u2026\u201d. In the window that opened select the &#8220;Media Options&#8221; page and check &#8220;Verify backup when finished&#8221; and &#8220;Perform checksum before writing to media&#8221;. Look at the picture below:<img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-3306 size-full\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Checksum1.png\" alt=\"Checksum\" width=\"608\" height=\"345\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Checksum1.png 608w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Checksum1-300x170.png 300w\" sizes=\"(max-width: 608px) 100vw, 608px\" \/><\/p>\n<h3>SQLBackupAndFTP<\/h3>\n<p>If you prefer third-party utilities\u00a0you can also use\u00a0<strong><a href=\"http:\/\/sqlbackupandftp.com\/\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">SQLBackupAndFT<\/a>P<\/strong> to set up <strong>CHECKSUM<\/strong>\u00a0and <strong>VERIFYONLY<\/strong>\u00a0options:<\/p>\n<p>Just scroll down to the &#8220;Backup options&#8221; section and check off the &#8220;Enable CheckSum&#8221; and &#8220;Verify After Backup&#8221; options.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options.png\"><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter size-large wp-image-3707\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options-648x389.png\" alt=\"\" width=\"648\" height=\"389\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options-648x389.png 648w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options-300x180.png 300w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options-768x461.png 768w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/SQLBackupAndFTP-CHECKSUM-and-VERIFYONLY-options.png 983w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you will be [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[157],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>CHECKSUM and VERIFYONLY - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server CHECKSUM and VERIFYONLY option\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"CHECKSUM and VERIFYONLY - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server CHECKSUM and VERIFYONLY option\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-09T13:04:32+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:19:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Checksum1.png\" \/>\n<meta name=\"author\" content=\"Alexandr Omelchenko\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandr Omelchenko\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/\",\"url\":\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/\",\"name\":\"CHECKSUM and VERIFYONLY - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-09T13:04:32+00:00\",\"dateModified\":\"2023-10-17T09:19:16+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server CHECKSUM and VERIFYONLY option\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"CHECKSUM and VERIFYONLY\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/academy.sqlbak.com\/#website\",\"url\":\"https:\/\/academy.sqlbak.com\/\",\"name\":\"Sql Server Backup Academy\",\"description\":\"All you need to know about Sql Server database backup\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/academy.sqlbak.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\",\"name\":\"Alexandr Omelchenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"caption\":\"Alexandr Omelchenko\"},\"sameAs\":[\"http:\/\/sqlbak.com\"],\"url\":\"https:\/\/academy.sqlbak.com\/author\/alexandr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"CHECKSUM and VERIFYONLY - Sql Server Backup Academy","description":"Learn about SQL Server CHECKSUM and VERIFYONLY option","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:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/","og_locale":"en_US","og_type":"article","og_title":"CHECKSUM and VERIFYONLY - Sql Server Backup Academy","og_description":"Learn about SQL Server CHECKSUM and VERIFYONLY option","og_url":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-09T13:04:32+00:00","article_modified_time":"2023-10-17T09:19:16+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/12\/Checksum1.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/","url":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/","name":"CHECKSUM and VERIFYONLY - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-09T13:04:32+00:00","dateModified":"2023-10-17T09:19:16+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server CHECKSUM and VERIFYONLY option","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/checksum-and-verifyonly\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"CHECKSUM and VERIFYONLY"}]},{"@type":"WebSite","@id":"https:\/\/academy.sqlbak.com\/#website","url":"https:\/\/academy.sqlbak.com\/","name":"Sql Server Backup Academy","description":"All you need to know about Sql Server database backup","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/academy.sqlbak.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8","name":"Alexandr Omelchenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","caption":"Alexandr Omelchenko"},"sameAs":["http:\/\/sqlbak.com"],"url":"https:\/\/academy.sqlbak.com\/author\/alexandr\/"}]}},"_links":{"self":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3278"}],"collection":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/comments?post=3278"}],"version-history":[{"count":32,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3278\/revisions"}],"predecessor-version":[{"id":3864,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3278\/revisions\/3864"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}