Database Backups 101

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

PHOTOGRAPHY DURING THE SESSION

Feel free to capture the moment! Taking pictures of the presentation during the session is perfectly fine.

SELFIES WITH THE SPEAKER

We encourage interaction! If you'd like a selfie with the speaker, don't hesitate to ask — most are happy to oblige during appropriate breaks.

RECORDING AND LIVE STREAMING

Recording or live streaming the session, in part or in full, is strictly prohibited. Thank you for respecting our content and speakers.

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

Super Hero Suit

Database Backups 101

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

Database Backups 101

 

Questions

What is a database backup?

A copy of your data captured at a point in time, so you can put the database back the way it was.

…and it only counts if you can restore it.

Database Backups 101

 

Questions

  • How many of you are DBA's?
     
  • How many of you have backups of all of your databases?
     
  • How many of you have restored one of those databases you backed up?

Database Backups 101

 

Questions

Why should you back up your databases?

Because that's what you were hired to do.

Database Backups 101

 

Questions

Why should you restore your backups?
 

 

So you don't get fired!

A backup you've never tested is a hope, not a recovery plan.

Database Backups 101

 

Backup Types

What Backup Types Are There?

  • Full — complete copy; the baseline for everything else
     
  • Copy Only — full-like copy that stays out of the chain
     
  • Differential — everything changed since the last full
     
  • Transaction Log — every change since the last log backup; enables point-in-time
     
  • VM Snapshot — whole-server image; all-or-nothing, no point-in-time

Database Backups 101

 

Recovery Models

What Backup Types are supported based on your Recovery Model

  • Full - Recovery Model: Full, Bulk-Logged, Simple
     
  • Copy Only - Recovery Model: Full, Bulk-Logged, Simple
     
  • Differential - Recovery Model: Full Only
     
  • Transaction - Recovery Model: Full, Bulk-Logged
     
  • VM Snapshot - Recovery Model: Full, Bulk-Logged, Simple

Database Backups 101

 

Recovery Model - Simple

Simple - Maximum Risk, No Point In Time Recovery

  • No point-in-time recovery
     
  • Log is auto-truncated — no log backups
     
  • Recover only to your last full or differential
     
  • Everything since that backup is gone
     
  • Fine for dev / read-only / easily rebuilt data

Database Backups 101

 

Recovery Model - Full/Bulk Logged

Full, Bulk Logged - Minimum Risk, Point in Time Recovery (RTO), Recovery Point (RPO)

  • Point-in-time recovery
     
  • Take regular transaction-log backups
     
  • Restore full → differential → logs
     
  • Recover to a specific second
     
  • The cost: you must manage log backups

Database Backups 101

 

Backup Type - Copy Only

  • Copy Only is essentially a Full backup, but...
     
  • No LSN - doesn't start a backup chain
     
  • No transaction continuity
     
  • It does NOT break LSN
     
  • Decide: How many files? Compression? Location — network / local / fast disk?

Database Backups 101

 

Backup Type - Full

  • Creation point for LSN
     
  • Required for point-in-time
     
  • Required for transaction logs
     
  • Baseline for differentials and transaction logs
     
  • Decide: How many files? Location — network / local / fast disk / Blob / S3?

Database Backups 101

 

Backup Type - Transaction Logs

  • Required for Full and Bulk Logged Recovery Models
     
  • Don't forget - The log file will expand - potentially filling up your log drive
     
  • Decide on your interval - more transactions, backup frequently, set schedule
     
  • Monitor transactions and file size - increase/decrease
     
  • Decide: Location - Fast Disk

Database Backups 101

 

VLFs

Manage your VLFs (log fragmentation)

  • The log is split into Virtual Log Files. One big growth = 8–16 VLFs; many tiny autogrowths pile up hundreds–thousands
     
  • Too many VLFs slows log backups, restores, and crash/startup recovery — SQL enumerates every VLF before recovery begins
     
  • Usual cause: small % or small fixed autogrowth on a busy log
     
  • Check the count: sys.dm_db_log_info (2016 SP2+/2017+) or DBCC LOGINFO
     
  • Fix: log backup → DBCC SHRINKFILE → regrow once via ALTER DATABASE … MODIFY FILE (SIZE = …); set autogrowth to a fixed chunk (e.g. 512 MB–1 GB)

Database Backups 101

 

VLFs

Pedro Lopes's VLF script (TigerToolbox)

/* Pedro Lopes (Microsoft) - aka.ms/sqlinsights
   TigerToolbox \ Fixing-VLFs \ Fix_VLFs.sql
   Surveys every DB, then PRINTs the shrink + regrow fix.
   Rule of thumb in the script: flag DBs with >= 50 VLFs. */

-- Quick check (SQL 2016 SP2+ / 2017+):
SELECT DB_NAME(d.database_id) AS [Database],
       COUNT(li.vlf_sequence_number) AS VLFs
FROM   sys.databases d
CROSS APPLY sys.dm_db_log_info(d.database_id) li
GROUP BY d.database_id
HAVING COUNT(li.vlf_sequence_number) >= 50
ORDER BY VLFs DESC;

