Database attach failed repair with MDF file only
Additional times the following error:
Server: Message 1813, Level 16, State 2, line 1
Failed to open new database ' test '. The CREATE DATABASE will terminate.
Device activation error. Physical filename "D:\data\test_log. LDF ' may be wrong.
Steps:
A, use Enterprise Manager to create a new data with the same name. Can modify the default path, for the presentation convenience, I use D:\data\, data file name test.mdf, log file name Test_log.ldf
B, stop the SQL service
C, delete the Test_log.ldf file and replace the new Test.mdf file with the old Test.mdf file.
D, start the SQL service. After entering Enterprise Manager, the test data was found to be "suspect", which ensured that no one was connected to the database and did nothing.
E, set the test database to allow direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, right-click, select Properties, and select the Allow direct modifications to system directories on the server Settings page. You can also use the following statement to implement it. I did not use the command when the actual operation, the command way everyone can test themselves ^_^
Copy Code code as follows:
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
F, set test database for Emergency Repair mode. Execute the following statement in Query Analyzer: Update sysdatabases set status=-32768 where dbid=db_id (' test ')
After the replication code is refreshed in Enterprise Manager, it is found that the TTDB database appears as read only \ suspect \ offline \ Emergency mode. You can see the tables inside the database, but only the system tables.
G, rebuild the database log file. Execute the following statement in Query Analyzer:
Copy Code code as follows:
DBCC REBUILD_LOG (' Test ', ' d:\Data\test_log.ldf ')
You must quit Enterprise Manager before executing, and no one is connected to some databases. Otherwise, you will report the following error:
Server: Message 5030, Level 16, State 1, line 1
Failed to lock the database to perform the operation.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Normal message:
Warning: Log of database ' Test ' has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can access the user table inside the database.
H, verify database consistency (can be omitted, but I faithfully executed the ^_^), execute in Query Analyzer:
Copy Code code as follows:
After reporting the performance of each table, the final report:
CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' test ').
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
I, set the database to a normal state. Execute statement:
Copy Code code as follows:
sp_dboption ' test ', ' dbo use only ', ' false '
Come to this step, if there is no error, already can long sigh of relief, to drink saliva bar. : P
J, in the final step, we will restore the "allow direct modifications to the system directory" set in step e. Of course you don't have to say anything more. You can also execute a statement:
Copy Code code as follows:
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go
