SQL Server backup failed: causes and fixes

"Backup failed" can mean a dozen different things. The quickest route to a fix is to find the real error message, then match it to its cause. Almost every SQL Server backup failure comes down to permissions, paths, space, database state or the destination.

Step 1: find the real error

Error 3013, "BACKUP DATABASE is terminating abnormally", is only a wrapper. The useful message is the one printed just before it. Look in:

• The job or script output, if you capture it to a log file.
• The SQL Server error log. In SSMS go to Management > SQL Server Logs, or query it:

EXEC sys.xp_readerrorlog 0, 1, N'backup';

Then check which databases have gone longest without a good backup:

SELECT d.name, d.state_desc, d.recovery_model_desc,
       MAX(b.backup_finish_date) AS last_full_backup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b
       ON b.database_name = d.name AND b.type = 'D'
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.state_desc, d.recovery_model_desc
ORDER BY last_full_backup;

Step 2: match the message to the cause

Backups to network shares

Network destinations fail for reasons that local ones do not:

• Mapped drives are per user. The SQL Server service does not see your Z: drive. Always use a UNC path.
• Built-in service accounts reach the network as the computer account, DOMAIN\SERVERNAME$. That account needs rights on both the share and the folder. On a workgroup (no domain), use a named local account with a matching account on the NAS.
• Brief network drops fail large backups halfway. A retry after a few minutes usually succeeds.

Works by hand, fails on schedule

When a backup runs fine in SSMS but fails as a scheduled task, the difference is almost always the account. The scheduled task runs as a different Windows user, which may not have a SQL Server login, backup rights or access to the script folder. Test by right-clicking the task in Task Scheduler and choosing Run, then read the log it writes.

Differential backups that will not restore

If a differential backup restores with an error about its base, someone or something took a full backup in between, for example another backup product or a manual backup. Use COPY_ONLY for any backup outside your main schedule, and check msdb.dbo.backupset for unexpected full backups.

Make failures visible and recoverable

• Retry automatically. Many failures are temporary: a locked file, a network blip, a busy destination. Retrying after a short delay, then longer ones, recovers most of them without anyone getting out of bed.
• Alert a person. A failure written to a log that nobody reads is the same as no backup. Send an email on failure, and ideally a daily summary on success, so silence is noticeable.
• Check the age of the newest backup, not just whether the last job succeeded. The query in step 1 does this.