Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Backups » Backup
Add to briefcase

Backup Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
iamsam.sandeep
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: Sunday, May 19, 2013 11:27 PM
Points: 92, Visits: 116
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
Adiga
Posted Wednesday, December 29, 2010 5:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 1,618, Visits: 20,901
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
Grant Fritchey
Posted Wednesday, December 29, 2010 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174
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
Ray Mond
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: Wednesday, May 08, 2013 2:28 AM
Points: 343, Visits: 1,510
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

TLogInfo - the only FREE tool to analyse your transaction logs. Download here.
SQL BAK Reader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here.
Post #1040696
iamsam.sandeep
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: Sunday, May 19, 2013 11:27 PM
Points: 92, Visits: 116
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
crazy4sql
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: Thursday, May 16, 2013 8:10 PM
Points: 876, Visits: 3,731
'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
Grant Fritchey
Posted Thursday, December 30, 2010 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174
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
Grant Fritchey
Posted Thursday, December 30, 2010 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174
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
Ray Mond
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: Wednesday, May 08, 2013 2:28 AM
Points: 343, Visits: 1,510
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

TLogInfo - the only FREE tool to analyse your transaction logs. Download here.
SQL BAK Reader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here.
Post #1041029
Grant Fritchey
Posted Thursday, December 30, 2010 11:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.