Corrupt or Ransomware-Affected SQL Server MDF/LDF: Distinguish Errors and Extraction Options
A database that will not open does not reveal the full incident. Distinguish storage, MDF/NDF structure, transaction-log and ransomware problems before choosing and validating a recovery path.
#TEKLink208 | Link.TUNGTEK.com/208
Read the analysis
Article contents
A database that will not open does not reveal the full extent of an incident. SQL Server may face storage-access issues, MDF/NDF structural damage, transaction-log problems or ransomware impact. Recovery assessment begins by distinguishing these conditions, preserving the source and reconciling results with actual business requirements.
What Should You Establish When a Database Will Not Open?
An ERP or accounting application may lose access because SQL Server is unavailable, file permissions changed or storage failed. A failed MDF attachment does not establish that every record is lost. A changed extension or ransom note warrants a ransomware investigation, but does not identify the malware family or damage scope.
- Access/resources: missing files, changed paths, service permissions or insufficient capacity.
- Structure/consistency: pages, metadata or transaction-log contents no longer match requirements.
- Ransomware: inspect actual files, clean sources and encryption coverage.
These conditions can coexist. Before attempting attachment, retain the complete error, SQL Server version and MDF/NDF/LDF inventory. A database from a newer engine cannot attach to an older one. Preserve originals and assess suitable working copies; a successful file copy while writes continue does not establish a consistent database copy.
References: Microsoft Learn — Database Detach and Attach · Microsoft Learn — Database Files and Filegroups
How Do SUSPECT and RECOVERY PENDING Differ?
The state identifies a starting point. Review SQL ERRORLOG around the incident rather than selecting a repair command from the state name alone.
| State | Meaning | Verify | Initial Check |
|---|---|---|---|
| SUSPECT | Startup recovery failed; the primary filegroup may be damaged. | Cause of recovery failure. | ERRORLOG and file set. |
| RECOVERY PENDING | A resource obstacle during recovery; not a corruption verdict. | Files, paths, permissions and capacity. | Source accessibility. |
| RECOVERING | Recovery is in progress; a transitional state. | Progress or new errors. | Logs and storage. |
Swipe horizontally to read the table on small screens.
EMERGENCY is a restricted diagnostic mode, not evidence of successful recovery. A DBA should assess state changes after the source has been preserved.
References: Microsoft Learn — Database States
Interpret SQL Server Errors 823, 824 and 825 Correctly
| Code | What SQL Server Reports | Correlate With |
|---|---|---|
| 823 | The operating system reports an I/O failure during a read/write. | OS error, disks, controllers and I/O path. |
| 824 | I/O completes, but a consistency check fails, such as checksum/torn page. | Affected page/file and underlying storage. |
| 825 | A read succeeds after a failure and retry. | Investigate I/O even when the query succeeds. |
Swipe horizontally to read the table on small screens.
Retain the file name, offset/page, time and accompanying messages; correlate Windows Event Logs. msdb.dbo.suspect_pages helps review page-error history but is not a complete database audit. Address the storage cause of 825 before failures become persistent.
5171 means the file is not a primary database file: verify its identity. 9004 concerns transaction-log processing. Physical-file errors such as 5120 require the OS error and permission context. None of these codes alone proves ransomware or a data-loss percentage.
References: Microsoft Learn — MSSQLSERVER_823 · Microsoft Learn — MSSQLSERVER_824 · Microsoft Learn — MSSQLSERVER_825 · Microsoft Learn — Database Engine Events and Errors 5000–5999 · Microsoft Learn — MSSQLSERVER_9004 · Microsoft Learn — Manage the suspect_pages Table
From HDD/SSD, RAID and Virtual Machines to SQL Data Pages
The problem may sit below the database: HDD/SSD, RAID, NAS/SAN or the file system. For VMs, distinguish a host datastore such as VMware VMFS, VMDK/VHDX virtual disks and the guest file system such as NTFS. An MDF acquired from an incorrectly reconstructed volume or incomplete virtual-disk set may be an incomplete source.
MDF is the primary data file, NDF a secondary data file, and LDF the transaction log used for recovery. LDF is not a complete MDF copy. LSN continuity helps evaluate log order; it does not make every surviving log a complete history of the data.
- Traditional SQL Server data pages are 8 KiB with 96-byte headers.
- PFS tracks allocation/free space; GAM/SGAM and IAM describe extent allocation for objects.
- Metadata/catalogs, indexes, LOB and row-overflow dependencies must also support interpretation.
A business document can depend on multiple tables, pages or files. For recovery assessment, many readable bytes do not establish complete documents: a small damaged metadata region may affect numerous objects. Determine which records can actually be extracted and validate their relationships.
References: Microsoft Learn — Database Files and Filegroups · Microsoft Learn — Page and Extent Architecture Guide · Microsoft Learn — The Transaction Log · Microsoft Learn — Troubleshoot Database Consistency Errors
Missing LDF, Damaged BAK or an Incomplete Backup Chain
Attaching database files and restoring backups are different paths. Inventory full, differential and log backups, then establish the recovery model and suitable sequence. Do not simply select the largest or latest BAK. Prefer a test restore of a clean backup in isolation and validate the restored data.
RESTORE VERIFYONLY assesses backup readability and completeness. It replaces neither an actual restore nor full structural validation. A plausible BAK name and size do not prove usability.
FOR ATTACH_REBUILD_LOG requires a read/write database, clean shutdown and all MDF/NDF files. It breaks the log-backup chain; Microsoft recommends a subsequent full backup. A crash, missing NDF or unfinished transactions cannot be made to satisfy those conditions by deleting the LDF.
Verify snapshot time, consistency and accessibility. SQL Server database snapshots specifically depend on the source database and do not replace independent backups. Assess VM snapshots with their disk chain and application state; do not assume a snapshot is a clean copy after an incident.
References: Microsoft Learn — CREATE DATABASE: ATTACH_REBUILD_LOG · Microsoft Learn — Database Detach and Attach · Microsoft Learn — Backup Overview · Microsoft Learn — RESTORE VERIFYONLY · Microsoft Learn — Database Snapshots · Microsoft Learn — The Transaction Log
Ransomware-Affected SQL Server: When Is Data Extraction Possible?
First verify encryption coverage on authorized samples and the actual file set. Whole-file, block-based and intermittent encryption create different conditions. Preserve encrypted files, backups and alternative sources for comparison: a file extension does not describe the complete encryption mechanism.
| Recovery Path | Conditions to Assess |
|---|---|
| Decryption using a legitimate key/decryptor | Correct variant, usable key or compatible tool, tested on copies. |
| Restore from a clean source | Unaffected source that can be restored and validated. |
| Extract readable regions | Pages, metadata and related components sufficient to interpret records. |
Swipe horizontally to read the table on small screens.
Assess unencrypted pages, catalogs, LOB, secondary files, logs and usable copies. Intact pages do not guarantee complete tables or relationships. As a cryptographic limitation, strongly encrypted whole-file contents may yield no plaintext without a suitable key or usable alternative source.
TDE is legitimate SQL Server encryption, not a ransomware indicator. Certificate-protected TDE requires the correct certificate and private key when moving a database; EKM deployments need corresponding key access. MDF repair cannot replace TDE keys. Assessment must consider both layers when ransomware affects a TDE database.
References: Microsoft Learn — Page and Extent Architecture Guide · Microsoft Learn — Transparent Data Encryption · Microsoft Learn — Move a TDE-Protected Database · Microsoft Learn — Backup Overview
Seven Steps for SQL Server Recovery Decisions
Choose a path from evidence and business priorities. This sequence helps administrators agree on the next decision before any action that could change the source.
- Scope and contain
Isolate suspected active ransomware, control further writes and coordinate evidence preservation with the response team.
- Preserve the source
Record time, condition and file inventory. Acquire suitable copies/images and verify them where practical before testing.
- Check storage and file completeness
Establish I/O and volume condition, MDF/NDF/LDF/BAK/TRN completeness and any additional database containers.
- Assess recovery sources
Correlate clean backups, SQL state, page/log errors and actual encryption coverage.
- Select a recovery path
Restore, resolve resource/I/O causes, recover structure or extract on working copies; document prerequisites and limitations.
- Validate in a test environment
Check technical consistency, business records and required application workflows.
- Deliver verified scope
Document recovered objects, omissions and limitations; return the application to operation under the administrator’s plan.
References: Microsoft Learn — Troubleshoot Database Consistency Errors · Microsoft Learn — Backup Overview · Microsoft Learn — DBCC CHECKDB
An ONLINE Database Still Needs Validation
ONLINE establishes accessibility, not complete business records. Acceptance needs both technical structure checks and practical business validation.
- Technical: DBCC CHECKDB where appropriate; examine tables, row counts, keys/constraints, LOB and missing objects.
- Business: reconcile periods, documents, inventory/receivables or relevant measures against actual sources.
- Application: test connections, permissions and required workflows in isolation before returning to service.
DBCC CHECKCONSTRAINTS helps assess FOREIGN KEY/CHECK constraints but does not replace business reconciliation. Compare row counts against a trustworthy reference: many records or a working application window do not establish correctness.
REPAIR_ALLOW_DATA_LOSS is a last resort when a known-good backup cannot be restored. It may remove pages/records and leave transactional or relationship inconsistencies. Preserve copies, assess the consequences and validate the result. This article provides no repair command to run on original files.
References: Microsoft Learn — DBCC CHECKDB · Microsoft Learn — DBCC CHECKCONSTRAINTS · Microsoft Learn — Troubleshoot Database Consistency Errors
DBA Preparation Checklist
A clear evidence package reduces diagnostic time and avoids experiments on the source. Prepare this inventory before transferring data to the recovery team:
- SQL Server version/build, recovery model if known, incident time and first symptoms.
- All available MDF/NDF/LDF and BAK/TRN files; retain names, paths and file-set relationships.
- SQL ERRORLOG, Windows Event Logs, application errors and recent changes.
- HDD/SSD/RAID/NAS/SAN/VM configuration, drive condition and actions already taken.
- Backup/snapshot source, timestamp, accessibility and any test-restore results.
- For suspected ransomware: ransom note, extension and authorized samples; note any TDE use.
- Priority tables/application modules and a person who can reconcile business data.
Do not post databases, passwords, key/certificate material or customer data in public comments. Agree on a secure intake channel with the data owner for sensitive material.
Frequently Asked Questions
| Question | Answer |
|---|---|
| Does failed MDF attachment mean all data is lost? | No. Verify the file set, paths, server version, exact error and readable regions. Establish the outcome through checks on a working copy. |
| Does RECOVERY PENDING always mean corruption? | No. Verify resource and file-access obstacles first; the state does not replace an integrity assessment. |
| What can be assessed when the LDF is missing? | Locate the original log and clean backups, check attachment prerequisites or assess extraction on copies. Do not assume log rebuilding or complete transactions are possible. |
| Does partial file encryption translate into a recovery percentage? | No. Damage location, metadata and page dependencies determine the result. Measure verified tables, records and business data instead. |
| Does an error-free DBCC CHECKDB establish complete business data? | No. Reconcile documents, periods and table relationships, then test the application. Structurally valid databases can still contain missing business records. |
| What should be prepared before moving a TDE database for testing? | For certificate-protected TDE, retain the correct certificate, private key and means of unlocking its backup. EKM requires suitable key access. Transfer key material securely. |
Swipe horizontally to read the table on small screens.
References: Microsoft Learn — Database States · Microsoft Learn — Database Detach and Attach · Microsoft Learn — Move a TDE-Protected Database · Microsoft Learn — DBCC CHECKDB
When a Deeper Assessment Is Needed
When backups are unusable or several layers are involved, TRecovery by TUNGTEK 🇻🇳 can review incident information to assess the next path. Start with errors, version, infrastructure, file set and backup condition. Establish the extraction scope before drawing conclusions.
References
Official Microsoft technical documentation; check the applicable SQL Server version and actual configuration.
- Microsoft Learn — Database States
- Microsoft Learn — MSSQLSERVER_823
- Microsoft Learn — MSSQLSERVER_824
- Microsoft Learn — MSSQLSERVER_825
- Microsoft Learn — Database Engine Events and Errors 5000–5999
- Microsoft Learn — MSSQLSERVER_9004
- Microsoft Learn — Database Files and Filegroups
- Microsoft Learn — Page and Extent Architecture Guide
- Microsoft Learn — The Transaction Log
- Microsoft Learn — CREATE DATABASE: ATTACH_REBUILD_LOG
- Microsoft Learn — Database Detach and Attach
- Microsoft Learn — Backup Overview
- Microsoft Learn — RESTORE VERIFYONLY
- Microsoft Learn — Database Snapshots
- Microsoft Learn — Transparent Data Encryption
- Microsoft Learn — Move a TDE-Protected Database
- Microsoft Learn — DBCC CHECKDB
- Microsoft Learn — DBCC CHECKCONSTRAINTS
- Microsoft Learn — Troubleshoot Database Consistency Errors
- Microsoft Learn — Manage the suspect_pages Table