If you have a huge SQL Server database, differential backups can be a very good solution to save disk space and backup time. In this article I will compare it to the other 2 of 3 main backup types, and introduce you 3 quick ways to create a SQL Server differential 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.
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 |
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.
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 the 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.
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.
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 SQL Server databases.
Except for automate backups, it's easy to restore a SQL Server differential backup, which is very helpful when you have corrupted database due to system crash or other issues.
|
✨Easy-to-use: With the intuitive interface, it's easy to configure SQL backups without script. |
💡Comprehensive SQL Backup Backup Microsoft SQL databases and all objects, including configurations, architectures, logs, authentications, commands, clusters, etc. |
|
🚩Centralized Backup: With the user-friendly console, centrally backup multiple remote MS SQL databases from one location. |
🎇Instant SQL Recovery: Given our fastest backup decompression and recovery technology, businesses can execute SQL data recovery in a very short time. |
Here’s the perpetual free software to back up Microsoft SQL 2005-2022, including SQL Express. Click the button below to download AOMEI 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
Step 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.
Step 2. Click "..."-->"Authentication" to validate the database instance.
You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".
Step 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.
Step 4. After completing SQL Server backup, click Restore to restore differential backup in SQL server.
When you restore, you only need to select a differential backup file to restore, then data can be recovered to the state when the selected differential backup was performed.
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, you can also use AOMEI Cyber Backup to centrally backup VMware or Hyper-V virtual machines within LAN.