Why this is a Sev1
If the server fails now, your recovery point is the last good backup, which may be days old. In FULL recovery, missing log backups also let the transaction log grow until the disk fills.
1. Measure the exposure
SELECT d.name, d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_full;
Check log growth risk at the same time:
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE log_reuse_wait_desc = 'LOG_BACKUP';
2. Find why backups fail
EXEC msdb.dbo.sp_help_jobhistory @job_name = N'DatabaseBackup - USER_DATABASES - FULL', @mode = N'FULL';
EXEC sys.xp_readerrorlog 0, 1, N'BACKUP failed';
| Error text | Cause | Fix |
|---|---|---|
Operating system error 5 (Access is denied) | Service account lost rights on the share | Grant the SQL Server service account write access |
Operating system error 112 / disk full | Target out of space | Clean old files, extend, or new target |
Operating system error 53/64 | Share unreachable | DNS, firewall, NAS outage |
The operating system returned the error '1450' / timeouts | Storage or network slow | Use a different target, add BUFFERCOUNT/MAXTRANSFERSIZE tuning |
| Job not running at all | Agent stopped, job disabled, schedule removed | Start Agent, enable job |
3. Take a good backup now
Back up to local disk or another working target first, then copy off-server:
BACKUP DATABASE [Orders]
TO DISK = N'E:\EmergencyBackup\Orders_FULL_20261031.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;
BACKUP LOG [Orders]
TO DISK = N'E:\EmergencyBackup\Orders_LOG_20261031_1.trn'
WITH CHECKSUM, COMPRESSION;
For an Azure VM, backup to URL is often the fastest working target:
BACKUP DATABASE [Orders]
TO URL = N'https://stsqlbackup.blob.core.windows.net/backups/Orders_FULL_20261031.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;
(Requires a SAS credential named after the container URL.)
Warning
If another tool (Azure Backup, Veeam, a third-party agent) also backs up these databases, an extra full backup can disturb its differential base. Add
COPY_ONLYto emergency full backups if that tool owns the chain, and tell its owner.
4. Prove it restores
A backup you haven't restored is a hope, not a backup.
RESTORE VERIFYONLY FROM DISK = N'E:\EmergencyBackup\Orders_FULL_20261031.bak' WITH CHECKSUM;
Better, restore to a test instance and run DBCC CHECKDB:
RESTORE DATABASE [Orders_Verify]
FROM DISK = N'\\fileserver\sqlbackup\Orders_FULL_20261031.bak'
WITH MOVE N'Orders' TO N'F:\Data\Orders_Verify.mdf',
MOVE N'Orders_log' TO N'G:\Log\Orders_Verify.ldf', STATS = 10;
DBCC CHECKDB ([Orders_Verify]) WITH NO_INFOMSGS;
5. Fix and resume the schedule
- Fix the root cause (permissions, space, share).
- Re-enable jobs and run them once manually while you watch.
- Confirm log backups run on schedule and
log_reuse_wait_descclears.
6. Report the gap
Record for the incident: databases affected, the time range without backups and the actual risk window. Some organisations must report this to audit.
Prevent it next time
- Alert on age of last backup per database (for example full > 26 h, log > 30 min), not just on job failure.
- Make the Agent job notify an on-call operator on failure.
- Restore-test backups automatically each week.
- Monitor backup target free space.