Databases Sql Server Error 823: I/O error on attaching DataBase in Microsoft Sql Server

There is some problem with the server's hard disk. I copy the database backup file to a new position in the server. When I attach the database I got an error prompt of Microsoft Sql-DMO 823 errors. View Log as follows:

Error: 823, Severity: 24, State: 5.

I know this may caused by the damage of a database file or hardware. I try the following steps to fix the database and got succes.

Assumes that the database name to be fixed is "mydb", then:

1. Run MS-Sql Enterprise Manager, create a new database named "mydb" same as the bad database's name, assume its physical path and name are:

D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF
D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Log.LDF

2. Stop the database service

    net stop mssqlserver

3. Remove the new database's files (D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF & D:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Log.LDF) we just created to a new directory (or just rename them). Then copy the bad database files here to replace the above files. Now the sql server would take the bad database as the new one we made.

4. Start the database service

    net start mssqlserver

5. Run Sql Server Enterprise Manager, see that the database is under a suspect state

6. Put the database into emergency mode using the following statement
in Sql Query Analyzer

    sp_configure 'allow', 1
    reconfigure with override
    update sysdatabases set status = 32768 where name = 'mydb'

7, Rebuild the ldf log file. First delete the ldf file of the database, then run the following code in Sql Query Analyzer.
    DBCC REBUILD_LOG('mydb', 'D:Program Files\Microsoft SQL\Server\MSSQL\Data\mydb_Log.LDF')

then we found the new ldf file has already under 'D:Program Files\Microsoft SQL\Server\MSSQL\Data' directory.

8. Remove the database emergency mode, running
    update sysdatabases set status = 0 where name = 'mydb'
    restore database mydb WITH RECOVERY
    sp_configure 'allow', 0
    reconfigure with override

9. Using DBCC command to check the database file to see if there was anything wrong
    DBCC CHECKDB ('mydb')

Last, to run the Sql Server Enterprise Manager, expand the database item and found it has been changed to normal mode from the suspect state, open the relevant tables, the data has been restored.

When use this way to fixed your database, remember to change the 'mydb' to be your database name which need to be repaired. Have good luck! Thank you for visiting Codes here, back to take!

Add comment

Security code

Database - Sql Server