What is the SQL BACKUP DATABASE statement for the SQL Server?

Overview

There are four types of backup in the SQL server: full backup, differential backup, incremental backup, and mirror backup. In this shot, we discuss full and differential backups.

Full backup

Using BACKUP DATABASE

The BACKUP DATABASE statement in SQL Server is creates a complete backup of an existing database.

Syntax

BACKUP DATABASE database_name

TO DISK = 'directory or path';

Example

In the code snippet below, we see SQL's BACKUP DATABASE statement:

CREATE DATABASE company;
GO
use company;
-- creating a table
CREATE table worker(
worker_id varchar(10),
first_name varchar(100), 
last_name varchar(100),
salary int,
department varchar(10));
-- inserting into worker table
INSERT INTO worker values ("001", "Monika", "Arora", 100000, "HR");
INSERT INTO worker values ("002", "Niharika", "Verma", 80000, "Admin");
INSERT INTO worker values ("003", "Vishal", "Singhal", 300000, "HR");
INSERT INTO worker values ("004", "Amitabh", "Singh", 500000, "Admin");
-- backup company database to current working directory
BACKUP DATABASE company
TO DISK = './data.bak';

Explanation

  • Lines 1–3: We create a company database and use it for the current program.
  • Lines 5–10: We create a worker table with the fields: worker_id, first_name, last_name, salary, and department.
  • Lines 12–15: We insert four entries into the worker table.
  • Line 17–18: We use BACKUP DATABASE to backup the company database at the current working directory.

Differential backup

Using WITH DIFFERENTIAL

A differential backup is based on a full backup—called the differential base—and only captures the segments of the database that have been changed since the full backup.

Syntax

BACKUP DATABASE database_name

TO DISK = 'directory or path'

WITH DIFFERENTIAL;

Example

In the code snippet below, we see SQL's BACKUP DATABASE WITH DIFFERENTIAL statement:

CREATE DATABASE company;
GO
use company;
-- creating a table
CREATE table worker(
worker_id varchar(10),
first_name varchar(100), 
last_name varchar(100),
salary int,
department varchar(10));
-- inserting into the worker table
INSERT INTO worker values ("001", "Monika", "Arora", 100000, "HR");
INSERT INTO worker values ("002", "Niharika", "Verma", 80000, "Admin");
INSERT INTO worker values ("003", "Vishal", "Singhal", 300000, "HR");
INSERT INTO worker values ("004", "Amitabh", "Singh", 500000, "Admin");
-- backup company database to current working directory
BACKUP DATABASE company
TO DISK = './data.bak';
-- inserting a new entry to the worker table
INSERT INTO worker values ("005", "Raj", "Kumar", 70000, "Admin");
-- backup with differential
BACKUP DATABASE company
TO DISK = './data.bak'
WITH DIFFERENTIAL;

Explanation

  • Lines 17–18: We create a full backup for the company database. This is required as we need a base backup to use the WITH DIFFERENTIAL command.
  • Line 20: We insert a new entry to the worker table.
  • Lines 22–24: We back up the updated company database using the WITH DIFFERENTIAL command. This only captures the changes made since the last full backup.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)