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

by

Troubleshooting Guide: Error 3145 – STOPAT Option Not Supported for SIMPLE Recovery Model

Why This Error Happens

This error occurs because the STOPAT, STOPATMARK, and STOPBEFOREMARK options rely on transaction log backups to perform point-in-time recovery, which are only available under the FULL or BULK_LOGGED recovery models. Under the SIMPLE recovery model, SQL Server automatically truncates the transaction log after each checkpoint, meaning there is no continuous log chain to roll forward to a specific point in time. As a result, when a RESTORE command including STOPAT is issued against a database backup taken under SIMPLE recovery, SQL Server 2025 rejects the operation and raises Error 3145.

Step-by-Step Resolution Guide

Step 1: Verify the Current Recovery Model

  • Open SQL Server Management Studio (SSMS) or a query window connected to your SQL Server 2025 instance.
  • Run the following query to check the recovery model of the target database:
  SELECT name, recovery_model_desc 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • If the result shows SIMPLE, this confirms the root cause of Error 3145.

Step 2: Switch to a Point-in-Time Compatible Recovery Model

  • Change the database recovery model to FULL (or BULK_LOGGED) before relying on point-in-time restores:
  ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
  • Immediately take a full database backup to initialize the log chain, as switching recovery models breaks any existing backup sequence:
  BACKUP DATABASE YourDatabaseName 
  TO DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak';

Step 3: Establish a Proper Transaction Log Backup Chain

  • Schedule regular transaction log backups using SQL Server Agent or Windows Task Scheduler on Windows Server 2025:
  BACKUP LOG YourDatabaseName 
  TO DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn';
  • Ensure log backups run frequently (e.g., every 15 minutes) to support granular point-in-time recovery.
  • Store backup files on a separate volume (e.g., D:\SQLLogBackups\) to avoid I/O contention and disk space issues.

Step 4: Re-run the Restore Command with STOPAT

  • Perform a full restore followed by sequential log restores, now specifying the STOPAT option:
  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'C:\SQLBackups\YourDatabaseName_Full.bak' 
  WITH NORECOVERY;

  RESTORE LOG YourDatabaseName 
  FROM DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn' 
  WITH STOPAT = '2025-01-15T14:30:00', RECOVERY;
  • Confirm the STOPAT timestamp falls within the time range covered by your log backup files.

Alternative or Verification Steps

  • Verify the fix by querying sys.databases again post-restore to confirm the recovery model is FULL and the database state is ONLINE.
  • If you cannot switch away from SIMPLE recovery (e.g., due to storage constraints), use full/differential backups only and restore to the nearest available backup point instead of using STOPAT.