Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Expand / Collapse
Author
Message
Posted Wednesday, December 29, 2010 4:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 25, 2013 4:07 AM
Points: 97, Visits: 124
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.
Post #1040189
Posted Wednesday, December 29, 2010 5:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:55 AM
Points: 1,618, Visits: 20,978
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
Post #1040210
Posted Wednesday, December 29, 2010 6:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1040238
Posted Wednesday, December 29, 2010 9:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 2:00 AM
Points: 343, Visits: 1,517
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.


Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #1040696
Posted Wednesday, December 29, 2010 10:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 25, 2013 4:07 AM
Points: 97, Visits: 124
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.
Post #1040712
Posted Wednesday, December 29, 2010 10:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081
'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
Post #1040713
Posted Thursday, December 30, 2010 6:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1040841
Posted Thursday, December 30, 2010 6:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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 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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1040842
Posted Thursday, December 30, 2010 10:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 2:00 AM
Points: 343, Visits: 1,517
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.


Ray Mond
Yohz Software
Providing SQL Server database tools for 9 years and counting.
http://www.yohz.com
Post #1041029
Posted Thursday, December 30, 2010 11:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1041058
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse