SQL Server Differential Backups – What You Must Know

In this article, we will explore the differential backup of SQL Server database in depth.

In a data driven business environment, losing valuable business data is hardly an option for companies. Now if you are using SQL Server in your organization, there are lots of different options for backups you can choose and create a backup strategy as per your need.

What kind of backup you choose depends on a lot of factors, and after making a selection of your backup type, you proceed towards making a backup strategy. A lot of things need to be considered while making a backup strategy. In this article we will be discussing all about Differential Backups.

Understanding Differential Backups in Depth

Full Backup And Differential BackupA Differential Backup is not a primary backup. Meaning if you want to backup all the data, differential backup is not the ideal choice. Full backups are used for backing up all the data. Differential backup is used for backing up the changes made in the database after a full backup. So after you are done with a full backup, you might or might not choose to do a differential backup. They are not incremental, but cumulative. With every successive differential backup after a full backup, you will find that the size of the database has increased. This happens because differential backups take into account all the changes made after a full backup.

SQL Server Differential BackupsFor every Global Allocation Map (GAM) interval of each data file, a special database page referred as differential bitmap, tracks all the GAM portions which have been altered after a full backup. These altered portions or changes in the database are captured when bitmaps are scanned in the process of differential backups. These bitmaps are reset with successive full backups. So with every database change made after a full backup, it will be tracked by differential bitmap, leading to larger successive differential backups. There is a possibility of a differential backup getting as big as a full backup. You can find out the probable size of a differential backup using a script.

While running a differential backup, use the With Copy_Only choice along with the backup statement if you want an Ad-hoc Backup which does not reset differential bitmaps. This is very important, without this feature all successive differential backups would end up basing on ad-hoc complete backup instead of the regular full backup. This can create greater problems at the time of restoration during or after a disaster.

Remain Prepared for Contingencies

Differential backups allow for faster restore operations by essentially allowing several transaction log backups to be ignored during a restore process. However in some cases tracking of changes may not be accurate and some records may end up getting missed. Now if your primary database file gets corrupted, the backup file would not be able to restore all the records. For such scenarios you can consider opting for a sql server recovery tool like DataNumen SQL Recovery. This class leading tool can pry out every single data element stored in the SQL file with ease. With exception to XML, recovery of all other data types is supported. Last but not the least, the tool can be used to recover several corrupted SQL files in one go.

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com

Comments are closed.