How to Create SQL Server Differential Backup in 3 Ways?

If you modify your database subset frequently and you have a huge SQL Server database, then the differential backup is a great choice for you to save storage and backup time. In this article, I’ll compare it to the rest backup types in SQL Server and introduce 3 ways to create the differential backup.

There are 3 backup types in SQL Server generally: Full Backup, Differential Backup, and Transaction Log Backup. Some may be confused about when to use and how to choose the backup types in the database, here’s a quick overview of their definitions and comparisons.

Full Backup: includes the whole database content and part of the transaction log to recover to the state when it was taken.

Differential Backup: captures only the data modified since the last full backup. You can perform it for any recovery model (full, bulk-logged, or simple).

Transaction Log Backup: collects all the transaction log records from the most recent full backup or transaction log backup to restore the database to a specific point in time.

Note: the optimal backup interval of a backup type basically depends on data importance, database size, and server workload.

Create a SQL Server differential backup in 3 ways

Before you begin:

• Make sure you have created a full backup as the differential base before the next steps.

• Log in with sysadmin, db_owner, and db_backupoperator roles with BACKUP DATABASE and BACKUP LOG permissions.

• SQL Server needs read and write permissions of the device, and the database account must have write permissions.

• The backup statement is not contained in an explicit or implicit transaction.

Recommendation:

Taking a new full backup at set intervals for the new differential base is suggested for the time required to restore a differential backup will increase in its size, for instance, you could take a weekly full backup followed by a series of differential backups within the week.

Way 1: Create a differential backup with SQL Server Management Studio (SSMS)

1.  Start SSMS and connect it to your instance in Object Explorer.

2.  Right-click the database for backup, and click Tasks > Back Up….

3.  In the pop-up window Source section, choose Differential as the Backup type. Do not check Copy-only Backup.

4.  Click Database for the Backup component. Optimally, change the backup set name in the Name text box and enter the description in the Description text box.

5.  Specify the backup set expiration data After a specific number of days (default) or click On to enter the date.

6.  On the Destination page, click Disk> Add… to specify the backup location, then name the backup with file name + .bak suffix. Click OK.

7.  Click Options in the Select a page pane to set the advanced options. For example, click Append to the existing backup set or Overwrite all existing backup sets to back up to the existing media set; Or enter a name in the New media set name text box to backup to a new media set.

Way 2:  Create a differential backup with Transact-SQL

1.  Start SSMS and connect it to your instance.

2.  Click New Query and input the commands.

USE master
GO
BACKUP DATABASE databasename TO DISK = 'backuplocationfilename.bak' WITH DIFFERENTIAL
GO

3.  Click Execute.

You can refer to Method 2 in the article on Restoring SQL Database from Bak File if you wish to restore a database using T-SQL commands from a differential backup.

Way 3: Create differential backup easily with Vinchin Backup & Recovery

You might need to automate differential backup procedures to prevent data loss due to forgetting to back up databases on time. Vinchin Backup & Recovery is a worry-free backup solution for multiple mainstream databases such as Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, 10 virtualizations, Linux & Windows Servers, and NAS.

With the software, you can automate the backup and recovery process, and you don’t have to restore the full backup before if you want to restore a differential backup. It also supports the hot backup and database consistency auto-check of SQL Server for data integrity verification.

You can have a 60-day full-featured free trial now below:

To create a SQL Server database backup job, install the database backup agent on the login screen, follow the setup wizard, and register the agent in the Vinchin server.

1.  Select a backup source.

Go to Database Backup > Backup page, select the database backup agent on the left column, expand the instance and choose the backup source(s).

2.  Specify the backup destination.

Select a backup node for the backup data in the Target Node dropdown list and select the storage that belongs to the chosen backup node in the Target Storage list.

3.  Set up backup strategies.

Set up the job as Backup as scheduled or Once-off backup in the Mode dropdown options. In the Schedule section, check the Full Backup and Differential Backup. You could also configure data compression, data retention policy, and speed limit under General Strategy.

Encrypt the transmission for safe data transfer and enable other advanced options optionally.

4.  Review all the settings and click Submit.

Final Thoughts

Differential backup in SQL Server could be a great choice to save storage space and backup time if the database has a massive amount volume of data. In this blog, I introduce 3 ways to create the differential backup using SSMS, Transact-SQL, and a professional third-party tool Vinchin Backup & Recovery. The solution facilitates greatly automatic scheduled SQL Server backup with multiple strategies.

You can also centrally manage the backups of 4 databases it supported in one web console and save costs.