Full script outputs Actual vs Potential VLFs and the exact SHRINKFILE + MODIFY FILE commands
https://github.com/microsoft/tigertoolbox/blob/master/Fixing-VLFs/Fix_VLFs.sql

Database Backups 101

 

Backup Retention

  • Short-term (disk): keep enough to meet your RPO/RTO — Ola @CleanupTime, e.g. 7–14 days
     
  • Long-term = GFS: daily diffs ~7 days → weekly fulls ~4–5 weeks → monthly fulls 6–12 months → yearly fulls for compliance (e.g. 7 years)
     
  • Cheap archive tiers: Azure Archive / Amazon S3 Glacier for the old stuff
     
  • Keep one copy immutable / WORM — ransomware can't delete what it can't touch
     
  • Cleaning MSDB history ≠ deleting the files — manage both, and re-test that old backups still restore

Retention & long-term backups

Database Backups 101

 

RPO vs RTO

RPO vs RTO — don't mix them up

  • RPO — Recovery Point Objective: how much data can you lose? Sets your log-backup interval.
     
  • RTO — Recovery Time Objective: how much downtime can you afford? Drives file count, disk speed & restore design.
     
  • Both are measured in time.
     
  • Smaller RPO = more frequent log backups. Smaller RTO = faster restores.
     
  • Verify and document from the business what they are willing to lose and how long they can afford to be down.

Database Backups 101

 

Software

  • SQL Server Maintenance Plans  - Not extremely customizable - Free
     
  • RedGate - Customizable - UI - Job Based - Costs
     
  • Idera SQL Safe  - Wouldn't Install without domain - UI - Costs
     
  • VM Snapshots - Not customizable - No point in time - All or nothing - entire server
     
  • dbatools (PowerShell) — free; Backup-DbaDatabase: full/diff/log, compression, encryption, striping, Azure blob, built-in verify
     
  • Ola Hallengren - Completely customizable - Free

Backup software options

Database Backups 101

 

Cons

  • SQL Server Maintenance Plans - Very Limited, Issues with upgrades, accounts
     
  • RedGate - Customizable - UI can be heavy at times due to records kept in MSDB
     
  • Idera SQL Safe - Wouldn't Install without a domain
     
  • VM Snapshots - No point in time, freezes IO
     
  • If you have multiple backup software, you will break LSNs
     
  • Ola Hallengren/dbatools - Completely customizable, can backup in other formats

…and their gotchas

Database Backups 101

 

Full Sample

