3 Quick Ways to Create A SQL Server Differential Backup

SQL Server Differential Backup vs Full Backup vs Transaction Log Backup

Basically, there are 3 types of SQL Server backup , full backup, differential backup, and transaction log backup. In order to better protect our data in SQL Server database from accidental loss, we should choose the suitable type according to our own needs.

• Full Backup: A full Backup is the complete copy of the entire database, containing all the objects and transaction logs needed to restore to the state at the time it was taken.
• Differential Backup: A differential backup only captures the data changed since the last full backup. The amount of backup time and space it takes depends on how much you changed.
• Transaction Log Backup: A Transaction log backup captures all the transaction log records from the last full or transaction log backup, to restore a database to a particular point-in-time before the failure has occurred.

Here is a table comparing their differences, advantages and disadvantages.

SQL Server Differential Backup Best Practices (3 Methods)

In this article I will introduce you 3 simple and quick ways to create a differential backup in SQL Server. Before you begin, please make sure you’ve created a new full backup as the differential base. 

*In practice, differential backups are often implemented with schedules to continuously protect the database. If you want to do this, the last way can help you schedule differential backup very easily.

Method 1. Select Differential Backup option in SSMS GUI

1. Launch SQL Server Management Studio (SSMS) and connect to your instance.

2. Right-click the database name you want to back up, and select Tasks > Back Up…. A setup window will pop up.

3.In Source section, select Differential as the Backup type. Remember not to check Copy-only Backup.

4.In Destination section, select Disk and click Add… to specify a location where you want to store the backup.

In the pop-up window, you need to name this backup with the suffix .bak in File name. For example: mydb.bak

5.Then you can click OK to execute this task. A message will pop up when completed, and you can find the backup file in destination location.

✎Related Tips:
Recovery model: You can perform a differential backup for any recovery model (full, bulk-logged, or simple). There are concerns about performing SQL Server differential backup in simple recovery model, in which you cannot take transaction log backup. In fact it doesn't matter, because it only requires your most recent FULL backup and the most recent DIFFERENTIAL backup.
Copy-only Backup: A copy-only backup won't break the differential chain. You cannot create a differential backup with copy-only.
Backup set will expire: To have the backup set expire after a specific number of days or on a specific date, then they can be overwritten.

Method 2. Create Differential Backup with T-SQL Commands

1. Launch SSMS and connect to your instance.

2. Click New Query on the upper bar and input the following T-SQL Commands:

USE master
GO
BACKUP DATABASE databasename TO DISK = 'filepath\filename.bak' WITH DIFFERENTIAL
GO

Note:
In “databasename” part you should type the database name you want to back up.
In “filepath\filename.bak” part you should type the location where you want to store this backup, and the name of the backup file with a .bak suffix.

3. Then click Execute to create the differential backup.

If you want to restore database from a differential backup with T-SQL commands, you can refer to Method 2 in the article about Restore SQL Database from Bak File.

Method 3. Use Professional Tool to Create Automatic SQL Differential Backup

To avoid data loss due to forgetting to back up databases in time, you may need to automate differential backup tasks. AOMEI Cyber Backup, which I'm going to introduce next, is a convenient and error-free way to achieve automatic full / incremental / differential backups of multiple databases.

Except for automate backups, when you want to restore SQL databases, it will list all the backups with backup type and dates in their names, so you won't mess up full backups with differential backups.  Besides, when you're restoring a differential backup, you don't have to restore the full backup in advance, which makes it more convenient.

You can also use AOMEI Cyber Backup to centrally backup VMware or Hyper-V virtual machines within LAN.

Easy-to-use: With the intuitive interface, it's easy to configure SQL backups without script.
Flexible Backup: Choose flexible backup methods and able to backup all the devices within LAN.
Centralized Backup: Schedule backups of SQL server database and run it automatically.

Here’s the perpetual free software to backup Microsoft SQL 2005-2022, including SQL Express.

How to Create SQL Server Differential Backup via Cyber Backup

✍ To properly use AOMEI Cyber Backup, you should note these prerequisites for Microsoft SQL backup:
• Computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed
• Local disk or network share to store backup files

1. Access to "Source Device" > "​​​​​​​Add Microsoft SQL". If the database exists and the version is supported, it will appear automatically. Otherwise, you can click "​​​​​​​Add Microsoft SQL" > "​​​​​​​Download proxy program", and install the program on the device with SQL Server installed. Then click "Already installed proxy" and select the proxies you want to add.

2. Click "..."-->"Authentication" to validate the database instance. You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".

3. Go to create a differential SQL server database backup as following:

▪ Click "Backup Task" > "Create New Task" to backup your SQL databases.

▪ Choose backup type as "Microsoft SQL Backup", and you could also try virtual machine Backup.

▪ Enter a name for the backup task, then go to select the databases you want to backup and specify a location as a storage end.

▪ Select backup methods as full/incremental/differential and specify the backup time as daily/weekly/monthly. For example, you can specify that a full backup is performed every Monday and only differential backups are performed every other day.

4. Click"Start backup".

Summary

If you have a high volume of data in your SQL Server database, creating differential backups can be a good solution to save disk space. In this article, I introduced 3 ways to perform SQL Server differential backup.

However, if you are afraid you may forget to regular back your databases up, it can be better if you schedule SQL backup tasks. AOMEI Cyber Backup can greatly help you. Except for that, this software can also enable you to remotely backup VMware and Hyper-V virtual machines.