attach database that was in suspect mode

Question

Post reply

attach database that was in suspect mode

Franky Leeuwerck

SSCrazy

Points: 2036
More actions
December 17, 2007 at 6:41 am

#185012

Hello everyone,
I was asked to bring up a database again, after it went into suspect mode a while ago ( lack of diskspace of the log file ). The database was already detached. Backups are far too old to be useful.
Is it possible to restore the database on another dbms by using only the .mdf file ?
Regards,
Franky
Franky L.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Yes, use the ATTACH_REBUILD_LOG option (look it up to be sure I'm write) with the attach command.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 2

I think you can make use of sp_attach_single_file_db. It attaches a database that has only one data file to the current server. sp_attach_single_file_db cannot be used with multiple data files.

Pls refer http://msdn2.microsoft.com/en-us/library/ms174385.aspx😛

[font="Verdana"]- Deepak[/font]

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 3

Thanks for the replies.

Apologies, but I forget to mention that I'm working on SQL Server 2000.

ATTACH_REBUILD_LOG is not working in that version,

and the sp_attach_single_file_db does not work in this version because it is looking for a logfile that does not exists.

Regards,

Franky

Franky L.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 4

You don't need a log file. Just mention the data file and try. Refer,

http://msdn2.microsoft.com/en-us/library/aa259610(SQL.80).aspx

if you have multiple data files then this procedure will not work.

[font="Verdana"]- Deepak[/font]

Vivien Xing SSChampion Points: 12297 More actions · Answer 5

Free some disk space for log file and try DBCC DBRECOVER (dbname)

Please refer to:

http://msdn2.microsoft.com/en-us/library/aa937568(SQL.80).aspx

or BOL search "Insufficient Disk Space".

JeremyE SSCoach Points: 16199 More actions · Answer 6

The post by eyechart in the middle of this forum works like a charm but it involves modifying system tables and stopping and restarting SQL Server so do this on a dev/QA server.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76613

This also only works for SQL 2000 which you indicated you are using.