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 ad­di­tion­al location. The WITH DIF­FER­EN­TIAL command can be used to save only the changes made since the last backup, con­serving storage space and time.

What is SQL BACKUP DATABASE?

The Struc­tured Query Language (SQL) is ideal for handling sensitive and extensive data records, which can be ef­fi­ciently 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 in­ter­rup­tion. This practice is highly re­com­men­ded for both business and personal use. With the SQL BACKUP DATABASE in­struc­tion, you can create a backup like this in just a few steps.

It’s important to save the backup on an ad­di­tion­al hard disk to ensure the data remains available even if the actual database is lost. The backup hard disk must have suf­fi­cient storage space. You can choose between a full backup and a dif­fer­en­tial backup. Here, we’ll explore options and their dif­fer­ences.

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 cor­res­pond­ing code looks like this:

BACKUP DATABASE name_of_database 
TO DISK = 'new_location';

name_of_database des­ig­nates 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 in­struc­tion

In practice, SQL BACKUP DATABASE could look like this:

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

Partial backup with WITH DIF­FER­EN­TIAL

If you’ve already created a backup, you can create a BACKUP WITH DIF­FER­EN­TIAL. This method saves only the changes made after the last backup, con­serving 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 DIF­FER­EN­TIAL would look like this:

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

Similar commands to SQL BACKUP DATABASE

An al­tern­at­ive to SQL BACKUP DATABASE is a Trans­ac­tion Log Backup. This method includes all changes made since the last trans­ac­tion 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 cor­res­pond­ing para­met­ers 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';