SQL Server 2000,log.ldf file is missing, the workaround for attaching the database fails [go]

There are two ways to backup SQL Server databases, one is to use backup database to back up the databases, and the other is to copy the database file MDF and log file LDF directly. The following is a discussion of the latter's backup and recovery. This article assumes that you are proficient in using SQL Server Enterprise Manager (SQL Server Manager) and SQL Server Quwey Analyser (SQL Server Query Analyzer)

1, the normal backup, recovery method
In the normal way, we want to back up a database, first to disconnect the database from the running data server, or to shut down the entire database server, and then copy the files.

command to remove database: sp_detach_db database name


Commands to connect to the database: sp_attach_db or sp_attach_single_file_db
-sql
s_attach_db [@dbname =]′dbname′, [@filename1 =]′filename_n′[,... 16]
sp_attach_single_file_db [@dbname =]′dbname′, [@physname =]′physical_name′

Use this method to correctly recover the SQL Sever7.0 and SQL Server 2000 database files, the point is that when backing up the MDF and LDF two files must be backed up, MDF file is a database data file, LDF is a database log file.

Example:
Suppose the database is test, its data file is Test_data.mdf, and the log file is test_log.ldf. Let's discuss how to back up and restore the database.
To remove a database:
-sql
sp_detach_db ' Test '

To connect to a database:
-sql
sp_attach_db ' test ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_data.mdf ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf '
sp_attach_single_file_db ' test ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_data.mdf '


2, only the MDF file recovery technology
For a variety of reasons, if we just back up the MDF file, then recovery is a very troublesome thing.
If your MDF file is generated by the current database, then it is very fortunate that you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but there will be a message similar to the following
Device activation error. The physical file name ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf ' may be incorrect.
A SQL server\mssql\data\test_log named ' C:\Program Files\Microsoft has been created. LDF ' new log file.

SQL Server 2000,log.ldf file is missing

If your database files are copied from other computers, unfortunately, the above approach may not work. You may get an error message similar to the following
Server: Msg 1813, Level 16, State 2, line 1
Failed to open new database ' test '. The CREATE DATABASE terminates.
Device activation error. Physical file name ' D:\test_log. LDF ' may be wrong.
What do we do? Don't worry, here are some examples of how to recover.
A We use the default method to establish a database (such as test) to be used for recovery. can be established within SQL Server Enterprise Manager.

B Shut down the database server.

C Test_log.ldf Delete the log file for the database you just generated, overwriting the database data file test_data.mdf that you just generated with the database MDF file you want to recover.

D Start the database server. You will see that the status of the database test is "suspect". No action can be made on this database at this time.

E. Set the database to allow direct operating system tables. This operation can be found in the SQL Server Enterprise Manager tool-SQL Server configuration properties, and in the Server Settings page, select Allow direct modifications to system directories. You can also use the following statement to implement.
-sql
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go


F. Set test to Emergency Repair mode
-sql
Update sysdatabases set status=-32768 where dbid=db_id (' test ')

In SQL Server Enterprise Manager, you can see that the database is in "read only \ suspect \ offline \ Emergency mode" and you can see the tables in the database, but only the system tables

G Perform a real recovery operation below to rebuild the database log file
-sql
DBCC REBUILD_LOG (' Test ', ' C:\Program Files\Microsoft SQL Server\mssql\data\test_log.ldf ')

During the execution, if you encounter the following prompt message:
Server: Msg 5030, Level 16, State 1, line 1
Failed to lock the database in order to perform the operation.
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. [Brown]
Indicates that your other program is using the database, and if you have just opened the system table for the test library using SQL Server Enterprise Manager in the F step, exit SQL Server Enterprise Manager.
The correct execution of the completion prompt should resemble the following:
Warning: The Log for database ' test ' has been rebuilt. The consistency of the transaction has been lost. You should run DBCC CHECKDB to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you see that the status of the database is "only for dbo use." You can now access the user tables in the database.

H. Verifying database consistency (can be omitted)
-sql
DBCC CHECKDB (' test ')

General implementation results are as follows:
CHECKDB found 0 allocation errors and 0 conformance errors (in database ' test ').
DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

I. Setting the database to a normal state
-sql
sp_dboption ' test ', ' dbo use only ', ' false '

If there is no error, then congratulations, you can now normal use of the restored database.

J In the final step, we will restore the "allow direct modification to the system directory" setting in step e. Because the direct operating system table is a relatively dangerous thing. Of course, we can recover from SQL Server Enterprise Manager, or we can use the following statement to complete
-sql
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go

SQL Server 2000,log.ldf file is missing, the workaround for attaching the database fails [go]