Appendix A: Backup Set Types for MS SQL Backup Set

There are four kinds of backup set type to choose from, namely full backup, differential backup, incremental backup and transaction log backup. The information below gives you an overall idea of what each backup set type is like.

To perform a full backup, CloudBacko Pro requests the SQL server to generate a Volume Shadow Copy Service (VSS) snapshot of the database. CloudBacko Pro will back up the VSS snapshot generated by the SQL server directly. A full backup is required in order to run incremental or differential backups.

You can also decide how the full backup is run by selecting the desired in-file delta type (Full, Differential or Incremental).

For further details on this topic, refer to this URL: https://msdn.microsoft.com/en-us/library/ms175477.aspx

A differential backup of the SQL server saves changes to the database that have occurred since the last full backup. To perform a differential backup, CloudBacko Pro requests the SQL server to generate a differential backup file of the database since the last full backup. At the back end, the SQL server performs the following:

1. Generate a VSS snapshot of the database of the current state.
2. Compare the VSS snapshot just generated by the SQL server with the one generated from the last full backup in order to produce a differential backup file.
3. The differential backup file being sent to CloudBacko Pro for backup.

Using a differential backup file to recover a database requires the restoration of only two data sets - the last full backup and the most recent differential backup.

The disadvantage of using differential backups is that it duplicates the backed up data in each backup until a full backup is performed. If there are many differential backups taken between full backups, the storage space required can greatly exceed that required by the same number of incremental backups. The SQL server does not allow a differential backup to occur when there has been no previous full backup to establish the starting point.

For further details on this topic, refer to this URL: https://msdn.microsoft.com/en-us/library/ms186289.aspx

An incremental backup of the SQL server saves changes to the database that have occurred since the last full or incremental backup. To perform an incremental backup, CloudBacko Pro requests the SQL server to generate a differential backup file of the database since the last full backup. At the back end, the SQL server performs the following:

1. Generate a VSS snapshot of the database of the current state.
2. Compare the VSS snapshot just generated with the one generated from the last full backup in order to produce a differential backup file.
3. The differential backup file being sent to CloudBacko Pro.
4. CloudBacko Pro performs an in-file delta check between the differential backup file just received from the SQL server and the one from the last backup.
5. CloudBacko Pro will then be able to generate an incremental delta file which contains changes of the database files since last differential backup. Only this incremental delta file will be backed up.

Using an incremental backup to recover a database requires the restoration of at least two data sets - the last full backup and every incremental backup taken after the last Full backup. The benefit of using incremental backups is that the individual backups are much smaller than a full backup and individual incremental backups are frequently smaller than differential backups.

The disadvantage of using incremental backups is that if there are many incremental backups made between full backups, recovering the storage group may involve recovering many incremental backups. The SQL server does not allow an incremental backup to occur when there has been no previous full backup to establish the starting point.

Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.

If you have chosen to back up in ODBC mode, you can configure schedule backup to back up the transaction log regularly at a time interval of your choice.