Lesson 1: Understanding Data Restoration Issues

3 4

In this lesson you will learn about the design goals of a successful data restoration strategy. Next, you will learn about the available types of database backups and restoration options from which you must develop your data restoration strategy. Next, you learn about the system databases that you need to back up (in addition to user databases) to recover successfully from a disk or system failure. Finally, you learn about the components of a successful data restoration plan.

After this lesson, you will be able to

  • Describe the goals of a data restoration strategy
  • Understand the types of database backups and the restoration options
  • Understand the system and user databases that must be backed up
  • Develop a successful data restoration plan

Estimated lesson time: 15 minutes

What Are the Goals of a Data Restoration Strategy?

A data restoration strategy must acknowledge that all databases will require data restoration at some point in their life cycle. As a database administrator, you need to minimize how frequently you need to employ data restoration, monitor for problems before they occur, anticipate the full range of possible disasters, increase the speed of restoration when disasters do occur, and quickly verify that the restoration was successful.

Provide Fault Tolerance

You should plan for fault tolerance, where affordable and possible, to keep your SQL Server 2000 installation running in spite of hardware failures. This includes using RAID to provide fault tolerance for your disk subsystem. Beyond your disk subsystem, this also includes protecting your Windows server against failure. Although the full range of these options is beyond the scope of this book, protecting your Windows server should include selecting reliable hardware, using power conditioning and power interruption devices, performing regular system backups, and being prepared for server hardware failures. Hardware failures will occur. You should also consider Windows clustering for high availability. Chapter 16 covers the use of SQL Server 2000 failover clustering using Windows clustering as a high availability solution for your critical 24x7 databases.

Monitor Your Database

You should continually monitor your database to detect problems before they occur. Chapter 14 covers system and database monitoring. In general, this includes using Database Consistency Checker (DBCC) statements to monitor your database for consistency, using SQL Server performance condition alerts to notify you of potential problems (such as transaction log files running out of space), and using SQL Server Agent to automate routine tasks (such as backing up the transaction log regularly).

Plan for All Forms of Failure and Disaster

You should anticipate all forms of possible disasters and develop plans to recover from each. The following is a partial list of some of the disasters that can occur during the life cycle of a database system.

  • Loss of a disk containing a data file
  • Loss of a disk containing a transaction log
  • Loss of the disk containing the system files
  • Server failure
  • Natural disaster (flood, earthquake, or fire)
  • Loss, theft, or destruction of the entire server
  • Theft of backup media
  • Faulty backup media
  • Faulty restoration device
  • Inadvertent user error (such as deleting an entire table by accident)
  • Malicious employee behavior (such as inserting inaccurate information intentionally)

Determine Acceptable Data Restoration Times

You need to determine the acceptable length of time for data restoration from each type of disaster. The absolute minimum length of time possible will vary depending upon the type of disaster and the size of the database. The acceptable length of time will vary based upon the use of the database. A database being used for Web-based order entry for a large corporation has much stricter data restoration requirements than a decision support database that is updated weekly from an OLTP database. Also, acceptable data restoration time should take into account the fact that the data in the order entry database is much more difficult (if not impossible) to regenerate than the data in the decision support database. For critical databases, consider high-availability solutions such as hot standby servers and failover clustering, covered in Chapter 16. Your knowledge of acceptable data restoration times will help you to make the decisions in your data restoration strategy.

When planning recovery from each type of potential disaster, you need to ask the appropriate questions to plan for all contingencies. For example, if a disk containing a data file fails, ask yourself the questions in the following list (which also suggests some contingencies for which you need to plan).

  • What is the true cost of having your database down?
  • Is the time required to replace a data disk (assuming you have one on hand) and restore the data from a database backup acceptable?
  • Do you need to implement RAID so that a single disk failure does not take your database down?
  • How long will the restoration of the data from database backups actually take?
  • Will more frequent backups significantly reduce this restoration time?
  • What if your tape drive fails during restoration; do you have another tape drive available?


You should periodically test how quickly you can perform a database restoration assuming various types of disasters (such as a natural disaster).

Be Able to Quickly Verify Proper Database Functionality

You must be able to quickly verify that your database is up and functioning properly before you allow users to access the restored database. You can usually accomplish this by using a Transact-SQL script to query the database to determine whether it is working as intended.

What Types of Backups Are Available?

Now that you understand the types of disasters that you need to plan for and the acceptable data restoration time, you can begin to develop a database backup strategy as part of your data restoration plan. First, you need to understand the types of SQL Server 2000 backups that are available to you to protect your data from disk and system failures. Table 8.1 briefly describes the types of backups that you can use in your data restoration plan.


