With the help of the SqlBak tool, you can create a backup job that will make backup according to your schedule. SqlBak can create full backups, differential backups, and transaction log backups. In this article, let’s discuss what is it differential backups.
Differential Backups
Differential backups are based on the previous SQL Server full backup. Differential backups contain only the data that changed since that full SQL Server database backup. The full backup upon which differential backups are depending on is known as the base of the differential. Full backups, other than copy-only backups, may work as the base for a series of differential backups, including database backups, partial backups, and file backups.
Why do I Need to use Differential Backups?
Producing differential backups will be really rapid compared to creating a full backup. Please note that differential backups save only the data that has changed from the full backup after the differential backup is based. This allows taking constant data backups, that decrease the risk of data loss. However, when you restore your database from differential backups, you should restore full SQL Server database backups.
Therefore restoring your database from differential backups will necessarily bring even more tips and duration than recovering out of a full backup considering couple backup files are required. SQL Server differential backups are especially helpful if a subset of a database is modified more usually than the rest of the database. In such cases, differential backups allow you to backup regularly without the overhead of full database backups.
Short Review of Differential Backups
Because the differential backups increase in size, recovering a differential backup might greatly improve the time that is needed to restore a database. So, we suggest that you create a new full backup at a fixed time interval to create a new differential base for the data. Such as, you might possibly take every week full backup of the whole database (that is, a full database backup) followed by a scheduled line of differential database backups over the week.
At restore time period, before you restore differential backups, you have to recover its base. Then, restore just essentially the most previous differential backup to get the database ahead to the time when that differential backup was produced. Regularly, you would definitely restore the latest full backup, followed by the absolute most recent differential backup that is based on that full backup.
How to Perform Differential Backup
Here we are going to discuss how to perform differential backups, and what is the easiest way. It should be admitted that there is three most common way to perform SQL Server differential backup. You can use T-SQL Commands or SQL Server Management Studio (SSMS), but the best way is to use a third-party tool like SqlBak.
T-SQL Command
Use the following syntax to perform a differential backup:
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
or if you need to perform a differential backup with COPY_ONLY option use next:
BACKUP DATABASE your_database TO DISK = 'full.bak' WITH COPY_ONLY
SQL Server Management Studio (SSMS)
Open SSMS and right-click on the database which you are going to backup. Choose “Tasks”, then “Back up…”. Select a backup type “Differential”, add backup destination and press “OK”.
SqlBak
This is the easiest way to perform all SQL Server backup types. All you need is to create a SqlBak backup job, which will create all backups according to the schedule. As usual, you can tune a backup job in 2 minutes. Below you can find a short tutorial on how to create an automatic backup job. So let’s start:
-
Create a backup job
Go to your Dashboard page and click “Add new job”.
-
Choose databases
Select all SQL Server databases from the list which you need to backup.
-
Select a place where your SQL Server databases backups will be stored
To do it, click “Add destination…” in the Store backups in destinations”.
In the opened window, select a place where your SQL Server database backups will be stored.
-
Create a backup schedule
To create a backup schedule, click “Schedule backups” then press “Advanced settings…” and set a backup schedule as you need.
-
Email notifications
You can tune email notification to receive an email in the case of failure or success. To enable this option, press “Send email confirmation” and make all necessary settings.
Tha’s it. Your SqlBak backup job is ready. Now all SQL Server database backup will be made according to your schedule.
Differential Backups of Read-Only Databases
Regarding read-only databases, full backups taken alone are easier to manage than whenever they are applied with differential backups. Whenever a database is read-only, backup, and another process can’t change the metadata that is covered in the file. So, metadata that is required by a differential backup, such as the log sequence number at which one the differential backup begins (the differential base LSN), is stored in the master database. If the differential base is taken when the database is read-only, the differential bitmap shows more variations than have actually taken place since the base backup. The extra data is read by backup but is not written to the backup since the differential_base_lsn stored in the backup set system table is used to determine whether the data has, in fact, updated since the base.
Whenever a read-only database is reconstructed, restored, or detached and connected, the differential base information is lost. That happens mainly because the master database is not matched with the user database. The SQL Server Database Engine can’t identify or preserve that problem. Any later differential backups are not based on the most recent full backup and might give sudden information. To establish a new differential base, we encourage that you take a full database backup.
Best Practices for Using Differential Backups with a Read-Only Database
Once you make a full database backup of a read-only database if you want to make subsequent differential backups, backup the master database.
If the master database is destroyed, restore it earlier you recover any of your differential backups of a user database. If you detach and attach a read-only database for which you organize to later use differential backups, as soon as it is practical, take a full database backup of both the read-only database and of the master database.