SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Restoring: Database was damaged when the backup occurred


Error Restoring: Database was damaged when the backup occurred

Author
Message
BriC
BriC
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
I recently backed up a database and when trying to restore it to my local machine I got this error:


Msg 3182, Level 16, State 2, Line 1
The backup set cannot be restored because the database was damaged
when the backup occurred.
Salvage attempts may exploit WITH CONTINUE_AFTER_ERROR



I tried several different backups (e.g., with the crc check/verifying the backup options checked/unchecked). I tried restoring within the management studio gui and via T-SQL script. Each time the same error.

I can successfully restore if I include the 'continue_after_error' but I'm unaware of what the error was and it leaving it this way will give me a nagging feeling of doom to come.

What should I be doing to troubleshoot this? I'm a relative beginner with respect to database administration and until now, things have gone smoothly in all my backups/restores.
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2897 Visits: 1351
try restoring the backup and then run DBCC CHECKDB and look for any error messages

http://blogs.msdn.com/b/venkn/archive/2010/03/16/failing-to-restore-sql-server-database-backup-with-internal-consistency-error.aspx

Jayanth Kurup
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86376 Visits: 45232
The source database is probably damaged. Discard that backup, it's likely to be fairly useless. You shouldn't be worrying about the backup at this point, worry about the DB that the backup was taken of.

How often do you run CheckDB on the source database and when did it last run without errors?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


johnwebsoft
johnwebsoft
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 9
Checkout these below mansion references would be help you.

http://msdn.microsoft.com/en-us/library/ms177429.aspx
http://msdn.microsoft.com/en-us/library/ms175510.aspx
http://msdn.microsoft.com/en-us/library/ms177429%28v=sql.100%29.aspx


Thanks
BriC
BriC
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
Thanks everyone for the responses. I'm a hobbyist programmer so my self-training has many holes. CheckDB was one of those holes. However, running it now seemed relatively positive, as much as I can make out the output. It was much like the sample output on the MSDN page. And at the end it said,

CHECKDB found 0 allocation errors and 0 consistency errors in database 'mydatabase'



So that was a relief of sorts.

However, I noticed something that should have been obvious to me the first time around that is perhaps a related detail.

In the database listing in SQL Management Studio it has (Principal, Suspended) behind the database name. This is on the web host and after a quick search it seems that this is related to a database mirroring arrangement (that I didn't know about). Could this cause the backup problem?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86376 Visits: 45232
Yes, that's database mirroring. No, it would not have caused the backup error. It's likely to cause you lots more fun unless you resume the mirroring or remove the mirroring entirely (like full transaction logs, drives out of space and such entertainment)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


BriC
BriC
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
It turned out that fixing the database mirroring made my database restoration difficulties go away.... though it took my host a fair while to get that straightened out.

And you were right about runaway log file size ... the backups shrunk _noticeably_ after the fix.

Thanks again for the help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search