Shulou(Shulou.com)05/31 Report--

How to repair the MDF file in SQL Server, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

First of all: if the backed up database has two files, .LDF and .MDF, open Enterprise Manager, right-click on the instance-all tasks-- attach the database, and then select the .MDF file.

Or enter in the query Analyzer:

The copy 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 database, one is to back up the database files using BACKUP DATABASE, and the other is to copy the database file mdf and log file ldf directly. The following will mainly discuss the backup and recovery of the latter. This article assumes that you are proficient in using SQL Server Enterprise Manager (SQL Server Enterprise Manager) and SQL Server Quwey Analyser (SQL Server query Analyzer).

1. Normal backup and recovery mode 0

Normally, if we want to back up a database, we must first disconnect the database from the running data server, or stop the entire database server, and then copy the file.

Command to remove the database: Sp_detach_db database name

The copy code is as follows: command to connect to the database: Sp_attach_db or sp_attach_single_file_dbs_attach_db [@ dbname =] 'dbname', [@ filename1 =]' filename_n' [, .16] sp_attach_single_file_db [@ dbname =] 'dbname', [@ physname =]' physical_name'

The database files of SQL Sever7.0 and SQL Server 2000 can be restored correctly by using this method. The key point is that both mdf and ldf files must be backed up when backing up. Mdf files are database data files and ldf is database log files.

Example:

Suppose the database is test, its data file is test_data.mdf, and its log file is test_log.ldf. Let's discuss how to back up and restore the database.

The copy code is as follows: remove the 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'

two。 Only the recovery technology of mdf files

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

Fortunately, if your mdf file is generated by the current database, you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but there will be a prompt similar to the following:

Device activation error.

The copy code is as follows: the physical file name'C:\ Program Files\ Microsoft SQL Server\ MSSQL\ data\ test_Log.LDF' may be incorrect. A new log file named'C:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data\ test_log.LDF' has been created.

Unfortunately, however, if your database files are copied from another computer, the above approach may not work. You may get an error message similar to the following:

The copy code is as follows: server: message 1813, level 16, status 2, Line 1 Server: message 1813, level 16, status 2, Line 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 I do? Don't worry, let's give an example of how to recover.

a. We use the default method to set up a database for recovery (such as test). Can be built 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 database data file test_data.mdf that you just generated with the database mdf file you want to restore.

d. Start the database server. At this point, you will see that the status of the database test is doubtful. Nothing can be done on this database at this time.

e. Set the database to allow direct operating system tables. This operation can select the database server in SQL Server Enterprise Manager, right-click, select "Properties", and select "allow direct modification of the system directory" on the "Server Settings" page. You can also use the following statement to do so.

f. Set test to emergency repair mode

The copy code is as follows: update sysdatabases set status=-32768where dbid=DB_ID ('test')

At this point, you can see in SQL Server Enterprise Manager that the database is in read-only\ doubt\ offline\ emergency mode. You can see the tables in the database, but only the system tables.

g. Perform a real restore operation to rebuild the database log file:

The copy code is as follows: dbcc rebuild_log ('test','C:\ Program Files\ Microsoft SQL Server\ MSSQL\ Data\ test_log.ldf')

During execution, if you encounter the following prompt message:

Server: message 5030, level 16, status 1, line 1

Failed to exclusively lock the database to perform the operation.

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

Indicate that your other programs are using the database, and if you just opened the system table of the test library using SQL Server Enterprise Manager in step F, just quit SQL Server Enterprise Manager.

The prompt for correct completion should be similar to:

Warning: the log for database 'test' has been rebuilt. Transaction consistency 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 excess log files.

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

When you open it in SQL Server Enterprise Manager, you will see that the status of the database is "for DBO only". You can now access the user table in the database.

h. Verify database consistency (can be omitted)

Dbcc checkdb ('test')

The general implementation results are as follows:

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

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

i. Set the database to normal

The copy code is as follows: sp_dboption 'test','dbo use only','false'

If there are no errors, congratulations, you can now use the restored database normally.

j. In the last step, we will restore the item "allow direct modification of the system directory" set in step E.

This is the answer to the question about how to repair the MDF file in SQL Server. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.