Can a corrupted BAK file be restored ?

Question

Hi,

When we restore a database from backup file, end users say that some of the information is missed out.

However, we have checked and confirmed the backup should be fine that night and the BAK file can be restored successfully.

Just wonder if the BAK file is corrupted, can it be restored ?  If yes, how can we find out it is corrupted ?

Thanks

Answer 1

Hi,

Only a successful restores guarantee that backup set is complete in all respect. If restore flashes a error I guess there is a corruption in backup or backup set is incomplete.However you MAY restore a backup using continue after error option

RESTORE DATABASE AdventureWorks2012 
 FROM DISK = 'Z:\SQLServerBackups\AdvWorksData.bak' 
   WITH CHECKSUM, CONTINUE_AFTER_ERROR;
GO

Please read http://msdn.microsoft.com/en-gb/library/ms175185.aspx

You can check ( not completely though) whether backup is consistent or not using restore verify only command

restore verifyonly from dis='backup Location'--back location is location of your backup on disk

If you can please post result of above command

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles

Answer 2

Similar post...

http://social.msdn.microsoft.com/Forums/en-US/447cf288-19fc-4a2d-bad7-260742f06391/restoring-bak-file-error?forum=sqldatabaseengine

http://social.msdn.microsoft.com/Forums/en-US/569c476d-14b1-4c81-b000-29c1f064bcac/restore-2008bak-file-error?forum=transactsql

http://social.msdn.microsoft.com/Forums/en-US/66364087-5d85-4754-8abe-cfb3863b89af/restoring-from-a-corrupt-bak-file-database-is-stuck-in-restoring-status?forum=sqldisasterrecovery

---

Raju Rasagounder Sr MSSQL DBA

Answer 3

To our surprise, there is no error message when we restore the database from backup.

When we restore the backup with SSMS, we clearly find that the start and end time of backup is correct together with LSN.

Answer 4

We don't get any error when we restore the BAK file.

When we run RESTORE VERIFYONLY FROM DISK='D:\MSSQL.1\MSSQL\Backup\Backup File.bak', we get "The backup set on file 1 is valid".

However, end users complain some information is lost. 

As they blame our backup must be corrupted AND we don't understand the data, how can we explain ?

Answer 5

http://www.sqlskills.com/blogs/paul/example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-io-errors/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths/

You can use CONTINUE_AFTER_ERROR clause in RESTORE command only if you did use it in the BACKUP command ( sure based on the corruption level)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

Dear Uri,

Thanks for your advice.  As mentioned before, when we run RESTORE VERIFYONLY FROM DISK='D:\MSSQL.1\MSSQL\Backup\Backup File.bak', we get "The backup set on file 1 is valid".  It appears that the backup file is not corrupted.  Is it worthwhile for us to use the CONTINUE_AFTER_ERROR clause ?

Thanks

Answer 7

Hi Tony

If it is not why do you need CONTINUE_AFTER_ERROR clause? If you have suspensions, run DBCC CHECKDB on the restored database.

I would advice you to add to the backups  WITH CHECKSUM,CONTINUE_AFTER_ERROR clauses 

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 8

We don't get any error when we restore the BAK file.

When we run RESTORE VERIFYONLY FROM DISK='D:\MSSQL.1\MSSQL\Backup\Backup File.bak', we get "The backup set on file 1 is valid".

However, end users complain some information is lost. 

As they blame our backup must be corrupted AND we don't understand the data, how can we explain ?

Hi,

If you can successfully restore a backup it means your backup is consistent. Now about data not present in backup it means transaction was not committed before backup finished so information/changes made by transaction is not present. This surely does not mean backup is corrupt.

Take a transaction log backup or differential backup taken just after full backup and ask them to restore they will get the data IF the differential backup or log backup includes the transaction changes.

Please ask them to read below link about what backup includes

http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles

Answer 9

Hi,

Many thanks for your advice.  The backup is re a Year End Finance System.

The finance staff is running Year End processing.  We get a backup that runs at 8:30pm (Daily Maintenance Plan) and another backup run at 10:00pm (They say that they have completed the task).

In both database restore, we don't get any information in the month of June 2014 that they opine that it should be there.  I just wonder if the transaction should not take more than 1.5 hours to complete (I don't know accounting at all and don't know what the Year End Processing do).

Answer 10

Unfortunately I cannot predict how much time transaction will take. But if its not there in backup either transaction did not committed or may be data which you are expecting somehow got missed . Can you again take full backup( If feasible I know its time taking for full backup)and restore and check or you can take transaction log backup and restore previous full backup with no recovery and log backup with recovery. If its not there you can say for sure that its not database issue backup is fine but since transaction or change did not come from application side its not recorded in database.

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles

Answer 11

Unfortunatley, there is no transaction log backup after 8:00pm at our site.  In other words, we only get 2 backups on hand - 8:30pm and 10:00pm for 30/6/2014.

They do update parameters on 1/7/2014 and thus they say that the backup on 1/7/2014 8:00pm is already no use for them to make analysis / manipulation.

Answer 12

Well you can ask them to take to prove your point that its not backup that is corrupt but may be data change did not committed or actually prog that run data change did not succeeded in doing so.

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles