How do I recover a database if the database log file is corrupted SQLServer2005? Let's analyze it in detail ...
In some accidental cases,
can cause corruption of SQL Server 2005 database log files, such as hardware failure, computer reboot, or shutdown.
When the SQL Server 2005 database log files are corrupted, the following conditions may occur:
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 15). 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 CREATE Database dbname on (FILENAME = N ' DBFile ') for attach_rebuild_log additional database
Prompts: The log cannot be rebuilt because the database is not cleanly shut down.
Recovery method:
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 it in SQL Server Management Studio
In addition to the database to be recovered.
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
The above method is to recover the database through the. mdf file, even if the log file is lost.