How to use SQL BACKUP DATABASE to secure your database against data loss

You can use the SQL BACKUP DATABASE statement to create a backup of a database and store it in an additional location. The WITH DIFFERENTIAL command can be used to save only the changes made since the last backup, conserving storage space and time.

What is SQL BACKUP DATABASE?

The Structured Query Language (SQL) is ideal for handling sensitive and extensive data records, which can be efficiently and easily stored and managed with the language. An important tool for ensuring the security of data is by creating a backup of an existing and active database. This protects you against data loss or hard disk damage and allows you to continue working with the stored data without interruption. This practice is highly recommended for both business and personal use. With the SQL BACKUP DATABASE instruction, you can create a backup like this in just a few steps.

It’s important to save the backup on an additional hard disk to ensure the data remains available even if the actual database is lost. The backup hard disk must have sufficient storage space. You can choose between a full backup and a differential backup. Here, we’ll explore options and their differences.

Syntax and function

The syntax of SQL BACKUP DATABASE consists of only two lines. You only need to specify the name of the database and the new storage location for the backup. The corresponding code looks like this:

BACKUP DATABASE name_of_database 
TO DISK = 'new_location';

name_of_database designates the database for which you want to create a backup. Use TO DISK to specify where the data should be stored.

Example for using the instruction

In practice, SQL BACKUP DATABASE could look like this:

BACKUP DATABASE customer list 
TO DISK = 'C:\backups\new_backup.bak';

Partial backup with WITH DIFFERENTIAL

If you’ve already created a backup, you can create a BACKUP WITH DIFFERENTIAL. This method saves only the changes made after the last backup, conserving time and storage space, since large parts of the database have already been saved. It is important that the storage location of the new backup matches that of the original backup. The syntax for this is as follows:

BACKUP DATABASE name_of_database 
TO DISK = 'new_location'
WITH DIFFERENTIAL;

Applied to our example above, the SQL BACKUP DATABASE operation with the SQL command WITH DIFFERENTIAL would look like this:

BACKUP DATABASE customer list 
TO DISK = 'C:\backups\new_backup.bak'
WITH DIFFERENTIAL;

Similar commands to SQL BACKUP DATABASE

An alternative to SQL BACKUP DATABASE is a Transaction Log Backup. This method includes all changes made since the last transaction log backup or the creation of the original database, allowing you to return to a specific database state or access it at any time. The syntax is:

BACKUP LOG name_of_database 
TO DISK = 'new_location';

For our example, the corresponding parameters are:

BACKUP LOG customer list 
TO DISK = 'C:\backups\new_backup.bak';

To restore a backup, you need the RESTORE DATABASE command:

RESTORE DATABASE name_of_database 
FROM DISK = 'new_location';

You don’t specify where the backup is to be called up, but rather the storage location of the backup. In our example, the command would look like this:

RESTORE DATABASE customer list 
FROM DISK = 'C:\backups\new_backup.bak';