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.
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.
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.
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).
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.
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).
Note
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.
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.
Note
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 | Description |
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 | Description |
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. |
Note
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.
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:
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.