Attach DB fails - Error 824 - torn page - SQL 2005

Question

Post reply

Attach DB fails - Error 824 - torn page - SQL 2005

Andrew van Zyl

SSC-Addicted

Points: 498
More actions
September 20, 2010 at 4:17 am

#387826

Hi Folks,
I have a .MDF that won't attach. I've tried Creating a new DB, same file size, stopping SQL and substituting mdf files but cannot access the database at all.
Unfortunately no backups.
Can anyone suggest a solution?

Viewing 15 posts - 1 through 15 (of 23 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

if you not able to attach it, it could be because it was not properly detached.

do you the database somewhere still?

----------
Ashish

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 2

Hi,

Storage on the server failed. I have the broken .mdf and .ldf files

I created a new database, stopped SQL, substituted new .mdf with old .mdf, deleted the log file and re-started SQL.

If I run SELECT state_desc FROM sys.databases WHERE name='Heat_Logistics' I get "Recovery_Pending"

If I run dbcc CHECKDB ('hEAT_lOGISTICS', REPAIR_ALLOW_DATA_LOSS ) I get "Database 'Heat_Logistics' cannot be opened due to inaccessible files or insufficient memory or disk space."

I'm not concerned about data loss. All I want to recover is database structure, functions and SPROCS

crazy4sql SSCoach Points: 19590 More actions · Answer 3

the new database have new definition and you mixed the definition with old mdf....and sql is totally not able to understand the definition from the replaced mdf file.

if you have old mdf and ldf file then the database must be there in list...no matter what state its in. Are you able to see your old database name and its state?

----------
Ashish

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 4

Hi,

Sorry, I should have been clearer - the db was detached

steveb. SSC-Forever Points: 46733 More actions · Answer 5

So was the database corrupted, then you dettached it?

Do you have any good backups?

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 6

Andrew van Zyl

SSC-Addicted

Points: 498

September 20, 2010 at 8:27 am

#1224063

DB came up suspect, was detached and no backups

crazy4sql SSCoach Points: 19590 More actions · Answer 7

ok, so if the database was properly detached then you will be able to attach them.

whats the problem/error you getting while attaching the files?

Also try to attach with original mdf and put new ldf file instead of using old ldf. If this also fails, Please provide the error details.

----------
Ashish

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Andrew van Zyl (9/20/2010)
DB came up suspect, was detached and no backups

You may out of luck then sorry, dettaching a suspect database can be one of worst things to do when facing corruption as chances are it wont re-attach

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 9

Do any of there .mdf/SQL recovery tools work? Can anyone recommend anything?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

ashish.kuriyal (9/20/2010)
the new database have new definition and you mixed the definition with old mdf....and sql is totally not able to understand the definition from the replaced mdf file.

That's not true. The database definition is in the mdf file. What Andrew's described is a standard way of hacking a corrupt database back into SQL Server.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Andrew van Zyl (9/20/2010)
If I run SELECT state_desc FROM sys.databases WHERE name='Heat_Logistics' I get "Recovery_Pending"

Try Emergency mode

ALTER DATABASE Heat_Logistics SET EMERGENCY

I don't guarantee it'll work. In fact, if the attach is failing with an 824, it means there's a very critical page that's corrupt and there's a good chance that nothing here will be recoverable.

If setting to Emergency gives any errors, post them here.

If setting to Emergency succeeds, please run the following:

DBCC CheckDB (<Database Name>) WITH No_INFOMSGS, ALL_ERRORMSGS

Please do not do anything else at the moment.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

ashish.kuriyal (9/20/2010)
Also try to attach with original mdf and put new ldf file instead of using old ldf.

That will not work. It'll give an error saying that the ldf file does not belong to the mdf. You can't mix and match database files. If you do the 'hack DB back into server' trick, you have to replace both the mdf and ldf, or do an Attach_Rebuild_Log.

The 'hack DB back into server' trick is described here for those who aren't aware how it works:

http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

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

Andrew van Zyl SSC-Addicted Points: 498 More actions · Answer 13

Hi Gail,

Have done exactly as suggested in the link.

Database is attached, but in suspect mode.

This is what I did subsequently, errors included:

1) DBCC CHECKDB (HeatCS, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

Database 'HeatCS' is being recovered. Waiting until recovery is finished

2) ALTER DATABASE HeatCS SET EMERGENCY;

GO

DBCC CHECKDB (HeatCS, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

Database 'HeatCS' is being recovered. Waiting until recovery is finished.

3) ALTER DATABASE HeatCS SET SINGLE_USER;

GO

DBCC CHECKDB (HeatCS, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

Database 'HeatCS' is being recovered. Waiting until recovery is finished.

SELECT state_desc FROM sys.databases WHERE name='HeatCS' = Recovery_Pending

Any suggestions?

crazy4sql SSCoach Points: 19590 More actions · Answer 14

crazy4sql

SSCoach

Points: 19590

September 21, 2010 at 7:22 am

#1224792

thanks for correcting me Gail.

----------
Ashish