MDF file Restoration Technology in sqlserver Database

First, place the file to be restored in the data file in ms SQL. after entering the ms SQL master database server

1. We use the default method to create a database for recovery (such as mhdyf2005 ). It can be created in SQL Server.

2. Stop the database server.

3. Delete the log file mhdyf2005_log.ldf of the database you just generated, and overwrite the generated database data file mhdyf2005_data.mdf with the MDF file of the database you want to recover.

4. Start the database server. (After refreshing) the status of the database mhdyf2005 is "questionable ". Do not perform any operations on this database.

5. 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.

Use mastergosp_configure 'Allow updates', 1 goreconfigure with overridego

6. Set mhdyf2005 to the emergency repair mode. The statement is as follows:

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

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

7. perform the following recovery operations to recreate the database log file.

DBCC rebuild_log ('mhdyf2005 ', 'c: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ mhdyf2005_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 mhdyf2005 database by 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 'mhdf200' 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.

8. Verify Database Consistency (omitted). The statement is as follows:

DBCC checkdb ('mhdyf2005 ')

The general execution result is as follows:

Checkdb finds 0 allocation errors and 0 consistency errors (in database 'mhdyf200 ).

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

9. Set the database to normal. The statement is as follows:

Sp_dboption 'mhdyf2005 ', 'dbo use only', 'false'

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

10. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager or use the following statement:

Sp_configure 'Allow updates', 0 goreconfigure with overridego

A total of 10 steps are complete.

The recovery process is like this. Can you recover it?