SQL BACKUP DATABASE Statement

–- backup database to the given path
BACKUP DATABASE my_db
TO DISK = 'C:\my_db_backup.bak';

Types of Backups in SQL

There are three types of backups in SQL. They are:

• Full Backup
• Differential Backup
• Transaction Log (T-log) backup

Full Backup

A full backup is a complete backup of an SQL server database.

Its syntax is:

BACKUP DATABASE database_name
TO medium = 'path\file_name';

Here,

• database_name is the name of the database to be backed up
• medium refers to the storage medium such as disk, tape or url
• path refers to the folder where the backup file should be stored
• file_name is the name given to the backup file

For example,

–- backup database to the given path
BACKUP DATABASE my_db
TO DISK = 'C:\my_db_backup.bak';

Here, the SQL command creates a backup file of the my_db database (named my_db_backup.bak) inside the C drive.

Note: It's a common convention to use the .bak file extension for database backup files, however, it's not mandatory.

Differential Backup

In SQL, you can also backup only the new changes compared to the last full backup by using the WITH DIFFERENTIAL command. For example,

-- backup the changes made to the database
BACKUP DATABASE my_db
TO DISK = 'C:\my_db_backup.bak'
WITH DIFFERENTIAL;

Here, the SQL command appends only the new changes to the previous backup file. Hence, this command may work faster.

Transaction Log Backup

A transaction log backup captures all the changes made to the database since the last transaction log backup or the creation of the database.

It allows you to create a point-in-time backup of your database and provides a way to recover the database to a specific point in time in case of a failure.

It's syntax is,

BACKUP LOG database_name
TO medium = 'path\filename';

For example,

-- backup database log to the given path
BACKUP LOG my_db
TO DISK = 'C:\my_db_backup.bak';

Restore Database From Backup

To restore a backup file to a database management system, we can use the RESTORE DATABASE command. For example,

-- restore database from given path
RESTORE DATABASE my_db
FROM DISK = 'C:\my_db_backup.bak';

Here, the SQL command restores the my_db_backup.bak file to the database named my_db.