3 Quick Ways to Create A SQL Server Differential Backup
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.
- SQL Server Differential Backup vs Full Backup vs Transaction Log Backup
- SQL Server Differential Backup Best Practices (3 Methods)
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.
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:
BACKUP DATABASE databasename TO DISK = 'filepath\filename.bak' WITH DIFFERENTIAL
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
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".
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.