13. Under Overwrite Media section, there are two options available namely Back up to the existing media set & Back up to a new media set, and erase all existing backup sets. In this article we have to select Append to the existing backup set option.
14. Next under Reliability section there are three options available to choose namely Verify backup when finished, Perform checksum before writing to media and Continue on error. Here you need to select Verify backup when finished option as we want the backups to be verified for any error once they are completed.
15. If you are performing transactional log backup then Transaction log section will be enabled where you can choose one of the options available namely; Truncate the transaction log or Back up the tail of the log, and leave the database in the restoring state.
16. Next under compression section, choose the appropriate backup compression settings. In SQL Server 2008 Enterprise Edition and later backup compression feature is available. If you want to take the database backup utilizing backup compression feature then you can select the option from the drop down list. To know more about Data Compression Feature in SQL Server 2008 you can refer to my previous article titled How to configure & use Database Backup Compression Feature of SQL Server 2008.
17. Finally click OK to take the differential database backup of AdventureWorks database.
How to take Differential Database Backups using TSQL Code
Execute the below TSQL code to take the differential database backup of AdventureWorks databases using the database backup compression feature.
USE master
GO
BACKUP DATABASE AdventureWorks
TO DISK = N’D:DatabaseBackupsAdventureWorks_Diff.bak’
WITH DIFFERENTIAL, COMPRESSION
GO
Advantages of Differential Database Backups
- Differential Backups usually compete in lesser time when compared to Full Backups. The duration depends upon the number of extents which got changes since the last full backups. A differential backup basically takes the backup of changed extents.
- Storage space required for differential backups is comparatively less when compared to the space required for full backups because in differential backup only changed extents are backed up.
- Restoration of database is much faster as DBA needs to first restore the full backup using NORECOVERY and then they need to restore the latest differential backup with NORECOVERY and then if you have any transactional logs then they need to be restored in the same order of backup with NORECOVERY except for the last transactional log which needs to be restored with RECOVERY option.
Disadvantages of Differential Database Backups
- Point in time recovery of the database may not be possible always if the differential backups available are many hours prior to the time of disaster.
- It is recommended to have transactional log backups taken at shorter time intervals along with differential backups to achieve point in time recovery.
- If there are lots of data changes happening then there could be scenarios when differential backups are more or less of same size that of full backups.
Conclusion
Using differential backup strategy database administrators can take database backups quickly without consuming lot of system resources especially when backing up very large databases. However it is advised to use a combination of differential and transactional log backups for larger databases to safeguard the database and this also helps database administrators to perform database restores more quickly when they need to perform a disaster recovery exercise.
]]>