How to fix Error 1824 on Windows Server 2025 / Windows 11

by

Why This Error Happens

SQL Server Error 1824 typically occurs when you attempt to attach a database file (.mdf) that is still in the middle of a restore operation, meaning the database has not been fully recovered or is still marked as “Restoring.” This can happen if the original RESTORE command used the WITH NORECOVERY option, leaving the database in a non-operational state that prevents a fresh attachment. Additionally, this error can surface if the log file (.ldf) associated with the data file is missing, detached, or mismatched with the primary data file’s internal sequence number.

Step-by-Step Resolution Guide

Step 1: Verify the Database Restore State

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run SELECT name, state_desc FROM sys.databases; to check if the database is listed as RESTORING.
  • If it is listed, do not attempt to detach/re-attach; proceed to recovery instead.

Step 2: Finish the Restore Sequence Properly

  • Execute the following T-SQL command to bring the database out of the loading state: RESTORE DATABASE [YourDB] WITH RECOVERY;
  • Ensure no other transaction log backups are pending to be restored before running this command.
  • Check the default data path (usually C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to ensure the file structure is intact.

Step 3: Check File Permissions and Locks

  • Navigate to the folder containing the .mdf and .ldf files.
  • Right-click the files, go to Properties > Security, and ensure the “SQL Server Database Engine” service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • Use Resource Monitor or Process Explorer to ensure no other process (like an antivirus scanner) is locking the .mdf file.

Step 4: Manually Re-attach Using T-SQL

  • If the recovery step fails, try forcing an attach using CREATE DATABASE [YourDB] ON (FILENAME = 'C:\Data\YourDB.mdf') FOR ATTACH;
  • If the log file is corrupted or missing, use the FOR ATTACH_REBUILD_LOG option instead, but only if you are sure the database was shut down cleanly prior to the restore attempt.

Alternative or Verification Steps

  • Verification: After running the WITH RECOVERY command, refresh the Object Explorer in SSMS and confirm the database icon no longer shows the “Restoring” (hourglass) overlay.
  • Alternative: If the files remain corrupted, restore the database from the last known good full backup to a new location using the SSMS Restore Wizard, selecting “Overwrite the existing database (WITH REPLACE)” to bypass the locked file state.