Skill 2 - Backup Strategy

Data loss comes in many forms, including hardware failure, database corruption, malicious activity, and user error, and you need to develop a DRP to protect against all of these eventualities. It is common for organizations to have data governance requirements, so you will need to factor these into your data disaster strategy.

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Backup and restore strategies

Backing up and restoring data must be customized to a particular environment and must work with the available resources. Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. A well-designed backup and restore strategy balances the business requirements for maximum data availability and minimum data loss, while considering the cost of maintaining and storing backups.

A backup and restore strategy contains a backup portion and a restore portion. The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). The restore part of the strategy defines who is responsible for performing restores, how restores should be performed to meet your goals for database availability and minimizing data loss, and how restores are tested.

Designing an effective backup and restore strategy requires careful planning, implementation, and testing. Testing is required: you do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy and have tested the restored database for physical consistency. You must consider a variety of factors. These include:

  • The goals of your organization regarding your production databases, especially the requirements for availability and protection of data from loss or damage.

  • The nature of each database: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.

  • Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

Choose appropriate recovery model

Backup and restore operations occur within the context of a recovery model. A recovery model is a database property that controls how the transaction log is managed. Thus, the recovery model of a database determines what types of backups and restore scenarios are supported for the database, and what the size of the transaction log backups would be. Typically, a database uses either the simple recovery model or the full recovery model. The full recovery model can be augmented by switching to the bulk-logged recovery model before bulk operations. For an introduction to these recovery models and how they affect transaction log management, see The Transaction Log (SQL Server)

The best choice of recovery model for the database depends on your business requirements. To avoid transaction log management and simplify backup and restore, use the simple recovery model. To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. To minimize impact on log size during bulk-logged operations while at the same time allowing for recoverability of those operations, use bulk-logged recovery model. For information about the effect of recovery models on backup and restore, see Backup Overview (SQL Server).

Source: Back Up and Restore of SQL Server Databases - SQL Server | Microsoft Docs

Review backup operations

Your backup strategy will consist of a number of scheduled backup operations. These backup operations will potentially perform different types of backups, which we need to review for the exam. SQL Server supports the following types of backups.

  • Full -- A full backup captures everything in the database.

  • Differential -- A differential backup captures everything in the database since the last full backup.

  • Incremental An incremental backup captures everything since the last incremental backup. In SQL Server it is called a log backup. You need to perform a full backup before you can perform log backups.

To design a backup strategy, you need to take into account a number of factors including:

The size of your database A larger database will take longer to backup. Your database might grow to a size where the backup operation can no longer be completed within an appropriate maintenance window. At that stage you might have to use different

hardware or redesign your database and backup strategy.

The structure of your database files A database that consists of a single primary data file will be difficult to back up within an appropriate maintenance window as it gets larger. You have no choice but to back up the database in its entirety. As an alternative, if the database consists of multiple secondary data files these files can be backed up individually at different frequencies.


Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.

Things that you can find in the Paddock Zone.

The speed/throughout of the network and storage subsystems involved...

How heavily utilized is the processor subsystem....

The volume of data modifications in the database....

The size of the data modifications in the database.....

The type of data modifications in the database, for example, if they are predominantly update, or insert operations....

13 views0 comments
Orange Splash
Orange Splash