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.

Backup Types	Source	Backup Files	Backup Speed	Space Taken	Restore	Restore Speed	Suggest Frequency
Full Backup	Entire database	All files	Low	High	Last full backup	High	Once a week
Differential Backup	Last full backup	Only the changed files since last full	Medium	Memium-High	Last full backup + last differential backup	High	Once a day
Transaction Log Backup	Last full or transaction log backup	Only the logs since last backup	High	Low-High	Last full backup + Last differential backup + all transaction log backups	Low	According to your needs

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 Centralized Backupper Database, which I'm going to introduce next, is a convenient and error-free way to achieve automatic full or 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 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 Centralized Backupper Database to centrally control all the laptops, workstations, and servers within LAN from one computer, to back up multiple databases on them. When restoring, you can choose to restore the backup to the local server, or to another server of the controlled computers remotely. For enterprises, it really promote efficiency and reduce manual errors.

Here’s a 30-day free trial for you:

To properly use Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.

For those computers which download client program manually, you need to request control over them for further operations.

Click Computers > Uncontrolled Computers, select single or multiple client computers and click Request Control on the upper bar.

A permission request window will pop up on their computers, once all permissions are received, you can manage them at any time.

How to Create SQL Server Differential Backup via Centralized Backupper Software

1. Click Tasks > New Task and select SQL Server Backup to create a new backup task.

In pop-up window, you can change the task name by moving your cursor to the task name and click it.

Then follow the 3 steps beneath the task name.

2. Step 1, click Add Computers to add the controlled client computer you want to back up with.

3. Step 2, click Add to select single or multiple SQL instances and databases you want to back up.

4. Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

In this step, click Settings you can enable encryption, email notification, and compression for backups.

To further save disk space, it provides 2 compression levels. The higher the level is, the smaller the files are.

5. Click Schedule next to Settings, and select to run this task Once, Daily, Weekly, or Monthly.

Each option you can select specific days and start time. You can also change it after created.

6. Click Advanced in Schedule Settings window, and choose to perform Differential Backup.

7. Now you can click Start Backup to select Only create the task, or Create and execute the task.

Once created, the tasks will be listed in Tasks separately for further edit or restore. When restoring, it will list all the backups, and distinguish them by the backup type and dates in their names.

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 Centralized Backupper Database Edition can greatly help you.

Except for that, this software can also enable you to remotely control all computers within LAN on one computer. You can back up the SQL Server Databases, Files, Systems, Disks, even Partitions on their computers.