How to easily restore/repair SQL Server MDF files

First, if the backup database has two files. LDF and. MDF: Open the Enterprise Manager, right-click the instance, select all tasks, and attach the database. MDF file.

Alternatively, enter:

Copy codeThe Code is as follows: sp_attach_db "Database Name", "path \ file name. ldf", "path \ file name. MDF"

There are two ways to back up SQL Server databases: one is to back up DATABASE files using BACKUP DATABASE, and the other is to directly copy the DATABASE files mdf and log files ldf. The following describes the backup and recovery of the latter. This document assumes that you are familiar with SQL Server Enterprise Manager and SQL Server Quwey Analyser ).

1. Normal backup and recovery methods 0

Normally, we need to back up a database. First, we need to disconnect the database from the running data server, or stop the entire database server, and then copy the file.

Command for removing the database: Sp_detach_db Database Name

Copy codeThe Code is as follows:
Command for connecting to the database: 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'

This method can be used to correctly restore the database files of SQL Sever7.0 and SQL Server 2000. The key point is that the mdf and ldf files must be backed up during backup. The mdf file is the database data file, ldf is a database log file.

Example:

Assume that the database is test, the data file is test_data.mdf, and the log file is test_log.ldf. Next we will discuss how to back up and restore the database.

Copy codeThe Code is as follows:
Detach a database: sp_detach_db 'test'
Connect to the database: 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 restoration technology is available.

For various reasons, if we only backed up the mdf file at that time, it would be very troublesome to restore it.

If your mdf file is generated by the current database, you may be lucky to use sp_attach_db or sp_attach_single_file_db to restore the database, but the following prompt will appear:

Device activation error.

Copy codeThe Code is as follows:
Physical file name 'C: \ Program Files \ Microsoft SQL Server
\ MSSQL \ data \ test_Log.LDF 'may be incorrect.
You have created a file named 'C: \ Program Files \ Microsoft SQL Server.
\ MSSQL \ Data \ test_log.LDF 'New log file.

However, if your database files are copied from other computers, the above method may not work. You may get an error message similar to the following:

Copy codeThe Code is as follows:
Server: Message 1813, level 16, status 2, Row 1
Server: Message 1813, level 16, status 2, Row 1 failed to open the new database 'test '. Create database will be terminated.
Device activation error. The physical file name 'd: \ test_log.ldf' may be incorrect.

What should we do? Don't worry. The following is an example of the restoration method.

A. We use the default method to create A database for recovery (such as test ). It can be created in SQL Server Enterprise Manager.

B. Stop the database server.

C. Delete the log file test_log.ldf of the database you just generated, and overwrite the generated database data file test_data.mdf with the mdf file to be restored.

D. Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database.

E. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, right-click the database Server and select "properties". On the "Server Settings" Page, select "allow direct modification to system directory. You can also use the following statement.

F. Set test to emergency repair mode.

Copy codeThe Code is as follows:
Update sysdatabases set status =-32768
Where dbid = DB_ID ('test ')

At this time, you can see in SQL Server Enterprise Manager that the database is in "read-only, suspicious, offline, and emergency mode" to see the tables in the database, but only the system tables

G. Execute the true recovery operation below to recreate the database log file:

Copy codeThe Code is as follows:
Dbcc rebuild_log ('test', 'c: \ Program Files \ Microsoft
SQL Server \ MSSQL \ Data \ test_log.ldf ')

If the following prompt is displayed during execution:

Server: Message 5030, level 16, status 1, Row 1

The database cannot be locked for this operation.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

This indicates that other programs are using this database. If you opened the system table of the test database using SQL Server Enterprise Manager in step F, you can exit SQL Server Enterprise Manager.

The prompt for correct execution should be similar:

Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. Dbcc checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.

H. Verify Database Consistency (omitted)

Dbcc checkdb ('test ')

The general execution result is as follows:

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

DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

I. Set the database to normal

Copy codeThe Code is as follows: sp_dboption 'test', 'dbo use only', 'false'

If no error occurs, Congratulations. Now we can use the recovered database normally.

J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E.