SQL - Backup Database

Nowadays, almost every organization uses a database to store information pke employee records, customer records, financial transactions, etc. It is very important to create backups of the database because there might be a chance of data loss due to power surges or disk crashes etc. Overall, regular database backups are essential for ensuring the long-term availabipty of critical data.

Backup database statement in SQL

To create a backup for an existing database, SQL provides us with a simple BACKUP DATABASE command.

Note − We should always back up the database onto a different disk other than the actual database. Even if the disk crashes, we will not lose our backup file along with the database.

Syntax

Following is the syntax of the BACKUP DATABASE command in SQL −

BACKUP DATABASE database_name
TO DISK =  filepath ;

Example

Firstly, let’s create a database in the SQL server using the following query −

SQL> CREATE DATABASE testDB;

Let’s verify whether the database “testDB” is created or not using the following query −

SQL> SELECT name
FROM sys.databases;

The database is successfully created in the SQL server.

+-------+
| name  |
+-------+
|master |
|tempdb |
|model  |
|msdb   |
|testDB |
+-------+

Now, let us try to create a backup file for the database “testDB” inside the “D” drive, named "DB_backup.bak" using the following query.

SQL> BACKUP DATABASE testDB
TO DISK =  D:DB_backup.bak 

Output

When we execute the above query, the output is obtained as follows −

Processed 344 pages for database  testDB , file  testDB  on file 1.
Processed 2 pages for database  testDB , file  testDB_log  on file 1.
BACKUP DATABASE successfully processed 346 pages in 0.011 seconds (245.383 MB/sec).

Backup Database with SQL DIFFERENTIAL Statement

The SQL Backup with a DIFFERENTIAL Statement is used to create a differential backup of the database. The differential backup contains only the changes made to the database since the last full backup. This type of backup is usually smaller in size compared to a full backup. Thus, it reduces the time to perform the backup.

Syntax

Following is the syntax for the backup database using DIFFERENTIAL Statement −

BACKUP DATABASE database_name
TO DISK =  filepath 
WITH DIFFERENTIAL;

Example

Let us look at an example using the DIFFERENTIAL Statement below −

SQL> BACKUP DATABASE testDB
TO DISK =  D:DB_backup.bak 
WITH DIFFERENTIAL;

Output

On executing the above query, the output is displayed as follows −

Processed 200 pages for database  testDB , file  testDB  on file 2.
Processed 2 pages for database  testDB , file  testDB_log  on file 2.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 202 pages in 0.011 seconds (143.110 MB/sec).