You must use SQL Server 2000 or third-party database backup programs to back up online database files. Windows 2000 and Windows NT 4.0 backups cannot back up files that are in use and for this reason cannot back up online SQL Server 2000 database files.

What Types of Data Restorations Are Available?

Table 8.2 briefly describes the types of data restorations that are possible based upon different types of database backups.

Table 8.1 Types of Backups

Backup Type


Full database backup

Full copy of the database.

Differential database backup

Copy of all modified data pages since the last full database backup.

Filegroup backup

Full copy of all files in a filegroup.

Differential filegroup backup

Copy of all modified data pages since the last full filegroup backup.

File backup

Full copy of a data file.

Differential file backup

Copy of all modified data pages in a data file since the last full file backup.

Transaction log backup

Copies the active portion of the transaction log (which) also truncates the log).

Snapshot backup and restore

Full database copy in a very short time (measured in seconds) using third-party hardware and/or software vendors. Can be used with conventional differential and transaction log backups.

Table 8.2 Types of Data Restorations

Restoration Option


Full database restore

A complete restoration of an entire database using a full database backup, the most recent differential database backup (if any), and all transaction log backups in sequence since the most recent full or differential database backup.

File or filegroup restore with full recovery

A complete restoration of a file or filegroup using a file or filegroup backup, the most recent differential file or filegroup backup (if any), and all transaction log backups in sequence since the most recent file or differential file or filegroup backup.

Recovery to a point in time

A recovery of an entire database to a specified earlier point in time using fully logged transactions in transaction log backups, along with database, file, or filegroup backups.

Recovery to a named transaction

A restoration of an entire database to a specified named mark (such as immediately before or after a specific transaction) using fully logged transactions in transaction log backups, along with database, file, or filegroup backups.


You can also perform a partial restoration of a database to a secondary server to extract needed data. A partial restoration restores only the needed filegroups.

Back Up All Necessary Databases

You need to back up all databases necessary to recover completely from any form of disaster. In addition to the applicable user databases, this includes backing up the appropriate system databases. You must back up the master database regularly using a full database backup (this is the only backup type available for master). The master database contains essential server-wide database objects, such as logins, backup devices, custom system and user error messages, and linked server definitions. You should also back up the msdb database regularly, generally using full database backups (although transaction log backups are sometimes used as well). The msdb database contains SQL Server Agent jobs, the Meta Data Services repository, and the history (and logic) of all database backups performed. Finally, if replication is involved, you must also back up the distribution database. SQL Server 2000 replication is covered in Chapter 15.

Develop and Implement a Data Restoration Plan

After you understand the types of database backups and the restoration options that are available to you, and after you determine the acceptable data restoration times, you need to develop and implement a data restoration plan (which includes a database backup component). Your plan should be in writing and should be reviewed periodically to determine whether the underlying data restoration requirements have changed. The plan should contain a variety of elements, including the following tasks:

  • Document each SQL Server 2000 (and SQL Server 6.5 or 7.0) installation, including operating system version, operating system service packs, SQL Server version, SQL Server service packs, data and transaction log file names (and locations), server names, network libraries, collation (character set and sort order for earlier SQL Server versions), and service account name. Store this information in a secure location and keep it current.
  • Document what databases are being backed up, how frequently, and using which types of backups. Consider documenting the reasons for the frequency and type of backups. The underlying reasons could change over time.
  • Determine what level of automation to implement. Consider implementing regularly scheduled backup jobs and using SQL Server performance condition alerts to back up the transaction log at a certain threshold. Jobs and alerts are covered in Chapter 13.
  • Determine who will be responsible for backups and who will verify that the backups actually occur.
  • Determine how you will verify the quality of the backups. Consider periodically testing backups by performing restores on a spare server. This is a good practice of your restoration skills, which will be tested when a disaster occurs.
  • Determine where to store backup media. Backup media should be secured, and some backup media should be stored offsite (such as in a safe deposit box or with a tape-vault company) to prepare for natural disaster and theft. Consider a fireproof safe onsite where you store several weeks worth of backups.
  • Determine how long to store backup media. Check legal requirements for tax records and similar data.
  • Document the backup and the server hardware.

Lesson Summary

Developing a data restoration plan involves determining your organization's acceptable level of database downtime for various types of disasters. Your job as a database administrator is to determine how to use RAID, implement backup and restore strategies, and use standby servers and clustering to achieve this acceptable level of downtime. As part of the backup and restore strategy, you need to determine the frequency and type of database backups to achieve desired speed of data restorations. You need to perform test restorations to verify that this speed can be achieved (and to keep your skills honed). Finally, you need to fully document how each part of this restoration plan will be implemented.

MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net