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

by

Troubleshooting Guide: SQL Server Error 5197 – Uncompress File Failure

Why This Error Happens

Error 5197 occurs when SQL Server 2025 attempts to decompress a backup file (typically a .bak file created with COMPRESSION enabled) but encounters corruption, truncation, or an I/O mismatch during the decompression stream. This is commonly caused by the backup file being copied incompletely (e.g., FTP in ASCII mode instead of binary), antivirus software locking or modifying the file mid-read, or the underlying storage media (disk/network share) returning bad sectors or dropped packets. It can also occur if the backup file was created on a newer build of SQL Server with a compression algorithm not fully compatible with the restoring instance.

Step-by-Step Resolution Guide

Step 1: Verify File Integrity and Transfer Method

  • Check the file size of the .bak file on the source server versus the destination server using Get-Item <path> | Select-Object Length in PowerShell.
  • If the file was moved via FTP, re-transfer it using Binary mode, not ASCII, as ASCII mode corrupts compressed binary streams.
  • If copied over SMB/network share, re-copy using Robocopy with the /Z (restartable mode) and /V (verbose) switches to ensure a byte-for-byte copy:
  robocopy "\\source\share" "C:\Backups" filename.bak /Z /V

Step 2: Exclude SQL Server Directories from Antivirus Scanning

  • Open Windows Security on Windows Server 2025 and navigate to Virus & threat protection > Manage settings > Exclusions.
  • Add exclusions for the SQL Server data and backup directories, typically:
  • C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup
  • C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
  • Restart the SQL Server (MSSQLSERVER) service after applying exclusions:
  Restart-Service -Name 'MSSQLSERVER' -Force

Step 3: Test Restore with CHECKSUM and NO_COMPRESSION Fallback

  • Run a validation pass using RESTORE VERIFYONLY with checksum validation to isolate whether the file itself is corrupted:
  RESTORE VERIFYONLY 
  FROM DISK = 'C:\Backups\filename.bak' 
  WITH CHECKSUM;
  • If this fails, the compressed stream is damaged; request a fresh backup from the source system rather than attempting repair.
  • If you control the backup job, regenerate it using WITH CHECKSUM, COMPRESSION to catch corruption at creation time going forward:
  BACKUP DATABASE [YourDB] 
  TO DISK = 'C:\Backups\filename.bak' 
  WITH COMPRESSION, CHECKSUM;

Step 4: Check Disk Health and Available Space

  • Confirm the destination drive has sufficient free space (decompression requires temporary working space equal to or greater than the uncompressed database size).
  • Run chkdsk C: /scan on Windows Server 2025 to rule out underlying disk corruption affecting the read operation.
  • Review the Windows Event Viewer > System log for Disk or NTFS warning events (Event ID 7 or 153) that coincide with the timestamp of the SQL error.

Alternative or Verification Steps

  • Verify the fix: Re-run RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\filename.bak' WITH CHECKSUM; and confirm it completes without Error 5197, then check sys.databases to confirm the database state is ONLINE.
  • Alternative: If corruption persists only on this specific file, use RESTORE HEADERONLY to confirm the backup set is readable at the metadata level — if this also fails, the file must be regenerated from source rather than repaired.