Businesses often remain stuck due to issues in the SQL Server database. A common error in this line is a corrupt MDF file. Notably, an MDF comprises 8 KB data pages, chained together to form structures of index trees (often called B-trees) and system tables, managed by a file header.
A hardware glitch or a power failure causes physical or logical corruption in these structures. Repairing this error becomes crucial for the flawless functionality of an organization. Experts use various ways to serve this purpose, such as restoring from a backup, or by switching the database to Emergency-mode before using DBCC CHECKDB repair commands.
These methods need you to have SQL Server installed on your system. In case you wish to repair a corrupt MDF file without SQL Server installation, you may also opt for several online SQL repair tools. This blog discusses these methods in detail.
Table of Contents
How does an MDF file become corrupt?
A corrupted Master Data File (MDF) results from a logically or physically inconsistent internal structure, which includes page, header, data records, or allocation maps. Some common reasons behind this inconsistency are as follows:
- Abrupt or unexpected shutdowns leading to torn pages or partial writes
- Corrupted device drivers or bad sectors on the hard drive, causing unwanted data modification
- LDF file mismatched with MDF or missing, preventing SQL Server from attaching the file
- Damaged file header or misaligned file sizes due to faulty file systems or disk write-cache issues
- Malware or viruses targeting database storage can encrypt or change file blocks
- Improper file handling during an active SQL Server service can hamper database integrity
- Storing an MDF file inside poorly configured encryption layers or compressed folders can affect the I/O of SQL Server
What are the signs of a corrupt MDF file?
One obvious sign of a damaged or corrupt MDF file is the failure of the database consistency check, marked by RECOVERY PENDING STATE or SUSPECT mode. These errors will turn the database online, stopping the application behind it. The SQL Server will throw any of the following I/O errors to indicate the page that it could not access:
- Error 823: This hard I/O error prevents the OS from reading or writing the page
- Error 824: This logical consistency error indicates an issue in the checksum or structure
- Error 825: This read-retry warning shows a successful read after repeated trials, pointing to a disk failure
Any of these error codes with the subsequent messages indicates damaged database pages. To get the complete list of errors, run DBCC CHECKDB with your database name. It will help in the proper resolution of the error.
What are the different ways to repair a corrupt MDF File?
Prompt repair of a corrupt MDF file can save your organization from bearing significant losses. Admins use several methods to serve this cause:
Restore from a backup of the database
You may execute this method by using the Graphical User Interface (GUI) of SQL Server Management Studio (SSMS) or through the T-SQL query in SSMS or any other similar platform.
Use the SSMS GUI to restore the database from a backup
- Open SSMS on your system
- Connect to your SQL Server instance
- Open Restore Window
- In the Object Explorer, right-click Databases > Restore Database
- Look for the Source section, choose Device
- Select three dots (…) on the right
- In the resulting pop-up, click Add
- Find your .bak file, select it, and then click OK
- If the database exists already, configure it properly
- Overwrite the existing database (WITH REPLACE)
- Check the Close existing connections to destination database option
- Click OK to start the database restore
Using T-SQL Query to restore the database from backup
You may also run a T-SQL Query to restore the database from a backup.
-
Overwriting the existing database
The following method will work if you overwrite an existing database or opt to restore the database to its original location.
- Remove active users to avoid “database in use” errors by switching it to Single_User mode. The command is as follows:
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Use the database backup file to restore the database by using the following command:
RESTORE DATABASE [YourDatabaseName] FROM DISK = “Path to your Backup File” With REPLACE, RECOVERY;
- After a successful restore, bring the database back to Multi-user mode
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
-
Database restore with a different file path or a new name
- For this method, first examine the logical file names inside the backup by running the RESTORE FILELISTONLY, as shown in the query below:
RESTORE FILELISTONLY FROM backup_device WITH FILE = *backup_set_file_number
- Next, use the logical names to run the restore query along with the MOVE clause.
RESTORE DATABASE <new_database_name>
FROM <backup_device> [ , …n ]
[ WITH
{
[ RECOVERY | NORECOVERY ]
[ , ] [ FILE = { <backup_set_file_number> | @backup_set_file_number } ]
[ , ] MOVE ‘logical_file_name_in_backup’ TO ‘operating_system_file_name’ [ , …n ]
} ]
[ ; ]
Use the DBCC CHECKDB command
This method can prove helpful if there is no backup of your database.
- When using the DBCC CHECKDB command, experts recommend setting the database to EMERGENCY mode for restricted access. The command is as follows:
ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
- Next, set your database to Single_User mode
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- To repair the corrupt database, admins use REPAIR_ALLOW_DATA_LOSS as the last option, when no other method works. This is because it causes significant data loss.
DBCC CHECKDB ([YourDatabaseName], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
- Once the database repair process completes, bring it back to Multi_User and Online states
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
ALTER DATABASE [YourDatabaseName] SET ONLINE;
All these methods have certain limitations, which prevent users from using them more often. Instead, they prefer using third-party tools that give them a settled advantage over the manual methods in terms of usability, time, and reduced downtime. Moreover, with the availability of online SQL repair tools, you can resolve the database errors without SQL Server installation.
Repair an MDF file without installing SQL Server
Of the many tools that fall in this category, Stellar Online SQL Repair is a feasible one. It can repair damaged or corrupt SQL database files online without requiring you to download and install the SQL Server on your system. All you need is the corrupt file and access to the online tool.
It can help you to repair several SQL database issues that may remain unresolved through manual methods, such as
- Database marked SUSPECT or RECOVERY PENDING
- Missing or corrupted backup
- Failed DBCC CHECKDB command
Why opt for Stellar Online SQL Repair?
- Allows you to repair an MDF file of up to 500 MB
- You do not need a SQL Server installation
- Can instantly detect corruption in the SQL database
- Allows table structure preview
- Provides a secure connection to upload and process your MDF file
- Works only in read-only mode, leaving your original MDF files untouched
- Generates a fresh MDF file with the repaired data
- Quick online processing
Ahead of the traditional database repairs or the third-party SQL repair tools on your system, this tool goes one step ahead in offering convenience and data confidentiality.
Conclusion
A corrupt database may result from system crashes, hardware failure, bad configuration of encryption layers, virus intrusion, and many other factors. On checking the logs, Errors 823, 824, and 825 will be an indicator of severe issues like RECOVERY PENDING or the database marked SUSPECT. To resolve these issues, SQL Server administrators use several methods. For example, they use a backup file to restore a corrupt database, the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option.
These methods work effectively, but have several drawbacks in terms of SQL Server installation, time consumption, and possibility of further errors, need for in-depth technical knowledge, and more. To avoid such hassles, third-party online tools are becoming a trend. Stellar Online SQL Repair is a feasible option in this regard. While it sets you free from SQL Server installation, it is quick and easy to use, without requiring deep technical know-how.
