MDF file Restoration Technology in SQL Server database

First, place the file to be restored in the DATA file in ms SQL, and enter 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 generated database and overwrite the generated database data file MHDYF2005_data.mdf with the mdfyu1.mdf.

4. Start the database server. After refresh) 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‘, 1goreconfigure with overridego

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

update sysdatabases set status=-32768 where dbid=DB_ID(‘MHDYF2005‘)

At this time, you can see in SQL Server Enterprise Manager that the database is in "read-only, suspicious, offline, and emergency mode". You can see the tables in the database, but there are only 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 failed to lock the database to perform 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.) 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 the 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‘,0goreconfigure with overridego

A total of 10 steps are complete. The recovery process is like this. Can you recover it?

(