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

by

Troubleshooting Guide: Error 3147 – Backup and Restore Operations Not Allowed on Database ‘tempdb’

Why This Error Happens

This error occurs because tempdb is a system database that SQL Server recreates from scratch every time the service restarts, meaning it holds no persistent data worth backing up. SQL Server 2025 intentionally blocks backup and restore operations against tempdb at the engine level to prevent administrators from relying on a backup strategy that can never actually be restored. Typically, this error surfaces when a maintenance plan, custom script, or third-party backup tool has been misconfigured to include system databases indiscriminately, inadvertently targeting tempdb alongside user databases.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the Backup Command

  • Check SQL Server Agent Job history in SQL Server Management Studio (SSMS) under SQL Server Agent > Jobs > View History to locate the exact job step calling the backup.
  • Review any maintenance plans via Management > Maintenance Plans for a “Back Up Database Task” that has “All databases” or “System databases” selected.
  • If using a script, search for BACKUP DATABASE tempdb or dynamic SQL loops that iterate through sys.databases without filtering.

Step 2: Exclude tempdb from Maintenance Plans or Scripts

  • Open the offending Maintenance Plan in SSMS, edit the “Back Up Database Task,” and change the database selection from “All databases” to “These databases,” then uncheck tempdb.
  • If using T-SQL, add a filter to exclude system databases: 
  WHERE name NOT IN ('tempdb', 'model')
  • For third-party tools (e.g., Ola Hallengren’s scripts), set the @Databases parameter to USER_DATABASES instead of ALL_DATABASES or SYSTEM_DATABASES.

Step 3: Correct Third-Party Backup Tool Configuration

  • Open your backup software (e.g., Veeam, CommVault, or Windows Server Backup VSS Writer settings) and locate the SQL Server component selection.
  • Deselect tempdb explicitly in the database inclusion list, since VSS-based tools sometimes auto-select all databases by default.
  • Save the job configuration and run a manual test backup excluding tempdb to confirm no errors are thrown.

Step 4: Restart the SQL Server Service to Clear Cached Job Definitions

  • Open services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER) or the named instance.
  • Right-click and select Restart to ensure any cached maintenance plan subsystem jobs reload with the corrected configuration.
  • Confirm the SQL Server Agent service also restarts automatically to pick up the updated job steps.

Alternative or Verification Steps

  • Run BACKUP DATABASE [YourUserDB] TO DISK = 'C:\Backups\test.bak' manually in SSMS to confirm backups succeed for user databases without triggering Error 3147.
  • If the error persists, query sys.dm_exec_requests and sys.dm_exec_sql_text while the job is running to catch the exact T-SQL statement attempting to back up tempdb, then remove or reconfigure that specific job step.