SQLServerCentral.com / Backups / SQL Server 2005 / Backup / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 13:49:52 GMT20RE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxExcellent information. Thanks.Thu, 30 Dec 2010 11:04:33 GMTGrant FritcheyRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspx[quote][b]Grant Fritchey (12/30/2010)[/b][hr]Just so I can learn better, do you have any white papers or deeper documentation into precisely that issue available? I've got a little best-practice article I want to write up and having a good reference would really help.[/quote]Sorry, I don't have any white paper available. It's all been part of some research into how best to validate a backup file, for an upcoming product. It'll be included in the help file for that product, but that isn't publicly available yet.It all started when we realised that SQL Server 2005 Express could verify backup sets larger than 4 GB. Yes, there is a limit (4 GB in SQL 2005, 10 GB in SQL 2008 R2) when restoring a database, but you could run RESTORE VERIFYONLY on backup files of any size. That of course opened up the possibility of setting up a dedicated machine running only the Express edition (for free) to validate backup files of any size. The backup sets would need to have been created with the CHECKSUM option so that the verification process can catch any corruption to the database data.Unfortunately, we found out that while RESTORE VERIFYONLY checks the backup data just fine, it did not fully check the MTF parts of the backup file. In a backup file, there is first the MTF header, followed by the backup data, then followed by the MTF footer. Corruption to certain parts of the MTF data can still pass the verification process, but fail during the restore. You can download an archive [url=http://www.yohz.com/downloads/restoretest.zip]here[/url] that demonstrates this. The original backup file is 'restoretest.bak', created with the CHECKSUM option on SQL 2005, that passes verification and restore. I changed a single byte in the backup data, and renamed it 'restoretest_verifyfail.bak', and that fails verification. Using the original backup file, I then changed a single byte in the MTF data and renamed it 'restoretest_verifypass_restorefail.bak', that passes verification but fails the restore with the following message on SQL 2005:[code]Server: Msg 3624, Level 20, State 1, Line 1A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. Location: restore.cpp:4440Expression: FALSESPID: 51Process ID: 316Description: Invalid switch valueConnection Broken[/code]By all accounts, that is a scary message to see during a restore, more so when you have previously successfully verified the backup file.So as you mentioned, the only sure way to ensure that your backup file is recoverable is to restore it. If you didn't use the CHECKSUM option during the backup, or is not available (e.g. using SQL 2000), best to run DBCC CHECKDB on the restored database too.Thu, 30 Dec 2010 10:38:09 GMTRay MondRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspx[quote][b]iamsam.sandeep (12/29/2010)[/b][hr]Thanks 2 all the the Responce.I was right i had mentioned 'xp_restore_verifyonly' , when i replied that you can test it in ur test env he strangely told me that u do not have any other option you have to restore directly in production.[/quote]No, I don't agree. You can restore that to another system and validate it's what you want before you restore it to production. Or, if forced, you restore it to a new database on production, validate and then restore over the existing database. You have a number of options that you can use before you get to just flat out overwriting the existing prod system with your fingers crossed.BTW, if it was a native backup, not LiteSpeed as it seems to be, you could look at using Red Gate [url=http://www.red-gate.com/products/dba/sql-virtual-restore/]Virtual Restore[/url] to create a database off the backup without actually restoring it. Cool stuff.Thu, 30 Dec 2010 06:10:27 GMTGrant FritcheyRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspx[quote][b]Ray Mond (12/29/2010)[/b][hr]Agree with Grant re. the need to actually restore from a database backup to ensure it's recoverable.RESTORE VERIFYONLY is fairly useless (with regards to data integrity in the backup set) without the use of the CHECKSUM option during a backup, and even with that option used, corruption of the MTF header and/or tail of the backup set can result in RESTORE VERIFYONLY succeeding but the actual restore to fail.[/quote]Just so I can learn better, do you have any white papers or deeper documentation into precisely that issue available? I've got a little best-practice article I want to write up and having a good reference would really help.Thu, 30 Dec 2010 06:07:14 GMTGrant FritcheyRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspx[quote]'xp_restore_verifyonly' [/quote]Seems like litespeed backup. In addition to above point, you can put verification step just after backup job and keep writing the output in text file which you can verify anytime later to see if the backup file is verified or not.Wed, 29 Dec 2010 22:54:27 GMTcrazy4sqlRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxThanks 2 all the the Responce.I was right i had mentioned 'xp_restore_verifyonly' , when i replied that you can test it in ur test env he strangely told me that u do not have any other option you have to restore directly in production.Wed, 29 Dec 2010 22:48:00 GMTiamsam.sandeepRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxAgree with Grant re. the need to actually restore from a database backup to ensure it's recoverable.RESTORE VERIFYONLY is fairly useless (with regards to data integrity in the backup set) without the use of the CHECKSUM option during a backup, and even with that option used, corruption of the MTF header and/or tail of the backup set can result in RESTORE VERIFYONLY succeeding but the actual restore to fail.Wed, 29 Dec 2010 21:32:00 GMTRay MondRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxAgreed with Pradeep. However, the best way to be sure the backup is valid is to have tested it against another server in an actual restore. For some of our most vital databases, we have a nightly job that restores them to a staging environment. It does two things. It gives our a developers a place to validate production issues without letting them into the production system, and it validates our backups are good, because we can, and do, restore from them.Wed, 29 Dec 2010 06:39:15 GMTGrant FritcheyRE: Backuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxYou would use RESTORE HEADERONLY, RESTORE FILELISTONLY to verify if the Backup file belongs to that particular database and to check the original location of the database file. Optionally you can run RESTORE VERIFYONLY on the backup file to verify its validity.Wed, 29 Dec 2010 05:38:12 GMTAdigaBackuphttp://www.sqlservercentral.com/Forums/Topic1040189-357-1.aspxI had a question that was asked to me . question wasif your db that is down and you have a Backup on the disk. How would you verify if the backup that is avaliable would help you to resolve your issue and bring the database online. Can some one clarify.Wed, 29 Dec 2010 04:27:33 GMTiamsam.sandeep