How to recover corrupted database file( .mdf .ldf).

What did you do before the db wass marked suspect.??

Did you do bulk insert

There is a bug in SQl that marks the db as suspect after bulk insert

search for the fix on microsoft.com





 

Most of that database transaction was insert operation.

I already search in microsoft website and found the hotfix which i already re-install MSDE with SP4 which include that hotfix.

Is there anyway I can attach my database file into server(even there's an error in the mdf file) so that I can try to fix the problem further.

try to attach the single file to an existing database thats online
make sure that the file names are not the same, you can rename the detached file

EXEC sp_attach_single_file_db @dbname = 'databasename',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\yourdbfile.mdf'


Good luck

Try this

create a database on the server then detach the database and attach the single file

EXEC sp_detach_db @dbname = 'databasename'
EXEC sp_attach_single_file_db @dbname = 'databasename',
   @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\datafile.mdf'

EXEC sp_attach_single_file_db @dbname = 'databasename',

This give me and error indicate that there's missing the database.ldf file.
And if I didn't rename the .ldf file and attach the database using the syntax, The same old error occur :

Location : page.cpp:2801
Expression: rowLength >= offset + deleteLenght
SPID : 56
Precess ID:1612

I belive if your database is in suspect mode you can try out this method.

Open Query analyzer for the server
select Master database
update sysdatabases
set status = status & ~256
where name = 'MySuspectDatabase'

Then followed by this command
update sysdatabases
set status = status | -32768
where name = 'MioSuspectDatabase'

After this command you must stop and restart the SQL Server service. The Emergency Mode prevents SQL Server from restoring the suspect database, which now appears to be available. If the database is now accessible, you should be able to read data using standard techniques, such as a bulkcopy command or SELECT INTO commands.

Or you can create new database and transfer all data tables and queries thourgh DTS Wizard.
Warning When you are using DTS wwizard it will only ccopy data not constaraints indexes and defaults. So make sure generate script of old database.

I hope this will work like charm for you .If not then let me know i can give you other solution.

Unfortunately, I only have un-attach database file so i can't use any SQL command with it.
I looking for the way to attach the database file into the server right now.

What happens when you rename the ldf file and try to attach

By using :

EXEC sp_attach_single_file_db @dbname = 'databasename',
   @physname = 'D:\DataFolder\datafile.mdf'

I get this error message :

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'databasename'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\DataFolder\datafile.ldf' may be incorrect.

Thank you for the help. I'll check this topic again tomorrow.

good bye for today.

ps. for the attachment issue, i don't think it'll work by just normal attachment, because the database file was corrupt when i detached it(it was mark as suspect).

I already try sparab's method and succesfully attach the database back to the server. Thank you.

But, after that i try to recover the database by using sp_resetstatus and dbcc_checkdb that angelIII have suggest and get the error.

I get this from the "sp_resetstatus MyDatabase" :
Prior to updating sysdatabases entry for database 'MyDatabase', mode = 0 and status = 1073741840 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

And i get this from "DBCC_CheckDB ('MyDatabase', REPAIR_FAST )" :
Server: Msg 945, Level 14, State 2, Line 1
Database 'MyDatabase' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

ok now database is in which state is it in normal mode ?????????????

If it is suspect mode why don't u try method given by me?????