MDF file Restoration Technology in SQL Server database

Source: Internet
Author: User

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?

(

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.