Database Backup Strategies

Database Backup Strategies

One of the most important responsibilities of SQL DBA is the planning, testing and deployment of the backup plan.  The planning of the backup strategy, ironically, starts with the restore strategy that has been determined by the organization.   Two leading questions direct the restore strategy: What is the maximum amount of data loss that can be tolerated, and what is the accepted time frame for the database to be restored in case of a disaster.  The first question involves taking multiple transaction logs backups and the second question involves the use of high availability solutions.  To answer these question, you must ask the why, what, when, where and who for the strategy.

Why we need back-up?

Backups are taken in order to be able to restore the database to its original state just before the disaster and protect against the following reasons:

•          hardware failure
•          malicious damage
•          user-error
•          database corruption

What is a backup?

A copy of data that is used to restore and recover the data after a system failure.

                                                                                 

There are several different kinds of backups:

Full backup - a complete, page-by-page copy of an entire database including all the data, all the structures and all other objects stored within the database.
Differential backups -  a backup of a database that have been modified in any way since the last full backup.
Transaction Log - a backup of all the committed transactions currently stored in the transaction log
Copy-only backup -  a special-use backup that is independent of the regular sequence of SQL Server backups.
File backup - a backup of one or more database files or file-groups

Partial backup - contains data from only some of the file-groups in a database.

When to do backups?

Again, this is a company decision that must be determined by asking the following questions: What is the maximum amount of data loss that can be tolerated, and what is the accepted time frame for the database to be restored in case of a disaster.  The first question will address the use of transaction logs and their frequency, and the second question will involve some type of high availability solution.

Where should backups be placed?

Ideally, the backups should be placed on their own drives onto separate servers from the production server, and then using a third party application, copies made to a remote server on a separate site. What should not be tolerated is having backups on the same drive and server as the data and log files.  If the server crashes, we lose all hopes or restoring critical data!

What needs backing up?

All user defined databases and system database should be backed up.

Who should perform backups?

The person in charge of the backup planning and executing will most likely be the Database Administrator (DBA).  He will coordinate with the upper management, direct and provide valuable advice on the best practices for restoring the database; however, note that on a production server, most of the backups will be automated by using the SQL Agent and jobs.

Backup Retention Period

The amount of backups retained is a question determined by the business needs. Most likely, it may involve a month of backups onsite and three months of backups offsite.  But again, this depends upon the organization needs.