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.
BACKUP DATABASE
The BACKUP DATABASE
statement in SQL Server is creates a complete backup of an existing database.
BACKUP DATABASE database_nameTO DISK = 'directory or path';
In the code snippet below, we see SQL's BACKUP DATABASE
statement:
CREATE DATABASE company;GOuse company;-- creating a tableCREATE table worker(worker_id varchar(10),first_name varchar(100),last_name varchar(100),salary int,department varchar(10));-- inserting into worker tableINSERT 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 directoryBACKUP DATABASE companyTO DISK = './data.bak';
company
database and use it for the current program.worker
table with the fields: worker_id
, first_name
, last_name
, salary
, and department
.worker
table.BACKUP DATABASE
to backup the company
database at the current working directory.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.
BACKUP DATABASE database_nameTO DISK = 'directory or path'WITH DIFFERENTIAL;
In the code snippet below, we see SQL's BACKUP DATABASE WITH DIFFERENTIAL
statement:
CREATE DATABASE company;GOuse company;-- creating a tableCREATE table worker(worker_id varchar(10),first_name varchar(100),last_name varchar(100),salary int,department varchar(10));-- inserting into the worker tableINSERT 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 directoryBACKUP DATABASE companyTO DISK = './data.bak';-- inserting a new entry to the worker tableINSERT INTO worker values ("005", "Raj", "Kumar", 70000, "Admin");-- backup with differentialBACKUP DATABASE companyTO DISK = './data.bak'WITH DIFFERENTIAL;
company
database. This is required as we need a base backup to use the WITH DIFFERENTIAL
command.worker
table.company
database using the WITH DIFFERENTIAL
command. This only captures the changes made since the last full backup.