Skip to content
DBAExperts
SQL Server Backups & recovery

How to back up a database in SQL Server (BACKUP DATABASE)

Backups are your safety net. Here are the three backup types (full, differential, and log) with ready-to-use commands.

  1. 1

    Full backup

    Copies the entire database. It is the foundation of any strategy. Use COMPRESSION and CHECKSUM to save space and catch corruption.

    BACKUP DATABASE [MiBase]
TO DISK = N'D:\Backups\MiBase_full.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM,
     NAME = N'MiBase-Full';
  2. 2

    Differential backup

    Copies only what changed since the last full backup. It is faster and smaller. A prior full backup is required to restore it.

    BACKUP DATABASE [MiBase]
TO DISK = N'D:\Backups\MiBase_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT;
  3. 3

    Transaction log backup

    Only applies if the database uses the FULL or BULK_LOGGED recovery model. It enables point-in-time recovery.

    BACKUP LOG [MiBase]
TO DISK = N'D:\Backups\MiBase_log.trn'
WITH COMPRESSION, CHECKSUM, INIT;
  4. 4

    Verify the backup

    A backup that does not restore is worthless. Validate its integrity with RESTORE VERIFYONLY right after creating it.

    RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\MiBase_full.bak'
WITH CHECKSUM;

// common mistake

Log backups only work if the database is in the FULL or BULK_LOGGED recovery model. In SIMPLE, the log truncates on its own and you cannot do point-in-time recovery. Check the model with SELECT recovery_model_desc FROM sys.databases.

// when it's worth an expert

If you need a backup strategy with defined RPO/RTO, periodic restore testing, and monitoring, at dba.mx we deliver it at a fixed price: verified backups and tested restores, not just .bak files piling up.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.