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

by

Troubleshooting Guide: SQL Server Error 3159 — Tail-Log Backup Required

Why This Error Happens

This error occurs because SQL Server detects that the existing database has an active transaction log containing data that has never been backed up, and it will not allow a RESTORE operation to proceed without explicit confirmation that this data loss is intentional. Essentially, SQL Server is protecting you from silently overwriting a database and losing committed transactions that exist only in the current log file. This commonly happens when restoring a newer backup over a database that is still online and actively being used, or when migrating/refreshing a database without first clearing its recovery chain.

Step-by-Step Resolution Guide

Step 1: Back Up the Tail of the Log (If Data Must Be Preserved)

  • Open SSMS or a query window connected to the target SQL Server 2025 instance.
  • Run the following command to capture any unbacked transactions before restoring:
  BACKUP LOG [YourDatabaseName] 
  TO DISK = 'C:\SQLBackups\YourDatabaseName_tail_log.trn' 
  WITH NORECOVERY;
  • Ensure the backup path (e.g., C:\SQLBackups\) exists and the SQL Server service account has write permissions to that directory.

Step 2: Overwrite the Database If Tail-Log Data Is Not Needed

  • If the existing log data is disposable (e.g., this is a test/dev refresh), skip Step 1 and instead force the restore using the WITH REPLACE clause:
  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak' 
  WITH REPLACE, RECOVERY;
  • Alternatively, use WITH STOPAT if you need to restore to a specific point in time rather than fully overwriting.

Step 3: Verify Database State and Recovery Model

  • Check the current recovery model, since this error primarily affects databases in FULL or BULK-LOGGED recovery mode:
  SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the database is in SIMPLE recovery mode, Error 3159 should not trigger — confirm no mismatched configuration exists between source and target environments.

Step 4: Confirm File Permissions and Path Accessibility

  • Validate that the SQL Server (MSSQLSERVER) service account in Windows Server 2025 has NTFS permissions on both the backup destination and the original .mdf/.ldf file locations (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Restart the SQL Server service via services.msc or PowerShell (Restart-Service MSSQLSERVER) if permission changes were made, to ensure they take effect.

Step 5: Re-run the Restore Operation

  • After completing the tail-log backup or applying the WITH REPLACE clause, re-execute the full RESTORE DATABASE command.
  • Monitor the SQL Server Error Log (via SSMS > Management > SQL Server Logs) to confirm the restore completes without further 3159 errors.

Alternative or Verification Steps

  • Verify success by querying sys.databases to confirm the database state_desc shows ONLINE and check msdb.dbo.restorehistory to confirm the restore operation was logged correctly.
  • If the error persists, check for active sessions or replication/Always On Availability Group configurations that may be holding log records open — run DBCC OPENTRAN('YourDatabaseName') to identify long-running transactions blocking log truncation.