Handling of corrupted SQL Server 2005 log files

In actual work and learning, many people's SQL Server 2005 database log files can be corrupted, such as hardware failures, computer restarts or shutdowns, and so on.

When the log file for SQL Server 2005 is corrupted, you will see the following:

1. Displaying the database in a suspect (suspect) state in SQL Server Management Studio.

2. The event log may receive the following error message:

Could not redo log record (21737:686:9), for transaction ID (0:2334886), on page (1:37527), database ' Test ' (Database ID 1 5). Page:lsn = (21735:299:5), type = 2. Log:opcode = 3, Context, PREVPAGELSN: (21737:615:1). Restore from a backup of the database, or repair the database.

During redoing of a logged operation in database ' Test ', an error occurred at log record ID (76116:286:2). Typically, the specific failure is previously logged as a error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

3, unable to detach the database

4, with the CREATE Database dbname on (FILENAME = N ' DBFile ') for attach_rebuild_log attached to the DB prompts: The log cannot be rebuilt because The database is not cleanly shut down.

Detailed method of recovery:

1, stop the database service.

2. Copy the database files that need to be recovered to another location.

3, start the database service.

4. Confirm that the database file you want to restore has been successfully copied to another location, and then delete the database to be recovered in SQL Server Management Studio.

5, create a new database with the same name (the database file name will be the same).

6, Stop the database service.

7, with the 2nd step of the backup of the. mdf file overwrite the new database file with the same name.

8, start the database service.

9, run ALTER DATABASE dbname set emergency, set the database to emergency mode

10, run the following command to restore the database:

Use master

DECLARE @databasename varchar (255)

Set @databasename = ' database name to recover '

exec sp_dboption @databasename, n ' single ', n ' true '--set the target data base to Single-user state

DBCC CHECKDB (@databasename, Repair_allow_data_loss)

DBCC CHECKDB (@databasename, Repair_rebuild)

exec sp_dboption @databasename, n ' single ', n ' false '--set target data base to multiuser state

Note: This method is to recover the database through the. mdf file, even if everyone's log files are lost.