Create a differential database backup

A differential backup contains only the data that has changed since the last full backup. The full backup serves as its base and includes all the data in the database at the time it was created. You can create multiple differential backups based on a single full backup. Each differential backup records all changes made after that full backup and is stored as a separate file.

This topic describes how to create a differential database backup in SQL Server by using the Backup wizard in dbForge Studio for SQL Server.

Limitations

• A differential backup requires a full backup as its base.
• When you create a new full backup, all subsequent differential backups are based on that new full backup.
• To restore from a differential backup, you must have both the full backup and the specific differential backup you want to restore.
• Differential backups don’t include transaction log changes.
• You can’t create a differential backup while the database is offline, read-only, or in restoring, suspect, or emergency mode.

Permissions

To create a differential database backup, you must have at least one of the following permissions or role memberships:

• BACKUP DATABASE permission.
• ALTER ANY DATABASE permission.
• Membership in the db_owner fixed database role.
• Membership in the sysadmin fixed server role.
• Membership in the db_backupoperator fixed database role.

Prerequisites

• Connect to the required server instance.
• Create at least one full database backup.

Back up a database

1. Open the Backup wizard in one of these ways:

• In the top menu, select Database > Tasks > Back Up.
• In Database Explorer, right-click the connection name or required database and select Tasks > Back Up.
• On the Start Page, select Administration > Back Up.

2. On the General page, configure the general backup options:

2.1. In Connection, select the server connection for the database you want to back up.

2.2. In Database, select the database you want to back up.

2.3. In Backup type, select Differential.

2.4. In Backup component, select Database.

2.5. In Backup to disk, click Add to change the path to the destination folder to store a backup file.

Tip

To remove a destination file, click Remove.

3. In the Browse Files dialog, select a destination folder to store the file:

3.1. In Select the file, select a backup file.

3.2. In Selected path, view the path to the backup file.

Note

The default path to store the backup file is
C:\Program Files\Microsoft SQL Server\<instance_name>\MSSQL\Backup\file_name.bak.

3.3. In FileName, keep the default name or enter a new one.

3.4. Click OK to save the changes.

4. On the Media options page, configure the media options:

• Under Overwrite Media, choose how the backup should be written to the media.
• Under Reliability, configure how errors should be handled.

5. On the Backup options page, configure the backup options:

5.1. In Name, specify the backup name.

5.2. In Description, specify the backup description.

5.3. Under Backup set will expire, select one of the following options:

• After: Specify the number of days after which the backup expires and can be overwritten.
• On: Specify the date when the backup expires.

5.4. Select Compress backup to reduce the size of the backup file, regardless of the server-level default.

6. To create an encrypted backup, select Encrypt backup, then select an encryption algorithm and a certificate or asymmetric key from a list.

Note

The Encrypt backup option is available when the Back up to a new media set and erase all existing backup sets option is selected.

Tip

It is recommended to back up your certificate or keys and store them in a different location from the backup you encrypted.

7. Click Back Up.

When the operation is complete, you can view its result on the Finish page.

8. To back up the database again, click Backup Again.

9. Click Finish to close the wizard.

For more information on configuring SQL Server backups on a Windows system, see How to Set Up Every Day Database Auto Backup in SQL Server.

Backup wizard options

Media options

The table provides a list of options available on the Media options page of the wizard.

Overwrite Media

Name	Description
Back up to the existing media set	Adds your backup to an existing media set, which can contain multiple backup sets.
Append to the existing backup set	Adds the new backup to the end of the current backup set. Existing backups are preserved.
Overwrite all existing backup sets	Replaces the current contents of the media set with new data. All previous backups on this set will be lost.
Check media set name and backup set expiration (checkbox)	Verifies the name and the expiration date of the backup sets.
Back up to a new media set and erase all existing backup sets	Creates a new media set, removing all existing backup sets on the media.
Media set name and Media set description	Specifies a name and description for the current media set.

Reliability

Name	Description
Verify backup when finished	Verifies the backup to ensure it can be read and restored correctly.
Perform checksum before writing to media	Adds a checksum for each page to detect corruption during the write operation.
Continue on error	Continues a backup operation if an error is encountered.

Backup options

The table provides a list of options available on the Backup options page of the wizard.

Backup set

Name	Description
Name	Specifies a backup name.
Description	Specifies a backup description.
Backup set expiration	Specifies when the backup set can be overwritten: After: Specify the number of days after which the backup expires and can be overwritten. On: Specify the date when the backup expires and can be overwritten.
Compress backup	Reduces the size of the backup file, regardless of the server-level default.

Encrypted options

Name	Description
Encrypt backup	Creates an encrypted backup to protect data from unauthorized access during storage and transfer.
Algorithm	Specifies an encryption algorithm.
Certificate or Asymmetric Key	Identifies a certificate or asymmetric key.