SQL Server Backup Plan Strategy

The estimated reading time for this post is 14 minutes

Backup plan! The operation that is getting fade and fade among SQL Server DBAs due to other backup technologies such as SAN replication, VM replication and VM HADR. Each SQL Server database needs its own backup and restore plan strategy depending on the business value. Most of DBAs and Infra-Administrators are looking at database backup plan as an optional and mostly unneeded due to mentioned new technologies, as SQL Server DBA always keep in mind that database is not such as file system, it is transactional and super sensitive towards I/O and storage sub-system. In some cases ‘Deferred Transactions’ can cause database corruption.

Why backup plan strategy is needed?

Beside mentioned backup technologies, database backup plan strategy is important to meet RTO and RPO and to make sure that database is recoverable and keep the business on track after any unexpected corruption or disaster.

Based on statistics:

  • 90% of businesses losing data from a disaster are forced to shutdown within two years.
  • The survival rate for companies without backup and restore / disaster recovery is less than 10%.
  • Only 44% of businesses successfully recovered information after a recent data recovery event.
  • 53% of claimants never recoup the losses incurred by a disaster.

Backup and restore plan considerations.

During planning of backup and restore strategy, keep in mind that result plan should meet agreed RTO and RPO.

Recovery Time Objective (RTO):


The recovery time objective (RTO) is the targeted duration of time and a service level within which a business process must be restored after a disaster (or disruption) in order to avoid unacceptable consequences associated with a break in business continuity.

It can include the time for trying to fix the problem without a recovery, the recovery itself, testing, and the communication to the users. Decision time for users representative is not included.

Recovery Point Objective (RPO):


The recovery point objective (RPO) is the age of files that must be recovered from backup storage for normal operations to resume if a computer, system, or network goes down as a result of a hardware, program, or communications failure. The RPO is expressed backward in time (that is, into the past) from the instant at which the failure occurs, and can be specified in seconds, minutes, hours, or days. It an important consideration in disaster recovery planning (DRP).

Once the RPO for a given computer, system, or network has been defined, it determines the minimum frequency with which backup s must be made. This, along with the recovery time objective ( RTO ), helps administrators choose optimal disaster recovery technologies and procedures. For example, if the RPO is one hour, backups must be made at least once per hour. In this case, external, redundant hard drives may prove to be the best disaster recovery solution. If the RPO is five days (120 hours), then backups must be made at intervals of 120 hours or less. In that situation, tape or recordable compact disk ( CD-R ) may be adequate.

—SQL Server Database Recovery Models

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.


Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model. Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

  • Log shipping
  • AlwaysOn or Database mirroring
  • Media recovery without data loss
  • Point-in-time restores


No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model.


An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations.

—How SQL Server Backup Works


Once the ‘Backup Database’ command executed, SQL Server automatically does few ‘Checkpoint’ to reduce the recovery time and also it makes sure that at point of command execution there is no dirty pages in the buffer pool. After that SQL Server creates at least three workers as ‘Controller’, ‘Stream Reader’ and ‘Stream Writer’ to read and buffer the data asynchronously into the buffer area (Out of buffer pool) and write the buffers into the backup device.

Be aware that ‘Stream Reader’ thread is one per volume and ‘Stream Writer’ thread is one per backup device.

For more information, read SQL Server Backup Internals.

Offline Data Cannot Be Backed Up

Any backup operation that implicitly or explicitly references data that is offline fails. Some typical examples include the following:

  • You request a full database backup, but one filegroup of the database is offline. Because all filegroups are implicitly included in a full database backup, this operation fails.

To back up this database, you can use a file backup and specify only the filegroups that are online.

  • You request a partial backup, but a read/write filegroup is offline. Because all read/write filegroups are required for a partial backup, the operation fails.
  • You request a file backup of specific files, but one of the files is not online. The operation fails. To back up the online files, you can omit the offline file from the file list and repeat the operation.

