SQL BACKUP DATABASE

Previous
Quiz
Next

The SQL BACKUP DATABASE Statement

The SQL BACKUP DATABASE statement is used to create a backup copy of an existing database. A backup is a safety copy of your database that you can restore later in case something goes wrong, such as accidental deletion, data corruption, or server failure.

For example, if you are working on a website or system that stores user profiles, transactions, or inventory, backing up your database protects that important information.

  • The BACKUP DATABASE command works in SQL Server. It does not apply to MySQL or PostgreSQL, which use different methods like command-line tools for backups (e.g., mysqldump, phpMyAdmin Export).
  • Always choose a secure and reliable location to store your backup files.
  • You can schedule regular backups using SQL Server Agent or external scripts.

Syntax

Following is the syntax to back up a database in SQL Server:

BACKUP DATABASE database_name
TO DISK = 'file_path.bak';

Here,

  • BACKUP DATABASE: This is the SQL command used to back up the database.
  • database_name: The name of the database you want to back up.
  • TO DISK: Specifies that the backup will be written to a disk file.
  • 'file_path.bak': The complete path and filename (with .bak extension) where the backup will be stored.

Example

Let us back up a database named testDB to a file named testDB_backup.bak located in the D:\Backups folder:

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

After executing the above command in SQL Server, you will see an output similar to the following:

Processed 352 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 354 pages in 0.030 seconds (92.057 MB/sec).

SQL BACKUP DATABASE with the WITH Clause

SQL Server provides additional options to customize the backup process using the WITH clause. A commonly used option is INIT, which tells SQL Server to overwrite any existing backup file.

Syntax

Following is the syntax to use WITH INIT to replace an existing backup file:

BACKUP DATABASE testDB
TO DISK = 'D:\Backups\filename_backup.bak'
WITH INIT;

Example

In this example, we are backing up the testDB database and overwriting any existing backup file:

BACKUP DATABASE testDB
TO DISK = 'D:\Backups\testDB_backup.bak'
WITH INIT;

The output looks something like this:

Processed 352 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 354 pages in 0.023 seconds (120.074 MB/sec).

Other Common WITH Options

Some other commonly used options with the WITH clause are:

  • FORMAT: Formats the backup media (mainly used when backing up to tapes).
  • COMPRESSION: Reduces the size of the backup file (if supported by your SQL Server edition).
  • STATS = 10: Displays progress updates for every 10% completed during the backup process.

Types of Backups in SQL

In SQL Server, there are three main types of backups you can perform. Each type helps to protect your data in a different way and allows you to restore it depending on your needs. The three types are as follows:

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

Full Backup

A Full Backup in SQL saves a complete copy of the entire database. It includes all tables, data, and objects at the time of the backup. This backup can fully restore the database if needed. It is the most complete type of backup.

Syntax

Following is the syntax to take a full backup of a database in SQL Server:

BACKUP DATABASE database_name 
TO DISK = 'file_path.bak';

Example

In this example, we create a full backup of the testDB database and store it in the D:\Backups folder:

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

We get the output as shown below:

Processed 376 pages for database 'testDB', file 'testDB' on file 2.
Processed 2 pages for database 'testDB', file 'testDB_log' on file 2.
BACKUP DATABASE successfully processed 378 pages in 0.025 seconds (117.968 MB/sec).

Differential Backup

A Differential Backup in SQL saves only the data that has changed since the last full backup. It is faster and smaller than a full backup, but it depends on the last full backup to restore the database completely.

Syntax

Following is the syntax to take a differential backup of a database in SQL Server:

 
BACKUP DATABASE database_name 
TO DISK = 'file_path.bak' WITH DIFFERENTIAL;

Example

In this example, we create a differential backup of the testDB database and store it in the D:\Backups folder:

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

We get the output as shown below:

 
Processed 56 pages for database 'testDB', file 'testDB' on file 1.
Processed 2 pages for database 'testDB', file 'testDB_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 58 pages in 0.016 seconds (28.076 MB/sec).

Transaction Log (T-log) Backup

A Transaction Log (T-log) Backup in SQL saves a record of all the changes (called transactions) made to the database since the last time a log backup was taken. This type of backup is important because:

  • It lets you restore the database to a specific point in time, like right before a mistake or crash happened.
  • It prevents the transaction log file from growing too large, which helps keep the system running smoothly.

Syntax

Following is the syntax to take a transaction log backup of a database in SQL Server:

 
BACKUP LOG database_name 
TO DISK = 'file_path.trn';

Example

In this example, we create a transaction log backup of the testDB database and store it in the D:\Backups folder:

 
BACKUP LOG testDB 
TO DISK = 'D:\Backups\testDB_log.trn';

We get the output as shown below:

 
Processed 2 pages for database 'testDB', file 'testDB_log' on file 4. 
BACKUP LOG successfully processed 2 pages in 0.002 seconds (5.234 MB/sec).
Print Page
Previous
Next
Advertisements