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

by

Troubleshooting Guide: SQL Server Error 3165 — “Database ‘%ls’ was restored…”

Why This Error Happens

Error 3165 typically occurs when SQL Server completes a restore operation but encounters an inconsistency between the backup file’s metadata and the current state of the database, often due to a mismatched recovery path (WITH NORECOVERY vs. WITH RECOVERY) or a broken log chain. This can also happen when the backup media is partially corrupted, truncated, or when permissions on the restore destination folder prevent SQL Server’s service account from writing files correctly. In Windows Server 2025 / Windows 11 environments, this is frequently compounded by stricter default NTFS permissions and Defender-based file locking during the restore sequence.

Step-by-Step Resolution Guide

Step 1: Verify the Backup File Integrity

  • Run RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourFile.bak' in SSMS to confirm the backup isn’t corrupted.
  • Check the backup file size against the source server to rule out a truncated transfer (common with network share copies).
  • If verification fails, re-generate the backup from the source instance.

Step 2: Check and Correct the Restore Sequence

  • Ensure full backup is restored with WITH NORECOVERY if differential or log backups follow.
  • Confirm the final file in the restore chain uses WITH RECOVERY to bring the database online.
  • Example:
  RESTORE DATABASE YourDB FROM DISK = 'C:\Backups\Full.bak' WITH NORECOVERY;
  RESTORE LOG YourDB FROM DISK = 'C:\Backups\Log1.trn' WITH RECOVERY;

Step 3: Validate NTFS and Service Account Permissions

  • Navigate to the SQL Server data directory (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
  • Right-click the folder → Properties → Security tab → confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
  • Temporarily disable Windows Defender real-time scanning on the backup/restore folders to rule out file-locking conflicts during the operation.

Step 4: Clear Orphaned Database State

  • If the database is stuck in “Restoring” mode, run:
  RESTORE DATABASE YourDB WITH RECOVERY;
  • If this fails, drop the database and restore again from a known-good full backup chain.

Step 5: Review SQL Server Error Logs

  • Open SSMS → Management → SQL Server Logs, or check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
  • Look for accompanying errors (e.g., 3241 or 3417) that indicate the specific file or LSN causing the inconsistency.

Alternative or Verification Steps

  • Run SELECT state_desc FROM sys.databases WHERE name = 'YourDB'; — it should return ONLINE if the fix succeeded.
  • If the issue persists, use DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS after recovery to confirm there is no residual page-level corruption from the restore process.