Typically, a log backup succeeds even if one or more data files are unavailable. However, if any file contains bulk-logged changes made under the bulk-logged recovery model, all the files must be online for the backup to succeed.

Concurrency Restrictions

SQL Server uses an online backup process to allow for a database backup while the database is still being used. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation. However, if you try to start a backup operation while a database file is being created or deleted, the backup operation waits until the create or delete operation is finished or the backup times out.

Operations that cannot run during a database backup or transaction log backup include the following:

  • File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
  • Shrink database or shrink file operations. This includes auto-shrink operations.
  • If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

If a backup operation overlaps with a file-management operation or shrink operation, a conflict occurs. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. (The time-out period is controlled by a session time-out setting.) If the lock is released during the time-out period, the second operation continues. If the lock times out, the second operation fails.

Database Backup Benefits

SQL Server Native Database backup protects you from potentially catastrophic data loss. With valid backups of a database, you can recover your data from many failures, such as:

  • Media failure.
  • User errors, for example, dropping a table by mistake.
  • Hardware failures, for example, a damaged disk drive or permanent loss of a server.
  • Natural disasters.

backups of a database are useful for routine administrative purposes This is the only way to reliably protect your SQL Server data.

What is Database Backup Plan?

backup plan

Because data is the heart of the enterprise, it’s crucial for you to protect it. And to protect your organization’s data, you need to implement a database backup and recovery plan. Backing up database can protect against accidental loss of user data, database corruption, hardware failures, and even natural disasters. It’s DBA job as an database administrator to make sure that database backups are performed and that backup tapes/disks are stored in a secure location.

Database backup is an insurance plan. Important data are accidentally deleted all the time. Mission-critical database can become corrupt. Natural disasters can leave your office in ruin. With a solid backup and recovery plan, you can recover from any of these. Without one, you’re left with nothing to fall back on.

Basic Types of Database Backup

There are many techniques for backing up database files. The techniques you use will depend on the type of data you’re backing up, how convenient you want the recovery process to be, and more.

The basic types of database backups you can perform include

Copy-only Backup: A special-use backup that is independent of the regular sequence of SQL Server backups.

Data Backup: A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

Database Backup: A backup of a database. Full database backups represent the whole database at the time the backup finished. Differential database backups contain only changes made to the database since its most recent full database backup.

Differential Backup: A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data extents that have changed since the differential base. A differential partial backup records only the data extents that have changed in the filegroups since the previous partial backup, known as the base for the differential.

Full Backup: A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

Log Backup: A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (full recovery model)

File Backup: A backup of one or more database files or filegroups.

Partial Backup: Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

In your backup plan you’ll probably want to perform full backups on a weekly basis and supplement this with daily, differential backups. You may also want to create an extended backup set for monthly and quarterly backups that includes additional database files that aren’t being backed up regularly.

You’ll often find that weeks or months can go by before anyone notices that a file or data source is missing. This doesn’t mean the file isn’t important. Although some types of data aren’t used often, they’re still needed. So don’t forget that you may also want to create extra sets of backups for monthly or quarterly periods, or both, to ensure that you can recover historical data over time.

Excellent Database Backup Plan Factors

