December 29, 2010 at 4:27 am
I had a question that was asked to me . question was
if 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.
December 29, 2010 at 5:38 am
You 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.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 29, 2010 at 6:39 am
Agreed 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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 29, 2010 at 9:32 pm
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.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
December 29, 2010 at 10:48 pm
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.
December 29, 2010 at 10:54 pm
'xp_restore_verifyonly'
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.
----------
Ashish
December 30, 2010 at 6:07 am
Ray Mond (12/29/2010)
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.
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 30, 2010 at 6:10 am
iamsam.sandeep (12/29/2010)
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.
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 Virtual Restore[/url] to create a database off the backup without actually restoring it. Cool stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 30, 2010 at 10:38 am
Grant Fritchey (12/30/2010)
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.
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 here 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:
Server: Msg 3624, Level 20, State 1, Line 1
A 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:4440
Expression: FALSE
SPID: 51
Process ID: 316
Description: Invalid switch value
Connection Broken
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.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
December 30, 2010 at 11:04 am
Excellent information. Thanks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply