SQL Server Database Backups: BACKUP DATABASE

Regular backups are essential for protecting your SQL Server databases from data loss. SQL Server provides commands to create both full and differential backups.

BACKUP DATABASE: Full Backups

A full backup creates a complete copy of your database at a specific point in time. It's the most straightforward backup method, but it takes longer than a differential backup because the entire database is copied.

Syntax

BACKUP DATABASE databasename
TO DISK = 'filepath';
      

Example: Creating a Full Backup

This example creates a full backup of the database "testDB" to a file named "testDB.bak" on the D: drive. It's best practice to back up to a different drive than the one containing your database, in case of a drive failure.

BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak';
      

BACKUP DATABASE WITH DIFFERENTIAL: Differential Backups

A differential backup only copies changes made since the last *full* backup. This is significantly faster than a full backup, especially if only a small portion of the database has changed.

Syntax

BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;
      

Example: Creating a Differential Backup

This creates a differential backup of the "testDB" database.

BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB_diff.bak' WITH DIFFERENTIAL;