SQL Server is a Relational Database Management Systems (RDBMS). Each modern business requires a platform to collect & analyze bulk of data. Inevitably, there are numerous platforms for the same but Microsoft SQL Server is the most constructive platform. Like any other essential RDBMS, it also supports ANSI SQL. It is used in big as well as small industries for the storage of database. It consists of 2 types of file to store data i.e. MDF and NDF. It also has LDF file for storing transaction log and BAK file to save backup of SQL Server database. Let us learn the reasons behind the corruption in each of these files along with some effective solutions.
MDF: It is the primary data file, which consists of startup information for database. Users store data & objects in this or secondary file. Every database contains one primary file, with the file extension .mdf.
NDF: It is an user-defined secondary data file used to store users’ data. The main motive of this file is to spread the data across various disks via placing the file on a different drive. Moreover, in case of exceeding size of a database, users can use NDF files to allow the database to grow. It uses .ndf file extension.
What Causes SQL Server Database Corruption
There are certain factors that are responsible for the corruption in SQL Server database such as:
Some Error Messages
Techniques to Detect Corruption
There are 2 most common approaches for the detection of database corruption.
If you are proactive user and run DBCC CheckDB regularly, then in case of corruption, you will encounter an error. It can be an error message, allocation error, or any other type of message.
Attempt Query on Table/Index
This is the second strategy to check damaged database. If the data is corrupted, it will give an error while trying to query a table/index, which is corrupt. You may encounter something like this:
Select * From Revenue;
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:244) in database ID 21 at offset 0x000000001e8000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CorruptionChallenge2.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
With both above-mentioned tricks, it can be concluded that the data is unhealthy or not. In case of error message, it is important to recover corrupt SQL Server database as soon as possible. These are some methods to repair database.
Strategies to Repair SQL Server MDF & NDF Files
Using SQL Server Management Studio
Users can restore the data from the latest backup by performing these steps:
Restore Full Backup:
You can also restore the database from T-SQL, which is another easy and less complicated way. Run this command to restore database form .bak file:
Regain a Backup with NORECOVERY:
With this option, user can leave the SQL database in a restoring condition even once the restoration has finished.
This method can be used only if you have the backup of the data. In case of no backup, proceed to other methods.
This is one of the essential methods for repairing SQL Server MDF and NDF file. The location where Rebuilt Wizard is located is
\Program Files\Microsoft SQL Server\80\Tools\BINN directory
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
Apart from all the above-discussed solutions, an innovative program has been designed for the recovery of SQL Server database. The name of this utility is SQL database repair tool. Using this, users can recover the data from SQL Server 2000, 2005, and all the above editions. It is useful in repairing the data of both MDF and NDF files. It comes up with two different options to repair data the files i.e. Quick Scan and Advance Scan. Moreover, the tool recovers the eliminated Tables of SQL Server Database. Wallet Ransomware affected MDF files can also be repaired. To perform the restoring process, users need to follow these few easy steps:
Step 1: Open MDF File
First, click on “Open” to browse & open the required MDF file from its position.
Step 2: Scanning Options
Now, scan the files i.e. MDF and NDF, using two options:
Put a mark in Auto detect option, or you can choose versions of SQL Server manually.
Step 3: Choose NDF Options
Select NDF Options, placed in the second tab. Now, add .ndf files or folder and click on OK.
Step 4: Details of MDF Files
SQL database repair tool displays the entire summarized details of the chosen MDF file after performing the scanning.
Step 5: Preview Database Item
Preview all the recovered database of both MDF & NDF files.
Step 6: Export MDF/NDF File
After clicking on Export to begin the process of exporting the data, which is recovered you can choose any option from “SQL Server Database” and “SQL Server Compatible SQL Scripts”. You can also export the data using two options i.e. “With only Schema” or “With Schema & data”. It is mandatory to put the database credentials if you are exporting to SQL Server database.
Step 7: Restore Deleted Records
A pop-up window will appear, asking if you want to recover the deleted records.
Step 8: Completion Report
Once the process is completed, you will receive report stating the completion report. Click on OK.
Now, we know that SQL Server saves the information within the master database, where it divides the physical record in primary database file i.e. MDF files. While, all the transactional information get stored in log files. These file has the extension of .ldf. The role of LDF files cannot be underestimated in SQL Server. These files are essential for users in case of any kind of transactional errors that occurs on the Server. Now, let us move further and discuss that what is the use to view SQL log file and how to read SQL LDF file?
Why it is Essential to Read SQL Server Transaction Log File
Now, again it is very important to know that what are the essential things the log files contains that make it necessary to read. Transactional log files carry the basic DML operations such as INSERT, UPDATE, and DELETE along with some DDL Operations such as CREATE, TRUNCATE, & DROP.
This implies that reading SQL log files is important as it consists of all the information related to the transactions performed by users. Now, the question arises that what is the use of this information. The answer is that this information is useful in forensics to identify the deleted records, to control the increasing size of the log file, or at the time of recovery of lost data.
How to View SQL Server Database Transaction Log File
Generally, it is not possible to read SQL database log files directly. The reason behind this is that logged data is not saved in a readable format. Therefore, to get the information from logged data, users need to use some functions such as fn_dblog () & fn_dump_dblog (), which shows the manner to open SQL Server LDF file.
It is an undocumented function of SQL, which comes up with the facility to analyze SQL Server log file. The fn_dblog() function recognizes two parameters, i.e. Start LSN and End LSN.
NULL is uniformly passed in the parameters to view all the present entries.
The fn_dblog() function can be used as:
SELECT * FROM fn_dblog(NULL,NULL)
Trick to Use fn_dblog() Function
Follow these steps as mentioned below to run fn_dblog function:
Create database readingtranlog;
Create table person ( Sn int IDENTITY, Name char (25) );
Select count(*) from fn_dblog(null,null)
select [current lsn],
Insert into person values (‘andrew’);
Set Name =’alan’
Where Name =’andrew’
[number of locks],
where operation IN
Now, another way to analyze SQL LDF files is by taking the help of third party utility SQL log reader software. This tool analyzes the log files and performs SQL LDF recovery in a rapid way. Users can view the log activities such as Time, Name, Query, Table Name, and Transaction. It becomes very easy to read and examine the Transactions such as, DELETE, INSERT, and UPDATE etc. Moreover, it is also helpful in recovery of the deleted records in case database is available in Simple Recovery Mode. One of the major features of the software is that it works in both offline & online SQL database. Users need to perform some of these simple steps:
If you choose Online DB Option, it becomes necessary to put Server Name, Authentication & then the arrow to Select Database.
Note: In this article, we are showing the method to analyze Online database log only.
If you choose to export the data to SQL Server database then you need to enter the Server Name, Database Name, User Name and Password. There is also an option to choose the type of log files like Insert, Delete or Update. Apply the Date filter and finally, choose the Destination location and click on Export.
Data is one of the most crucial parts of any organization. Therefore, it is always recommended to take the backup of the data. In SQL Server this backup file, is known as BAK file.
But what if the Backup of SQL Files Get Corrupted!
Without any doubt SQL is a safe and secure platform, but there is a possibility that the data may get corrupt or damaged. There are several reasons behind this like hardware failure, virus attack, sudden shutdown, etc. In such conditions, we can restore SQL Server database from .bak file. However, the backup files are also prone to corruption. Before, discussing the method to repair corrupt SQL backup file, let us discuss the some more reasons behind this damaged files.
Factors Responsible For Corruption of SQL BAK Files
The primary reason that results in the unhealthy or damaged SQL backup files are:
Now, in case of corrupted SQL BAK files when users try to restore the backup of the database, they may encounter some errors as mentioned below:
SQL Server 7.0
SQL Server 2000
SQL Server 2005
Methods to Repair Corrupt SQL Server Backup File
When the error occurs while restoring the data from the backup files, it is feasible to restore the other backup data by indicating the number of the file in the device.
RESTORE HEADERONLY FROM DISK= 'c:\MyDatabase.bak'
RESTORE DATABASE MyDatabase FROM DISK= 'c=\MyDatabase.bak WITH FILE=FileNumber
SQL backup repair tool is another solution that has been designed with the advance algorithms, for the recovery of backup files of SQL Server data. Users can restore and export corrupt BAK files into MS SQL Server after recovery. They can recover both MDF and NDF files from BAK file, there is no file size limitation and it supports all versions of SQL Server. Moreover, it comes up with different options to export the data i.e. SQL Server Database and SQL Server Compatible Script. Users need to perform only some simple steps to complete the task as mentioned below:
Choose the SQL Server version and then click on OK.
You need to put the database credentials if you are exporting to SQL Server database and you can export the selective data. The software export data with: