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

by

Troubleshooting Guide: SQL Server Error 3166 — “RESTORE DATABASE could not drop database”

Why This Error Happens

This error occurs when SQL Server attempts to perform a RESTORE DATABASE with the REPLACE option, but the existing target database cannot be dropped because it is currently in use by another session, held open by a lingering connection pool, or locked by the operating system. It can also happen if the database files are marked read-only, are being accessed by an external backup/antivirus tool, or if the SQL Server service account lacks the necessary permissions on the physical file path. In some cases, an orphaned schema lock from a previous failed restore or a database snapshot dependency prevents the drop operation from completing.

Step-by-Step Resolution Guide

Step 1: Verify and Terminate Active Connections

  • Check for existing sessions against the target database using sys.dm_exec_sessions or sp_who2.
  • Run SELECT DBID, LOGINAME, HOSTNAME FROM sys.sysprocesses WHERE DB_NAME(DBID) = 'YourDatabaseName'; to identify blocking processes.
  • Manually kill lingering sessions with KILL <session_id>; or set the database to single-user mode first.

Step 2: Force Single-User Mode Before Restore

  • Execute ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; to forcibly disconnect all users and roll back open transactions.
  • This prevents new connections from re-attaching to the database while the restore is in progress.
  • Re-run the RESTORE DATABASE command immediately after this step, as the single-user flag only applies to the current operation window.

Step 3: Check for Database Snapshots or Replication Dependencies

  • Database snapshots based on the target database will block a drop/replace operation; list them via SELECT name, source_database_id FROM sys.databases WHERE source_database_id IS NOT NULL;.
  • Drop any associated snapshots using DROP DATABASE [SnapshotName]; before attempting the restore again.
  • If the database is part of an Always On Availability Group or replication topology, remove it from the group first using SQL Server Management Studio (SSMS) or ALTER AVAILABILITY GROUP ... REMOVE DATABASE.

Step 4: Validate File System Permissions and Locks

  • Navigate to the default data path, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\, and confirm the .mdf and .ldf files are not marked Read-Only.
  • Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions on the folder via Windows Security properties.
  • Use Resource Monitor on Windows Server 2025 to check if an external process (like antivirus or a backup agent) has an open file handle on the database files, and exclude the SQL data directory from real-time antivirus scanning.

Step 5: Manually Drop the Database and Reissue Restore

  • If the above steps fail, manually drop the database using DROP DATABASE [YourDatabaseName]; while in single-user or no-user mode.
  • Reissue the original RESTORE DATABASE [YourDatabaseName] FROM DISK = 'PathToBackup.bak' WITH REPLACE, RECOVERY; command.

Alternative or Verification Steps

  • To verify the fix, re-run the restore operation and confirm it completes with the message “RESTORE DATABASE successfully processed… pages” without Error 3166 reappearing.
  • As an alternative, restore the backup under a new database name (e.g., YourDatabaseName_Restored) to bypass the drop conflict entirely, then rename it later using sp_renamedb once the original is safely decommissioned.