MDF file recovery technology in SQL Server _mssql

First, the files to be recovered are placed in the data file in MS SQL and into the MS SQL primary database server.

1. We use the default method to establish a database for recovery use (e.g. MHDYF2005). Can be built in SQL Server.

2. Shut down the database server.

3. Delete the log file mhdyf2005_log.ldf the database you just generated, overwriting the database data file mhdyf2005_data.mdf that you just generated with the database MDF (yu1.mdf) file that you want to recover.

4. Start the database server. (after refreshing) you will see that the status of the database MHDYF2005 is "suspect." Do not take any action on this database at this time.

5. Set the database to allow 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.


Code:
Use Mastergosp_configure ' allow updates ', 1goreconfigure with Overridego



6. Set MHDYF2005 as Emergency Repair mode with the following statement:


Code:
Update sysdatabases set status=-32768 where dbid=db_id (' MHDYF2005 ')



You can see in SQL Server Enterprise Manager that the database is in "read only \ suspect \ offline \ Emergency mode" to see the tables inside the database, but only the system tables.

7. Perform a real restore operation below to rebuild the database log file


Code:
DBCC REBUILD_LOG (' MHDYF2005 ', ' C:\Program Files\Microsoft
SQL Server\mssql\data\mhdyf2005_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 MHDYF2005 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 ' MHDYF2005 ' 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.

8. Verify database consistency (can be omitted), statements are as follows:


Code:
DBCC CHECKDB (' MHDYF2005 ')



The general results are as follows: CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' MHDYF2005 '). DBCC execution completed. If DBCC prints an error message, contact your system administrator.

9. Set the database to a normal state with the following statement:


Code:
sp_dboption ' MHDYF2005 ', ' dbo use only ', ' false '



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

10. 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 do this with the following statement:


Code:
sp_configure ' allow updates ', 0goreconfigure with Overridego



It's 10 steps, and it's finished. This is the whole recovery process, can you restore it?