The moss database log file is too large-restore the database data documentary without the ldf File

A few days ago, I used the database separation method to clear the database logs corresponding to a MOSS site (the log file expands too fast, and the correct method is compressed ).

I found that the database corresponding to a MOSS site has crashed. Although I have backed up the complete mdf and ldf files, put them in the data directory, and re-Put them back, the SQL manager can only see the icon of this database, but cannot expand to see the table, and so on.

Environment:

MOSS2007, MS SQL2005, and Windows2003
The detailed recovery process is as follows:
Problem description:
It is estimated that SQL Server 2005 database log files may be damaged, such as hardware faults, abnormal computer restart or shutdown.
The symptoms are as follows:
1. in SQL Server Management Studio, the database is in the suspect state, and the tree structure of the database cannot be expanded (I did not even have this state when I recovered today, and there are only icons, the symptoms are the same as those seen in the addison seat last week ).
2. the following error message may appear in the event log:
Cocould 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 19, 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 previusly logged as an 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. When you append a DATABASE with Create database DBName ON (FILENAME = N "DBFile") FOR ATTACH_REBUILD_LOG, The following error occurs: the log cannot be rebuilt because The DATABASE was not cleanly shut down.
Recovery steps:
1. Stop the database service.
2. Copy the database files to another location.
3. Start the database service.
4. confirm that the database file to be restored has been successfully copied to another location, and then delete the database to be restored in SQL Server Management Studio.
5. Create a database with the same name (the database file name must be the same ).
6. Stop the database service.
7. Use the. mdf file backed up in step 1 to overwrite the same name file of the new database.
8. Start the database service.
9. Run alter database dbname set emergency and set the database to emergency mode.
10. Run the following command to restore the database (Name: WSS_Content) (which is executed in sequence ):

 

Code

1 -- database and emergency
2 alter database WSS_Content SET EMERGENCY
3 -- allow ad hoc updates to System Directories
4sp_configure 'Allow updates', 1
5go
6 reconfigure with override
7go
8-Single User Mode
9sp_dboption 'wss _ content', 'single user', 'true'
10 -- create the log file corresponding to this database
11 dbcc checkdb (WSS_Content, REPAIR_ALLOW_DATA_LOSS)
12 -- then you can run commands for multiple modes.
13 dbcc checkdb (WSS_Content, REPAIR_REBUILD)
14 -- Multi-User Mode
15 exec sp_dboption 'wss _ content', 'single user', 'false'
16 -- disable ad hoc updates to System Directories
17sp_configure 'Allow updates', 0
18go
19 reconfigure with override
20

Note that there are many practices on SQL2000 on the Internet. The command dbcc rebuild_log for log creation is invalid in SQL2005.