ATTACHED SUSPECT DATABASE in SQL2008 R2

Question

Post reply

ATTACHED SUSPECT DATABASE in SQL2008 R2

jolan.mahinay

Ten Centuries

Points: 1111
More actions
November 8, 2010 at 9:08 pm

#240921

hello to all
i would like to ask some assistance for attaching a suspect db a size of 400 gigz, here what happen in our prod DB. we try to run the sqlserver 2008 R2 and we found out that our DB is in suspect mode i dont know what cause then one of the IT personnel detached it while in a suspect mode then he tried to attached but it does not attach at all..is there a way to attach this database? please help we need it badly..
thanks

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

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

JeremyE SSCoach Points: 16199 More actions · Answer 1

You need to "hack" the database back into SQL server.

1. Create an empty database on the server to match the real database in size, name, files, etc.

2. Stop SQL Server services.

3. Copy the real database files (mdf,ndf,ldf) to the locations of the empty database you just created.

4. Start SQL Server and the real database should come up suspect.

5. Run ALTER DATABASE <yourdbname> SET EMERGENCY

6. Run DBCC CHECKDB to find out why the database is suspect

If this is a production database you should probably call Microsoft support.

ps. One Orange Chip Points: 29422 More actions · Answer 2

A suspect database should never have been detached!! you can try doing what was suggested in previous post. do you have backups?

Pradeep Singh

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Is the last full backup and log file available.If yes then you need to

1)Take a Tail log backup.

2)Create a new database and then restore the full backup.

3)Restore the Tail log backup.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

ps. One Orange Chip Points: 29422 More actions · Answer 4

sachin-355820 (11/9/2010)
Is the last full backup and log file available.If yes then you need to
1)Take a Tail log backup.

Database has been detached so log file wont be available.

2)Create a new database and then restore the full backup.

No need to create a new database. Restore will be just fine.

3)Restore the Tail log backup.

All subsequent log backups taken after full backup need to be restored in sequence first before restoring tail log backup(if that is available, in this case it is not)

Pradeep Singh

prakashmuddu Grasshopper Points: 15 More actions · Answer 5

i had detached suspected database in s2k8R2. i have full backups? problem is i'm not able to attach.

planning to restore the db.plz suggest. it's urgently needed.