Ways to fix the MDF file for SQL Server

Ways to fix the MDF file for SQL Server

If the database backed up has 2 files, respectively. LDF and. MDF, open Enterprise Manager, right-click on the instance---All Tasks-append database, then select that. MDF file, it's OK.

Or, in Query Analyzer, enter:

sp_attach_db "Database name", "Path file name. ldf", "Path file name." MDF "

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

1. Normal backup and Recovery methods

Normally, to back up a database, first disconnect the database from the running Data server, or deactivate the entire database server, and then copy the files.

To remove a database command: sp_detach_db database name

command to connect to a database:

The following are the referenced contents:

sp_attach_db ' test ', ' C:Program filesmicrosoft sql Servermssqldatatest_data.mdf ', ' C:Program FilesMicrosoft sql Servermssqldatatest_log.ldf '

sp_attach_single_file_db ' test ', ' C:Program filesmicrosoft SQL servermssqldatatest_data.mdf '

2, only the MDF file recovery technology

For a variety of reasons, if we had just backed up the MDF file, it would be a hassle to recover.

If your MDF file is generated by the current database, then it is a fluke that you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but there will be a hint like the following

Device activation error. The physical filename ' c:program filesmicrosoft SQL servermssqldatatest_log.ldf ' may be incorrect.

Created named ' C:Program FilesMicrosoft SQL servermssqldatatest_log. LDF ' new log file. However, if your database files are replicated from other computers, unfortunately, perhaps the above approach will not work. You may get an error message similar to the following

Server: Message 1813, Level 16, State 2, line 1

Failed to open new database ' test '. The CREATE DATABASE will terminate.

Device activation error. Physical filename ' D:test_log. LDF ' may be wrong.

What to do? Take it easy, let's illustrate the recovery approach.

A. We use the default method to create a database (such as test) for recovery use. Can be built in SQL Server Enterprise Manager.

B. Deactivate the database server.

C. test_log.ldf the log file of the database that you just generated, overwriting the database data file that you just generated with the database MDF file you are restoring Test_data.mdf.

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

E. Setting up a database allows direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, right-click, select Properties, and select the Allow direct modifications to system directories on the server Settings page. You can also use the following statement to implement it.

The following is a reference fragment:

Use master

Go

sp_configure ' allow updates ', 1

Go

Reconfigure with override

Go

F. Set test to Emergency Repair mode

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

You can see in SQL Server Enterprise Manager that the database is in "read-only suspect offline emergency mode" to see the tables in the database, but only the system tables

G. Perform a real recovery operation to rebuild the database log file

DBCC REBUILD_LOG (' Test ', ' C:Program filesmicrosoft SQL servermssqldatatest_log.ldf ')

During execution, if you encounter the following prompt information:

Server: Message 5030, Level 16, State 1, line 1

Failed to lock the database to perform the operation.

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

Indicates that your other program is using the database, and if you have just opened the system table of the test library using SQL Server Enterprise Manager in step F, then quit SQL Server Enterprise Manager.

The prompt for completion should resemble the following:

Warning: Log of database ' Test ' has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can now access the user table inside the database.

H. Verifying database Consistency (can be omitted)

DBCC CHECKDB (' test ')

The results of the general implementation are as follows:

CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' test ').

DBCC execution completed. If DBCC prints an error message, contact your system administrator.

I. Set the database to a normal state

sp_dboption ' test ', ' dbo use only ', ' false '

If there is no error, then congratulations, it is now normal to use the restored database.

J. In the final step, we will restore the "allow direct modifications to the system directory" set in step e. Because the ordinary direct operating system table is a more dangerous thing. Of course, we can recover in SQL Server Enterprise Manager, or we can use the following statement to complete

The following is a reference fragment:

sp_configure ' allow updates ', 0

Go

Reconfigure with override

Go

The following are the referenced contents:

sp_attach_db or sp_attach_single_file_db.

s_attach_db [@dbname =] ' dbname ', [@filename1 =] ' Filename_n ' [,... 16]

sp_attach_single_file_db [@dbname =] ' dbname ', [@physname =] ' physical_name '

You can use this method to correctly restore SQL Sever7.0 and SQL Server 2000 database files, which is important when backing up the MDF and LDF two files, the MDF file is the database data file, and the LDF is the 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.

Remove database: sp_detach_db ' test '