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

by

Why This Error Happens

Error 3167 typically occurs when SQL Server 2025 cannot start the RESTORE operation because the service account lacks the necessary file system permissions on the backup file or target data/log directories. This can also happen if the database is still in use by active sessions, replication, or mirroring configurations that prevent an exclusive lock required for restore. Additionally, corrupted or inaccessible backup media (e.g., a .bak file on a network share with broken ACLs) can trigger this failure before the restore sequence even begins.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

  • Open Services.msc on Windows Server 2025 / Windows 11 and confirm the account running MSSQLSERVER (or named instance service).
  • Navigate to the backup file location (e.g., D:\SQLBackups\MyDB.bak) and ensure the service account has Read and Write NTFS permissions.
  • Right-click the folder → Properties → Security → Edit → Add the SQL Server service account with Full Control if needed.

Step 2: Check for Active Connections or Locks on the Database

  • Run the following query in SSMS to identify blocking sessions:
SELECT session_id, status, login_name 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('MyDB');
  • Kill any lingering sessions using KILL <session_id>; if safe to do so.
  • If the database is part of an Availability Group or mirroring setup, remove it from that configuration before attempting the restore.

Step 3: Validate Backup File Integrity and Path

  • Confirm the .bak file is not corrupted by running:
RESTORE VERIFYONLY FROM DISK = 'D:\SQLBackups\MyDB.bak';
  • Ensure the path is accessible from the SQL Server instance (avoid mapped drives; use UNC paths like \\FileServer\Backups\MyDB.bak with proper share permissions instead).

Step 4: Confirm Target Database State and File Paths

  • Check if the database already exists and is online; if so, set it to SINGLE_USER mode or drop it before restoring:
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Verify destination file paths (.mdf/.ldf) specified in the RESTORE command exist and are writable by the SQL Server service account.

Alternative or Verification Steps

  • After applying the fixes, re-run the RESTORE DATABASE command and confirm successful completion with no error codes in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
  • If the issue persists, use RESTORE DATABASE ... WITH MOVE to explicitly redefine file paths and bypass any stale path references from the original backup metadata.