Addressing SQL Server Data Backup Challenges the Right Way

By CIOReview | Monday, August 29, 2016

It’s better to be safe than sorry when it comes to SQL server failures and disasters, so it’s imperative to have a backup and Disaster Recovery (DR) plan in place to recover from any man-made disaster or natural calamity. Having the necessary database backup and recovery tools in place is the safest and the most secure way of protecting the assets of any organization. Real-world databases on the SQL Server platform continue to get larger and there has also been a growing trend towards consolidating SQL Server environments to cut down costs and wasted resources. 

Consolidated environments consist of multiple databases on a single instance, multiple instances on one server, or virtualized servers. These consolidated environments pose challenges of their own, one of which is to provide an efficient solution for disaster recovery in the shortest span of time. One way to reduce the backup time is to back up your database to disk. SQL Server includes functionality for differential backups, which can provide time savings for consolidated environments. Scalability isn’t the only challenge that comes into play here, it’s also important to take into account the amount of time needed to perform a recovery operation. Some sites recommend making a full SQL Server data backup on a weekly basis and then backing up transition logs every few hours. 


•    Integrated backup features are capable—but limitations start to show up as SQL server environment grows in size, number of databases, number of instances and critical business dependency on SQL server. 
•    There’s a growing tendency to introduce SQL Server differential backups as form of deduplication. Differential backups only backup data that has changed since the last full backup.
•    Unfortunately there are some major challenges with differential backups. Restores are always dependent on full access to backups. Restore time objectives are affected by the amount of data change. 
•    It’s difficult to do anything with SQL differential backup without its associated full backup. This can arise because someone has deleted the full backup or files were moved to hard disks to access servers or other off media sites. 
•    It is not that difficult to easily mess up SQL server recovery by recovering a full backup from the disk without even thinking about the interdependencies with other backups. 
•    The effectiveness of differential backups is dependent on the amount of data that that is there inside the database.
•    File retention is the automated process of deleting backups from disk that are no longer needed, while also making sure that needed backups stay around for a longer period.

Types of Database Backups

In general, databases backup in full recovery mode are taken in three different kinds of database files.

1.    Full Database Backup
2.    Differential Database Backup
3.    Log Backup

‘After being able to restore full database backup, restore latest differential database backup and all the transaction log backup after that to get database to current state.’

Full Database Backup

A full backup contains all the data in a specific database or set of files, and also enough logs to allow for recovering that data. It is the fundamental on which both differential backup and transaction log backup are based.

Differential Backup

It is of common knowledge that SQL Server includes functionality for differential backups, which can help in providing additional disk and time savings for consolidated environments, particularly when there is data that doesn’t compress well. DBAs have been skeptical and reluctant to use differential backups in the past, but they need to get accustomed to it fast as databases grow in size and business SLAs can no longer be met.

The key to establishing a solid differential backup strategy is the ability to have confidence and reliability in the ‘differential base’. A differential base is a full file, or file group backup of the database. It’s required for differential backups. There are backup options such as ‘Checksum’ and ‘Restore with Verify’, which are useful to instill confidence in the differential base’s reliability and effectiveness.

There are two distinguishable types of differential backups: single-base (a differential backup that’s based on a single full backup) and multibase (a differential backup that’s based on different file or file group backups). The most prevalent and commonly used type is the single-base differential backup. It’s not advisable to run a single-base differential backup on databases operating under the ‘Simple Recovery Model’. If a multibase differential backup is run, then it will lead to an error and the differential backup will fail.

Transaction Log Backup

The Transaction Log Backup is a record of all the transactions that have been performed against the database since the last transaction log was backed up. With transaction log backups, you can recover the database to a specific point in time, or to the point of failure. The transaction log backups are only valuable under the full recovery model or bulk-logged recovery model. Just like differential backup, transaction log backup is also based on full backup.

Recovery Models

Which recovery model is best suited for the company? This is a question that keeps popping up every now and then. SQL Server offers three recovery models that can be implemented for databases, as per requirement. These settings can be configured either through enterprise manager or through T-SQL .

The three database recovery model options are:

•    Simple Model: In this type of recovery model data is recoverable only to the most recent full database or differential backup. The Simple Recovery  Model is far easier to manage and maintain than the ‘Full or Bulk-Logged Models’, but at the expense of high data loss because contents of the database transaction log are truncated each time a checkpoint is issued for the database.

•    Full Recovery Model: This model uses database backups and transaction log backups to provide complete and emphatic protection against failure. Full Recovery provides the ability to recover the database to the point of failure or to specific point in time. 

•    Bulk Logged Model: This recovery model provides protection against failure combined with the best performance. In the this model, a damaged data file can result in having to redo work manually based on the operations above that are not fully logged. This recovery model should be used intermittently to improve performance before large scale bulk operations.  

Differential backups have been there in SQL Server for a long time, although they appear to be a long forgotten functionality now. Differential backups are powerful and, if used correctly, can play a crucial part in the DBA's (Database Administration) storage management strategy. It can be easily integrated into any backup scenario and DBAs can instantly realize savings in both the storage required for backups and the associated costs per gigabyte.