Database can't be recovered? Any help would be appreciated!!!

Question

Database can't be recovered? Any help would be appreciated!!!

Viewing 15 posts - 16 through 30 (of 130 total)

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

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

And error 9904 is...???

Was that from attach, or from the hack method from my blog? That's partially valid for SQL 2000, up until the set emergency which has to be done differently.

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

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Error 9904: An error occured while processing the log for database 'CheckListDB'.

This happened when I tried to attach it directly to the SQL 2000 server

halifaxdal SSCoach Points: 19741 More actions · Answer 3

I created a new database with the same name, and then take it off line, and then replace with the damaged files, and then try to follow your steps in your blog on SQL 2000, I stuck here:

ALTER DATABASE CheckListDB_Data SET EMERGENCY

Server: Msg 102, Level 15, State 6, Line 1

Incorrect syntax near 'EMERGENCY'.

The database is OFFLINE and can't be brought back online

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

halifaxdal (7/20/2011)
I created a new database with the same name, and then take it off line, and then replace with the damaged files, and then try to follow your steps in your blog on SQL 2000, I stuck here:
ALTER DATABASE CheckListDB_Data SET EMERGENCY
Server: Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'EMERGENCY'.
The database is OFFLINE and can't be brought back online

Well, yes, as I said "[the blog post]'s partially valid for SQL 2000, up until the set emergency which has to be done differently."

set allow updates on and then update sysdatabases and set the status to 36767 for that database. Then try to run a simple query against it.

p.s. the trick isn't to set offline and swap out the files, that leaves the DB offline which you don't want. Stop SQL, then swap the files out, then when SQL starts it tries to open the DB. That's the state you want it it prior to setting emergency.

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

halifaxdal SSCoach Points: 19741 More actions · Answer 5

GilaMonster (7/20/2011)
Well, yes, as I said "[the blog post]'s partially valid for SQL 2000, up until the set emergency which has to be done differently."
set allow updates on and then update sysdatabases and set the status to 36767 for that database. Then try to run a simple query against it.
p.s. the trick isn't to set offline and swap out the files, that leaves the DB offline which you don't want. Stop SQL, then swap the files out, then when SQL starts it tries to open the DB. That's the state you want it it prior to setting emergency.

So, what do I do now? Sorry I don't quite get you, please help me, thank you so much.

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

Drop the database (make sure you have copies of the damaged data file)

Repeat the hack to get the DB visible, but do not set offline to swap the files, stop SQL, swap the files, restart SQL.

Then, once SQL is back up

set allow updates on and then update sysdatabases and set the status to 36767 for that database.

Then try to run a simple query against it.

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

halifaxdal SSCoach Points: 19741 More actions · Answer 7

GilaMonster (7/20/2011)
set allow updates on and then update sysdatabases and set the status to 36767 for that database.
Then try to run a simple query against it.

I run the required code:

Update sysdatabases set status = 36767 where name = 'CheckList_DB'

return results:

(0 row(s) affected)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Is the db in the db list?

Is the server case sensitive?

halifaxdal SSCoach Points: 19741 More actions · Answer 9

Ninja's_RGR'us (7/20/2011)
Is the db in the db list?
Is the server case sensitive?

No, the DB is not in the list, that's the problem, it's not even in the SYSDATABASES

The server is not case sensitive.

Thanks.

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

Did the hack attach work (create new DB, stop SQL, swap files, start SQL)?

Do you see the database in the list?

Is that it's full name?

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

halifaxdal SSCoach Points: 19741 More actions · Answer 11

GilaMonster (7/20/2011)
Did the hack attach work (create new DB, stop SQL, swap files, start SQL)?
Do you see the database in the list?
Is that it's full name?

Not in the list. I did (create new DB, stop SQL, swap files, start SQL)

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

So the DB that you created is no longer there? (the one you created in order to swap the files out)

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

halifaxdal SSCoach Points: 19741 More actions · Answer 13

GilaMonster (7/20/2011)
So the DB that you created is no longer there? (the one you created in order to swap the files out)

I created a new DB with the same name

I stopped the server

I swap the DB files

I started the server

The DB is in suspect mode

I tried to set the emergency mode but failed

:crying::crying::crying::crying:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Any way you can try doing that on another sql 2000 server instead of 2k5?

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

halifaxdal (7/20/2011)
The DB is in suspect mode
I tried to set the emergency mode but failed

Hang on, you first said the DB is not in the list, then you said it's there in suspect mode. Which is it, it can't both not be there and be there in suspect?

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