MS SQL Server database repair/mdf data file Data Recovery/mdf query/mdf cannot attach

Source: Internet
Author: User
Tags sql server query

Microsoft's SQL Server database is most commonly used in two types of files:

1, the main data file, file suffix is generally. MDF;

2, transaction log file, file suffix is generally. LDF.

User data tables, views, stored procedures and so on data, are stored in the MDF file, the LDF file is stored in the MS SQL Server during the operation of the log records.

MDF files must be read to be attached to the MS SQL Server database environment before the data in them can be read properly. When the database is not attached for some reason, the contents of the MDF database file cannot be read, there are two ways to fix it:

1, using the MS SQL Server environment to repair, first mount the database in Repair mode, and then in a single-user mode to repair the database, with luck, the database can be repaired successfully.

2, using third-party tools to directly read the MDF data files, data tables, views, stored procedures, etc. extracted, and then restored to the new database.
D-recovey for MS SQL Server is a tool that can directly read MDF data files, even if the first method described above is not successful, D-recovey for MS SQL Server can sometimes read out the data in the MDF data file. Let's describe some of the data recovery features of D-recovey for MS SQL Server.

Let's take a look at some of the database corruption scenarios:

1, due to power outages and other causes of database query, this is a more common MS SQL Server database corruption situation, this situation in the first repair method described above can often be repaired, but there is also the possibility of repair.

2, database attach error, the most common such as:

3, data table query error, such as:

Database report 823 error is usually the data page check does not pass, generally due to bad, sudden power outages, data coverage and other reasons, in some cases the database can be attached, but query a table when reported 823 error, with MS SQL Server database environment also repair bad. D-recovey for MS SQL Server works well for 823 error handling.

D-recovey for MS SQL Server has the following recovery features:

1, can recover due to power outages and other reasons caused by the database status into question and damaged MDF file

2, can restore the disk bad channel and other reasons caused by the database can not be properly attached and damaged MDF file

3, can restore the database status is normal and some data table query error caused by data loss situation data

4. Able to recover data from MDF files made from MDF file fragment combination (manually extracted)

The D-recovey for MS SQL Server software interface is as follows

The interface design of the D-recovey for MS SQL Server is based on the use of MS SQL Server Enterprise Manager, and the left-hand window displays data tables, views, stored procedures, user-defined functions, user-defined data types, and click on the data table to see all the data table names , the data table "+" number expands, you can see the table field information, and so on, click on one of the tables, the right window to display the data record information of the table, if a data table record more, you can go through the next page, the previous page to page display.

Observant friends will find that each page may show a different number of records, this is the design time, according to a certain data page to display, such as no display page read 64 data page, in the MDF file, each data page the number of records is not necessarily equal, So the number of records displayed will be different.

D-recovey for MS SQL Server database recovery steps:

1. Open MDF file, select database version

2. After opening, the left side display data table, view, stored procedures, user-defined functions, user-defined data type, click "+" to expand, click on the left side of a table, the table records on the right.

Restore data to a script file

Data recovery in the export of all table structure, export the selected table structure is to export the database table structure, save as a. sql file, which is a text file containing the format of SQL statements, users can run these SQL statement files in MS SQL Server, you can create a data table.

Exporting all table data and exporting the selected table data is the export of the table data to a SQL statement file that users can run in MS SQL Server Query Analyzer to restore data from the corresponding table to MS SQL Server. It is important to note that the database is all saved as a SQL file, The program generates a commit.bat batch file, runs the commit.bat batch file, fills in the data to import the target database user name and password, and the database server IP address and target database name, and then the batch file automatically imports all the SQL statement files into the target database.

Restore data to a new database

The most useful function of database recovery is to import the data from the data tables you see directly into the new database of MS SQL Server. Join us to recover a a.mdf file, we want to restore all the data in the A.mdf file to the Dsttest database on the database server 127.0.0.1, click on the "Import all tables to Database" button of D-recovey for MS SQL Server, The following dialog box appears, we put the database server IP address, database name, connection database user name and password, and then click OK, the data in the A.mdf file is automatically imported into Dsttest.

D-recovey for MS SQL Server also has the ability to detect whether the MDF data File data page is correct.

Click on the "Check Database Files" button, the following dialog box, we choose an MDF file, start detection, in the detection process, we note the "null page" (that is, empty data page) and "Err page" (that is, the wrong data page) The number of changes, if the error data page is particularly large, That this MDF file corruption is more serious.

MS SQL Server database Another common method of recovery is by extracting the MDF data page and then consolidating it into an MDF data file, which is typically rarely attached to the database, and can be d-recovey for MS SQL The server extracts the normal data records.

1, D-recovey for MS SQL Server Overall design idea is to directly read the MDF file in the normal data records, and to save the data as SQL statements or directly into a new database, It is not designed to directly repair the original MDF file so that the MDF file can be properly attached to the database. D-recovey for MS SQL Server is especially useful in cases where the database administrator cannot use any method to fix the database so that it is properly attached.

2, the data can be successfully restored based on the MDF file critical system table is not destroyed, the data record content does exist in the MDF file, if the data record content is overwritten, data recovery will not correctly restore the original information.

If you have a friend who has a problem with the same database, you can contact me

qq:3449645657

MS SQL Server database repair/mdf data file Data Recovery/mdf query/mdf cannot attach

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.