{"id":105,"date":"2012-01-04T07:07:44","date_gmt":"2012-01-04T07:07:44","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/01\/04\/verify-sql-server-database-backup\/"},"modified":"2012-01-04T07:07:44","modified_gmt":"2012-01-04T07:07:44","slug":"verify-sql-server-database-backup","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/01\/04\/verify-sql-server-database-backup\/","title":{"rendered":"Verify SQL Server Database Backup"},"content":{"rendered":"

Today I wanted to share on this blog a simple but yet very powerfull SQL Server command RESTORE VERIFYONLY<\/strong>.<\/p>\n

Syntax:<\/strong>
\n[sql]
\nRESTORE VERIFYONLY FROM DISK = ‘<<location of database backup>>’
\n[\/sql]<\/p>\n

This command can be used to check weather your database backup is corrupted.<\/p>\n

In order to explain this, I have taken backup of one of my database.
\nI also made another copy of database backup and I intentionally corrupted it.<\/p>\n

DatabaseBackup.BAK is the perfect database backup.
\nDatabaseBackupCorrupted.BAK is the corrupted database backup.<\/p>\n

So First I issued RESTORE VERIFYONLY command on the perferct database backup.
\nFollowing is the Query:
\n[sql]
\nRESTORE VERIFYONLY FROM DISK = ‘C:DatabaseBackupsDatabaseBackup.BAK’
\n[\/sql]<\/p>\n

Output:
\nThe backup set on file 1 is valid.<\/em><\/p>\n

There were no errors and this made me confirm that the database backup is perfect.<\/strong><\/p>\n

Now I issued the same command on the corrupted database backup.
\n[sql]
\nRESTORE VERIFYONLY FROM DISK = ‘C:DatabaseBackupsDatabaseBackupCorrupted.BAK’
\n[\/sql]<\/p>\n

As we expect the command errored out with the following error.<\/p>\n

Output:
\n
\nMsg 3242, Level 16, State 2, Line 1
\nThe file on device ‘C:DatabaseBackupsDatabaseBackupCorrupted.BAK’ is not a valid Microsoft Tape Format backup set.
\nMsg 3013, Level 16, State 1, Line 1
\nVERIFY DATABASE is terminating abnormally.
\n<\/font><\/p>\n

There this error output confirms me that the database backup is corrupted.<\/strong><\/p>\n

When ever restoration of a database backup is failing we can use the RESTORE VERIFYONLY command on the back up set and verify it.<\/p>\n

Note:<\/strong>
\nYou get the error Operating system error 2(The system cannot find the file specified)<\/strong> when the file cannot be found on the file system. In order to fix it give the correct file name and file path.
\n
\nMsg 3201, Level 16, State 2, Line 1 Cannot open backup device
\n‘C:DatabaseBackupsDatabaseBackupCorrupted1.BAK’. Operating system error 2(The system cannot find the file specified.).
\nMsg 3013, Level 16, State 1, Line 1
\nVERIFY DATABASE is terminating abnormally.
\n<\/font><\/p>\n

I got the above error when I issued the following command:
\n[sql]
\nRESTORE VERIFYONLY FROM DISK = ‘C:DatabaseBackup.BAK’
\n[\/sql]
\nAs the file C:DatabaseBackup.BAK <\/strong> does not exists.<\/p>\n

Hope this posts helps!!!<\/p>\n

Related post:
\nThe media family on device is incorrectly formed. SQL Server cannot process this media family Error: 3241<\/a>
<\/p>\n","protected":false},"excerpt":{"rendered":"

Today I wanted to share on this blog a simple but yet very powerfull SQL Server command RESTORE VERIFYONLY. Syntax: [sql] RESTORE VERIFYONLY FROM DISK = ‘<<location of database backup>>’ [\/sql] This command can be used to check weather your database backup is corrupted. In order to explain this, I have taken backup of one…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[137,128,3],"tags":[657,658,659,660,661,662,663,664,665,666,667,668,669,670,671],"class_list":["post-105","post","type-post","status-publish","format-standard","hentry","category-databasebackup","category-sql-errors","category-sqlserverpedia-syndication","tag-how-to-check-if-sql-server-database-backup-is-corruped","tag-is-by-database-backup-corrupted","tag-msg-3013","tag-msg-3013-level-16-state-1-line-1","tag-msg-3013-level-16-state-1-line-1-verify-database-is-terminating-abnormally","tag-msg-3201","tag-msg-3201-level-16-state-2-line-1","tag-msg-3201-level-16-state-2-line-3-cannot-open-backup-device-operating-system-error-2the-system-cannot-find-the-file-specified","tag-msg-3242","tag-msg-3242-level-16-state-2-line-1","tag-msg-3242-level-16-state-2-line-1-the-file-on-device-is-not-a-valid-microsoft-tape-format-backup-set","tag-restore-verifyonly-command","tag-restore-verifyonly-from-disk","tag-sql-server-database-backup-error","tag-verify-database-is-terminating-abnormally"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=105"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/105\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}