SQL Server 2000 database LDF is damaged, only MDF recovery

SQL Server 2000 database LDF is damaged. Only the restoration of SQL Server 2000 database files by MDF is often damaged. Can I fix database errors? The answer is yes. This log uses an SQL Server 2000 database. The database log file LDF is damaged, the MDF is normal, and the Restoration Method for database attaching failure is summarized, database data recovery is often complicated. When a database has a large number of errors, it is not possible to use DBCC for restoration. You need to disassemble the database to rescue important data, the following is a common SQL Server 2000 database restoration method:

1) back up the original database files (such as test. MDF) to other places in time
2) Stop the server
3) Delete the test. MDF file.
4) re-create a database with the same name as test.
5) Delete the test. LDF file of the newly created test database and overwrite the newly created test. MDF file with the test. MDF file.
6) Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database.
. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select the database server, right-click it, and select "properties". On the "Server Settings" Page, select "allow direct modification to the system directory"
7) set test to the emergency repair mode.

Update sysdatabases set status =-32768 where dbid = db_id ('test ')

At this time, you can see in SQL Server Enterprise Manager that the database is in "read-only, suspicious, offline, and emergency mode" to see the tables in the database, but only the system tables

8. Execute the true recovery operation to recreate the database log file.

DBCC rebuild_log ('test', 'c: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ test_log.ldf ')

If the following prompt is displayed during execution:

Server: Message 5030, level 16, status 1, Row 1

The database cannot be locked for this operation.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

Describe your otherProgramThe database is being used. If you opened the system table of the test database using SQL Server Enterprise Manager in step f, you can exit SQL Server Enterprise Manager.

The prompt for correct execution should be similar:

Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.

9. Verify Database Consistency

DBCC checkdb ('test ')

10. Set the database to normal

Sp_dboption 'test', 'dbo use only', 'false'

If no error occurs, Congratulations. Now we can use the recovered database normally.

11 In the last step, we need to restore the "allow direct modification to the system directory" set in Step E;

[Google ad]