It takes time to create and implement a database backup and recovery plan. You’ll need to figure out what data needs to be backed up, how often the data should be backed up, and more. To help you create a plan, consider the following:

  • How important is the data on your systems? The importance of data can go a long way in helping you determine if you need to back it up—as well as when and how it should be backed up. For critical data, you’ll want to have redundant backup sets that extend back for several backup periods. For less important data, such as daily user files, you won’t need such an elaborate backup plan, but you’ll need to back up the data regularly and ensure that the data can be recovered easily.
  • What type of information does the data contain? Data that doesn’t seem important to you may be very important to someone else. Thus, the type of information the data contains can help you determine if you need to back up the data—as well as when and how the data should be backed up.
  • How often does the data change? The frequency of change can affect your decision on how often the data should be backed up. For example, data that changes daily should be backed up daily.
  • How quickly do you need to recover the data? Time is an important factor in creating a backup plan. For critical systems, you may need to get back online swiftly. To do this, you may need to alter your backup plan.
  • Do you have the equipment to perform backups? You must have backup hardware to perform backups. To perform timely backups, you may need several backup devices and several sets of backup media. Backup hardware includes tape drives, optical drives, and removable disk drives. Generally, tape drives are less expensive but slower than other types of drives.
  • Who will be responsible for the backup and recovery plan? Ideally, someone should be a primary contact for the organization’s backup and recovery plan. This person may also be responsible for performing the actual backup and recovery of data.
  • What is the best time to schedule backups? Scheduling backups when system use is as low as possible will speed the backup process. However, you can’t always schedule backups for off-peak hours. So you’ll need to carefully plan when key system data is backed up.
  • Do you need to store backups off-site? Storing copies of backup tapes off-site is essential to recovering your systems in the case of a natural disaster. In your off-site storage location, you should also include copies of the software you may need to install to reestablish operational systems.

Typical Backup Devices and Solutions

backup devices

Many tools are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that’s right for your organization depends on many factors, including

  • Capacity The amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?
  • Reliability The reliability of the backup hardware and media. Can you afford to sacrifice reliability to meet budget or time needs?
  • Extensibility The extensibility of the backup solution. Will this solution meet your needs as the organization grows?
  • Speed The speed with which data can be backed up and recovered. Can you afford to sacrifice speed to reduce costs?
  • Cost The cost of the backup solution. Does it fit into your budget?

Capacity, reliability, extensibility, speed, and cost are the issues driving your backup plan. If you understand how these issues affect your organization, you’ll be on track to select an appropriate backup solution. Some of the most commonly used backup solutions include

Tape drives Tape drives are the most common backup devices. Tape drives use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive but aren’t highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 100 MB to 2 GB. Compared with other backup solutions, tape drives are fairly slow. Still, the selling point is the low cost.

Digital audio tape (DAT) drives DAT drives are quickly replacing standard tape drives as the preferred backup devices. DAT drives use 4 mm and 8 mm tapes to store data. DAT drives and tapes are more expensive than standard tape drives and tapes, but they offer more speed and capacity. DAT drives that use 4 mm tapes can typically record over 30 MB per minute and have capacities of up to 16 GB. DAT drives that use 8 mm tapes can typically record more than 10 MB per minute and have capacities of up to 36 GB (with compression).

Auto-loader tape systems Auto-loader tape systems use a magazine of tapes to create extended backup volumes capable of meeting the high-capacity needs of the enterprise. With an auto-loader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most auto-loader tape systems use DAT tapes. The typical sys tem uses magazines with between 4 and 12 tapes. The main drawback to these systems is the high cost.

Magnetic optical drives Magnetic optical drives combine magnetic tape technology with optical lasers to create a more reliable backup solution than DAT. Magnetic optical drives use 3.5-inch and 5.25-inch disks that look similar to floppies but are much thicker. Typically, magnetic optical disks have capacities of between 1 GB and 4 GB.

Tape jukeboxes Tape jukeboxes are similar to auto-loader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high-capacity solutions. These systems load and unload disks stored internally for backup and recovery operations. Their key drawback is the high cost.

Removable disks Removable disks, such as Iomega Jaz, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.

Disk drives Disk drives provide the fastest way to back up and restore files. With disk drives, you can often accomplish in minutes what takes a tape drive hours. So when business needs mandate a speedy recovery, nothing beats a disk drive. The drawbacks to disk drives, however, are relatively high costs and less extensibility.

Before you can use a backup device, you must install it. When you install backup devices other than standard tape and DAT drives, you need to tell the operating system about the controller card and drivers that the backup device uses.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:

Leave a Comment

Be the First to Comment!

Notify of