See Also: Main_Page - Database Administration - Backup & Recovery

The Basics of Backup & Recovery

Ensuring the availability and integrity of the organization's data is a database administrator's primary responsibility. Data can be your company's biggest asset and losing data might cost millions of dollars in lost customers and lost revenue. Having a dependable disaster recovery process is vital. It is tough to predict all possible disaster scenarios, but you can prepare for the worst and hope for the best.

Backup is the process of copying data from the database to the backup media. If you have a valid backup you can restore the database to the state it was in when the backup was generated. Like any other activity, backups place a certain amount of overhead on the SQL Server system. Furthermore, users cannot access the database while it is being recovered. Therefore it is essential to know the factors affecting the performance of backup and restore operations and how to tune the performance of backups.

Recovery is the ability of the SQL Server database to re-run the transactions after the system failure. Changes to the data are not written to the disk immediately. Rather, transactions are initially written to the transaction log. SQL Server periodically flushes the data modifications to the disk through a process called CHECKPOINT. If a system failure occurs, SQL Server examines the transaction log at system startup. Transactions that have already been committed are saved to disk; transactions that haven't been committed are rolled back. SQL Server cannot recover a database if the transaction log is unavailable.

SQL Server Backup Basics Tutorial

In this video, Brent Ozar talks about the basics of full, differential and log backups:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20081229-BackupBasics.flv

For more information about the types of backups, check out the Types of Backups article.

For more tutorial videos like this, check out the SQL Server Tutorials page.

SQL Server Disaster Recovery Options Tutorial

In this video, Brent explains the differences between log shipping, database mirroring and SAN-to-SAN replication:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20081231-DisasterRecovery.flv

For more information about log shipping, check out the Log Shipping topic.

Factors Affecting the Disaster Recovery Plan

Disaster recovery plans will differ significantly from one organization to the next, depending on many factors. Some of these factors are:

How important is data on each server?

Some servers might be used solely for development or testing - these can be easily re-created and typically don't require judicious recovery plans. Production server failure, on the other hand, can mean a loss of millions of dollars and cannot be tolerated.

How much data can you afford to lose?

Some environments with few transactions each hour can afford losing a day's or even week's worth of data - if this happens someone will simply have to re-enter all of the lost information. On the contrary, heavily utilized commercial systems cannot afford to lose many transactions - such occurrence might mean losing customers.

How much down-time can you afford?

Recovering from a hardware failure or network outage can translate into lost business. In such cases, you should look into high availability solutions so that business can continue running even during the recovery process.

What budget do you have available for supporting the disaster recovery plan?

This question is often overlooked, but the first three questions are pointless if you have little or no money to invest in high availability or a disaster recovery process.

Based on these questions it should come as no surprise that recovery takes more than backing up and restoring databases. Database administrators may or may not be involved in resolving hardware, operating system and network issues. However, having appropriate backups and knowing when and how to restore them is the responsibility of the DBA.

SQL Server Backup Topics To Read Next

• Log Shipping - for both SQL Server 2000 and 2005.
• Recovery Models - explains the difference between full recovery mode, simple recovery, and bulk logged.
• Backup Performance - backups slow the SQL Server down. This article explains how to avoid a big performance hit.