Data from Corrupt SQL backup file

Question

Data from Corrupt SQL backup file

andy.hextall

Valued Member

Points: 68
More actions
April 29, 2009 at 8:24 am

#203450

Hi All,
Think I may already know the answer to this post, however would like others opinion on it please. I have a corrupted sql backup file that will not restore, it appears that the backup isnt complete. Is there anyway of retrieving the data? Any software? Any methods anybody has used to retrieve such data would be much aprreciated.
Regards
Andy

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Basically no. Unless you can force it to restore using WITH CONTINUE_AFTER_ERROR - that's your last option.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

andy.hextall Valued Member Points: 68 More actions · Answer 2

Thanks Paul,

you have confirmed my initial thoughts!

Andy

andy.hextall Valued Member Points: 68 More actions · Answer 3

Hi Paul,

thanks for your earlier reply, I am trying the WITH CONTINUE_AFTER_ERROR method, I dont know if I have missed something but I am trying to create/run it through management and jobs. But cant appear to get the script(from http://www.sqlskills.com) to work. Do I need to run it as a batch command file using osql?

Regards

Andy

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

All the scripts are designed for use directly in a query window or through osql/sqlcmd.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

andy.hextall Valued Member Points: 68 More actions · Answer 5

Hi Paul,

many thanks once again for your reply, am bvery close to getting the function working. I am running it through osql in the cmd prompt, however when I try to run the restore I get the following message -:

'CONTINUE_AFTER_ERROR' is not a recognized Restore option

I am following the Example you posted on sklskills and am still getting the same message.

Any ideas.

Andy

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

Can you post the exact T-SQL you're running that generates the error please?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

andy.hextall Valued Member Points: 68 More actions · Answer 7

Hi Paul,

at the moment I am trying the example you put on http://www.sqlskills.com site.

RESTORE DATABASE broken FROM DISK='c:\broken80.bck'

WITH REPLACE, CONTINUE_AFTER_ERROR;

GO

I get a similar message when I try the BACKUP command and use WITH CHECKSUM in there.

Thanks for all your help.

Andy

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

That tells me you're not running on SQL Server 2005 - as does the name of your backup file.

There are two corruption forums - one for 2005+ and one for 7.0/2000 - as you posted here I assumed you're running 2005+.

Given that you're on 2000, there's no CONTINUE_AFTER_ERROR or CHECKSUM options to BACKUP or RESTORE - so I'm afraid its game over for that backup.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

andy.hextall Valued Member Points: 68 More actions · Answer 9

Hi Paul,

sorry a typo on my behalf, I have got both an SQL 2000 and 2005 setup and have tried both and am getting the error. I will continue to press fofrward with the script on 2005 and see if I can get it to work, no doubt I have put a typo in there aswell!!!

Many thanks for all your help.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

That syntax should work fine on SQL 2005. Can you do a select @@version in the same script and send all the results?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

manolomalaga SSCommitted Points: 1697 More actions · Answer 11

Hi, you could try this licensed software:

A) http://www.sqldatabaserepair.com/

or

B) http://www.findmysoft.com/software/damaged_backup_repair/

with SQL Server Data Recovery 7.08.01 utility.

May be a problem of hard disk, in this case you could try a hard drive recovery software like Handy Recovery.