Backup Operations

SQL Server backup operations protect your data by creating a copy of the database, transaction log, or filegroup. Backups can be full, differential, or transaction‑log based, enabling point‑in‑time recovery and disaster‑recovery scenarios.

Backup Types

TypeDescriptionTypical Use‑Case
FullCopies the entire database.Initial backup and periodic full copies.
DifferentialBacks up changes since the last full backup.Frequent backups with smaller size.
Transaction LogRecords all transactions since the last log backup.Point‑in‑time restore, high‑availability.
File/FilegroupTargets specific files or filegroups.Large databases with selective recovery needs.
Copy‑OnlyCreates a backup without affecting the backup chain.Ad‑hoc backup for testing or reporting.

Backup Commands

SQL Server provides T‑SQL BACKUP statements and SMO/PowerShell cmdlets. Below is the core syntax:

BACKUP DATABASE [database_name]
TO { DISK = 'path\file.bak' | URL = 'https://storage.blob.core.windows.net/container/file.bak' }
    [ WITH
        { INIT | NOINIT |
          COMPRESSION |
          STATS = integer |
          DESCRIPTION = 'text' |
          NAME = 'backup_set_name' |
          COPY_ONLY |
          CHECKSUM |
          ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = cert_name ) } ];

Examples

Full database backup with compression and progress display:

BACKUP DATABASE AdventureWorks2019
TO DISK = 'C:\Backups\AdventureWorks_Full.bak'
WITH COMPRESSION, STATS = 10;

Transaction‑log backup to Azure Blob Storage:

BACKUP LOG AdventureWorks2019
TO URL = 'https://myaccount.blob.core.windows.net/backups/AdventureWorks_Log.trn'
WITH COMPRESSION, STATS = 5, ENCRYPTION (
    ALGORITHM = AES_256,
    SERVER CERTIFICATE = MyBackupCert
);

Copy‑only differential backup:

BACKUP DATABASE AdventureWorks2019
TO DISK = 'C:\Backups\AdventureWorks_Diff_CopyOnly.bak'
WITH DIFFERENTIAL, COPY_ONLY, STATS = 5;

Best Practices