USE [DBA];
GO
EXEC [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = 'C:\Files\DatabaseBackups',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@CleanupMode = 'AFTER_BACKUP',
@Compress = 'Y',
@CopyOnly = 'N',
@ChangeBackupType = 'Y',
@BackupSoftware = NULL,
@NumberOfFiles = 4,
@Description = 'Full Backup',
@DirectoryStructure = '{BackupType}{DirectorySeparator}{DatabaseName}{DirectorySeparator}',
@FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@CleanupTime = 336,
@LogToTable = 'Y';

Database Backups 101

 

Differential Sample

USE [DBA];
GO
EXEC [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = 'C:\Files\DatabaseBackups',
@BackupType = 'DIFF',
@Verify = 'Y',
@CheckSum = 'Y',
@CleanupMode = 'AFTER_BACKUP',
@Compress = 'Y',
@CopyOnly = 'N',
@ChangeBackupType = 'N',
@BackupSoftware = NULL,
@NumberOfFiles = 4,
@Description = 'Diff Backup',
@DirectoryStructure = '{BackupType}{DirectorySeparator}{DatabaseName}{DirectorySeparator}',
@FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@CleanupTime = 168,
@LogToTable = 'Y';

Database Backups 101

 

Transaction Sample

USE [DBA];
GO
EXEC [dbo].[DatabaseBackup]
@Databases = 'ALL_DATABASES',
@Directory = 'C:\Files\DatabaseBackups',
@BackupType = 'LOG',
@Verify = 'Y',
@CheckSum = 'Y',
@CleanupMode = 'AFTER_BACKUP',
@Compress = 'Y',
@BackupSoftware = NULL,
@NumberOfFiles = 1,
@Description = 'Tran Backup',
@DirectoryStructure = '{BackupType}{DirectorySeparator}{DatabaseName}{DirectorySeparator}',
@FileName = '{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@CleanupTime = 7,
@LogToTable = 'Y';

Database Backups 101

 

Performance

  • BUFFERCOUNT — number of I/O buffers; usually the biggest win (Ola @BufferCount)
     
  • MAXTRANSFERSIZE — largest write; default 1 MB, max 4 MB (Ola @MaxTransferSize)
     
  • BLOCKSIZE — 64 KB (65536) common for disk / URL (Ola @BlockSize)
     
  • Memory ≈ BUFFERCOUNT × MAXTRANSFERSIZE — too high starves the buffer pool. Tune, measure, repeat.
     
  • Stripe to multiple files across paths to parallelize — often the easiest gain

Backup performance tuning

Database Backups 101

 

Compression

  • Less data written = shorter backups (and faster restores) — costs CPU, watch busy servers
     
  • TDE gotcha: on encrypted DBs, compression barely works unless
    MAXTRANSFERSIZE > 65536 (decrypt → compress → re-encrypt)
     
  • SQL Server 2022: hardware offload via Intel QuickAssist (QAT) — ~2.3× faster
    Limitations: Enterprise Edition: hardware or software QAT · Standard Edition: software only
     
  • SQL Server 2025: NEW ZSTD Compression...!!

Backup performance tuning

Database Backups 101

 

Compression

  • New: ZStandard (ZSTD) — open source, from Meta / Yann Collet
  • Any edition, Windows or Linux, no special hardware
  • Pick ALGORITHM + LEVEL: LOW (default), MEDIUM, HIGH
  • MS claims 30–50% better than MS_XPRESS

ZSTD backup compression (SQL Server 2025)

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.Medium.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = MS_XPRESS, LEVEL = MEDIUM),
        STATS = 10,
        CHECKSUM;
GO

Database Backups 101

 

ZSTD Benchmark

ZSTD vs MS_XPRESS — my own test

Backup Time Size Savings Restore
No compression 38:45 258 GB — 8:22
MS_XPRESS · Low 27:28 128 GB 50.4% 9:21
MS_XPRESS · Medium 25:13 121 GB 53.1% 7:00
MS_XPRESS · High 30:18 121 GB 53.1% 7:01
ZSTD · Low 16:15 129 GB 50.0% 5:22
ZSTD · Medium 28:28 106 GB 58.9% 6:26
ZSTD · High 1:06:00 100 GB 61.2% 6:30

444 GB Stack Overflow · 16-core i9, 64 GB RAM, NVMe · each run verified. Winner: ZSTD Low.

Database Backups 101

Does striping to more files help?

Files Time Backup Type Compression Level Restore
2 9:34 Full ZSTD Low
4 9:20 Full ZSTD Low
8 9:36 Full ZSTD Low
12 8:42 Full ZSTD Low
16 8:38 Full ZSTD Low 4:07

444 GB Stack Overflow · ZSTD Low on every run · 16-core i9, 64 GB RAM, NVMe. Winner: 16 files at 8:38.

File Count

Database Backups 101

1 file vs 16 files: striping wins

Metric 1 File 16 Files Faster
Backup 16:15 8:38 47%
Restore 5:22 4:07 23%

Same 444 GB DB, ZSTD Low. Striping to 16 files nearly halved backup (16:15 to 8:38) and cut restore (5:22 to 4:07).

Backup + Restore

Database Backups 101

 

ZSTD Takeaways

  • ZSTD Low won: 16:15 backup (vs 38:45 uncompressed) and the fastest restore (5:22)
     
  • 50% smaller — same savings as MS_XPRESS, far faster
     
  • Higher level ≠ better: ZSTD High took over an hour for only ~11% more savings
     
  • Your data compresses differently — test the levels on your database

ZSTD takeaways

Database Backups 101

 

Restore

  • Take a tail-log backup before you restore — it captures the last transactions after the disaster
     
  • Restore full → differential → logs, each WITH NORECOVERY; only the final step is WITH RECOVERY
     
  • Use STOPAT = '2025-11-16 14:32:00' to recover to an exact second
     
  • STANDBY leaves the DB readable between log restores

Restore 101

A backup is only as good as your last successful restore.

Database Backups 101

 

Test Restores

  • VERIFY ≠ RESTORE: RESTORE VERIFYONLY only checks the media is readable — it does not prove the DB restores
     
  • Automate test restores to a scratch server (dbatools Restore-DbaDatabase / Ola Hallengren DatabaseRestore)
     
  • Encrypting backups? Back up the certificate / key or the backups are unrecoverable
     
  • 3-2-1: 3 copies, 2 media, 1 offsite — plus 1 immutable / air-gapped. SQL 2022 backs up to S3-compatible URLs

Test restores & protect the keys

Database Backups 101

 

Always

  • Always Checksum
     
  • Always Verify
     
  • If not using TDE, encrypt backups
     
  • Use Compression
     
  • Cleanup Old Backups
     
  • Add indexes to MSDB Backup tables

Database Backups 101

 

Always

  • Cleanup MSDB Backup Tables
     
  • Alert on Failures
     
  • Check and verify minimal VLFs by managing the Transaction Log size and auto growth
     
  • Run CheckDB on the Production Database to check for issues
     
  • Discuss and determine RPO/RTO and Retention with Business and Legal
     
  • Test your backup by restoring the database

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-overview-sql-server
 

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
 

https://www.jefftaylor.io/post/sql-server-2025-backups-new-zstd-compression
 

​https://www.jefftaylor.io/post/sql-server-2025-backup-compression-and-restore-review

https://github.com/microsoft/tigertoolbox/tree/master/Fixing-VLFs
 

https://ola.hallengren.com
 

https://dbatools.io

Resources

 

Let's talk backups — and restores.

Questions

 

Session Evaluation

Thank you!

Jeff Taylor

Thank you for attending my session today.
If you have any additional questions, please don't hesitate to reach out.

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io