attaching a detach 'SUSPECT' database in MSSQL7

[pparker]

greetings!

first of all I want to congratulate you guys for this great forum you have. It have help me lot just reading several threads here. I myself have posted my own questions of couple of times before and the response have been great.

now I've got new concern with my SQL7. this regards attaching a formerly 'SUSPECT' status database which was detach.

I have detached a database(by the way the database is broken into filegroups) using 'sp_detach_db' while the database was in 'SUSPECT' status, because I was trying to restore the database to its normal state, I tried everything and detaching/attaching was the last option I tried... now its worse.

When I tried attaching using sp_attach_single_file_db' this error occurs:


Server: Msg 3624, Level 20, State 1, Line 1

21 transactions rolled forward in database 'DelMonte' (7).
Location: scanrid.cpp:315
Expression: m_len != 0
SPID: 8
Process ID: 1712

Connection Broken


I've run out of ideas already.
Any suggestions? help. :-)
I really need to re-attach the database.


thank you very much.

regards,
pparker

[skhanal]

Since database was suspect when detached, it can't be attached back.

Do you have any backup?. You will have to restore the database from the backup.

[pparker]

hi skhanal,

thanks to ur prompt reply,

unfortunately the database doesn't have backup or and any backup procedures of sort. (sad)

actually i was just new(2 days) to this company im working right now, and this is what they want me to do first, that is to be able to restore their down database and also fix their existing sales system which was made by a third party firm which they can no longer contact for technical support. I myself doesn't have the vague idea why proper backup procedures have not been made.

you think its gone 4ver?
oh man, im sure this is not only the first time it happens to anyone. i really hope someone who have encountered same situation could give us suggestions on how to fix it.
i've been doin search for 2 days now, still no clear answer. :-)

thank you.

regards,
pparker

[MAK]

--try this

1. create same database
2. stop sql server
3. copy your suspect mdf file to the created database location
4. start sql server in singe user mode
5. update status of sysdatabase table for the created database to emergency
6. restart sql server

[pparker]

hi mak,

the database is broken into filegroup, and the primary database is about 122GB which is an ndf file

i did try this on the mdf and ldf, what i did:

1. shutdown SQL
2. i transferred location mdf and ldf since its only about 8MB.
3. start SQL and create new database of the same name.
5. shutdown SQL
4. copy/paste the original mdf and ldf to to overwrite newly created mdf, ldf.
6. start SQL. database is now in suspect status.
7. stop SQL
8. Start SQL then run script:
sp_dboption 'dbname', 'single user', true

until now database is in suspect status.

mak, sorry i dont know how to do ur #5 step... can you elaborate.

thank you very much

regards,
pparker

[MAK]

Something similar.....

http://forums.databasejournal.com/sh...e+sysdatabases

[pparker]

greetings!

hi mak! you're a life-saver, I just did it! thanks to you.
database was successfully attached and data are intact.

thanks!

regards,
pparker

[MAK]

I am glad that I can help some body.

PLease take a backup.

[pparker]

hi mak,

luckily im one of those somebody. ;-)
making backup now, this would take approx 3 days... whew!

thnx.

pparker

[MAK]

Stripe it to multiple files. It will take only few hrs

[pparker]

with a 130GB size database, I don't know how many strip files(filegroups) that would be... :-)

anyway, I've already run the backup so, i'll just have to wait...:=) im now focusing in revising the program itself and find out why it created such a huge database in less than a year!.

:=)
pparker