Contents:
Primary (.mdf) and Secondary (.ndf) Data Files
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:
- Hardware failure is one of the major reasons that causes SQL database corruption
- Bugs in SQL server are also responsible behind this corruption
- Abrupt shutdown of system at the time of opened database
- Modifications in the account of SQL and Virus infection as well
- SQL Server up-gradation from older versions to a recent version may also, results in corruption of the database.
SQL Server Error Messages
- Msg 823 error in SQL Server
- SQL Server Table Corruption Error
- Msg 825 (read retry) in SQL Server
- Corruption on non-clustered indexes
- Corruption on data pages
- Metadata corruption Error
- SQL Server Page Level Corruption
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:
- To begin the process, go to the Start Menu and open SQL Server Management Studio. Now, connect it to suitable instance of Database Engine and hit on the Database folder for expanding the database tree & right click on Database, then choose Tasks»Restore»Database.
- Now, from the window of Restore Database, choose From Device option and find the backup sets to recover.
- Then, from the window of Specify Backup put File as a Backup media & click on Add.
- Next, locate SQL backup file, which you wish to restore & click on Ok button.
- Under Select a page, click on Option.
- You will see a Restore Options section, choose any of the available options as per the choice.
- After this, from the grid of Restore the database file as, you may indicate new restore location for the restoration of database into a new place.
- Choose any of the options for Recovery state box that tells the condition of database once the restoration is done.
- Finally, click on OK to re-save the database.
Using Transact-SQL
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
- Firstly, open the Rebuild Master by double-clicks on Rebuildm.exe.
- A tab of Rebuild Master opens. Here, you can see Collation Settings and location of data files. Select database file and confirmed all other information. Then, click on Rebuild.
- A confirmation page appears, you need to click on Yes. Once the process is completed, a message comes again stating that rebuild is finished successfully. With this, you have a fresh master database & you can recover SQL Server data file simply.
- Now, begin a SQL Server Engine in SINGLE USER mode. For this open, the command prompt & type sqlservr.exe –c –m command from Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.
- Then, using a backup, you need to save the master database.
- Once the restoration is done, exit SINGLE USER mode & start the SQL Server in NORMAL OPERATION mode once again.
- Firstly, discontinue SQL Server instance & copy MDF & LDF files to a different location.
- After this, remove the original MDF & LDF file. Once it is done, restart the SQL Server instance & then, generate a fresh database there.
- Again, stop SQL Server & overwrite the fresh generated MDF & LDF.
- Then, put the database to an EMERGANCY mode & SINGULAR mode.
- Follow the code as mentioned below for the execution of DBCC CHCKDB & then verify the repaired files.
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
- Firstly, open Microsoft SQL Server Management Studio.
- Now, connect it with the database. Hit on New Query button.
- Then, in the page of New Query paste SQL script as mentioned below & replace [YourDatabase] with the name of your database
EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER
- Finally, click on Execute.
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:
Download SQL Database Repair Tool
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:
- Quick Scan: For fast scanning of MDF files.
- Advance Scan: For deep scanning of highly damaged MDF files.
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.
Contents:
SQL Server Transaction LOG (.ldf) File
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.
SQL Server Log File Corruption Causes
- Viruses or other Malicious Software attack on the log files
- Abnormal termination of the system
- Disturbance in the Input Output Configuration
- Exceeding the Storage Size limit
SQL Server Log Error Messages
- Error: 22004 Unicode file expected
- Error 9002. The transaction log for database is full due to 'AVAILABILITY_REPLICA
- BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally
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:
- Firstly, you need to produce a database.
Create database readingtranlog;
GO
- Now, generate a table in the database.
Use readingtranlog;
Create table person ( Sn int IDENTITY, Name char (25) );
- Then, run fn_dblog() function and see the steps & processes happens in SQL Server for the creation of database & table.
Use readingtranlog;
Select count(*) from fn_dblog(null,null)
- Next, you can see the data present the log file.
Use readingtranlog
GO
select [current lsn],
[operation],
[transaction name],
[transaction id],
[transaction sid],
[spid],
[begin time]
From fn_dblog(null,null)
- Execute some operations like INSERT, DELETE, and UPDATE to check that way these operation added to the log file.
Use readingtranlog
GO
Insert into person values (‘andrew’);
GO
Update person
Set Name =‘alan’
Where Name =‘andrew’
GO
Delete person
Where sn=‘1’
GO
- Finally, the log entries can be seen performed in the previous step
USE readingtranlog;
GO
SELECT
[current lsn],
[transaction id],
[operation],
[transaction name],
[context],
[allocunitname],
[page id],
[slot id],
[begin time],
[end time],
[number of locks],
[lock Information]
from sys.fn_dblog(NULL,NULL)
where operation IN
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
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:
Download SQL Server Log Reader Tool
- First, click on Open and add LDF or MDF file to the software.
- Now, you will get 2 options to add the file.
- Online DB Option
- Offline DB Option
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.
- You will get the entire scanning report. Once the scanning is done, you can preview the selected log files. Next, click on Export.
- You will get three options to Save Records:
- SQL Server Database
- SQL Server Compatible SQL Scripts
- CSV
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.
- You will get a confirmation window and you need to click on OK.
Contents:
SQL Server Database Backup (.bak) File
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:
- Failure of Media on the device, which contains the backup of the database.
- Write failure at the same time of the execution of backup process.
- Loss of connectivity while going through the process of backup.
Error Messages for SQL BAK Files
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.
- Check various backup sets on your device by running the mentioned code:
RESTORE HEADERONLY FROM DISK= 'c:\MyDatabase.bak'
- To specify a set of backup, run the mentioned command:
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:
Download SQL Backup Repair Tool
- To begin the process, click on Load button for loading & initializing the SQL Backup (BAK) file scanning procedure. You need to Browse the file and click on Open.
- Now, you can see two options available for the scanning:
- Quick Scan: For minor corrupted files
- Advance Scan: For severely corrupted files
Choose the SQL Server version and then click on OK.
- After this, click on the option of Multiple Backup Files to add various SQL backup files at a time. Choose any of the two options:
- Autodetect the SQL Server multiple backup (.bak) files.
- Let me choose the SQL Server multiple backup (.bak) files.
- You can see the completion of the scanning report, with the entire details.
- Then, you can preview the entire data. After this, you need to click on Export.
- Choose the desired options to export data accordingly.
- SQL Server Database
- SQL Server Compatible SQL Scripts
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:
- Only Schema
- Schema and Data
- The software will prompt a window asking if you want to recover the deleted files as well. click on Yes if you want to, otherwise on NO.
- You will see the completion report once the process is completed.
