Lesson 3: Understanding the Restoration Process
3 4
To implement an effective backup and restore strategy, you must understand the SQL Server 2000 restoration process. In this lesson, you learn how SQL Server 2000 performs automatic and manual restorations of data. You will learn about using various types of backups to perform full restorations quickly. You will also learn about partial restoration options that are available with certain types of database backups. Finally, you will review the restoration process given several disaster scenarios.
After this lesson, you will be able to
- Describe the automatic recovery process
- Describe the manual recovery process
- Understand how different types of database backups can increase data restoration speed
- Understand the full and partial data restoration options
- Understand data restoration paths for different disaster scenarios
Estimated lesson time: 15 minutes
Understanding the Recovery Process
SQL Server 2000 has two recovery processes: an automatic process that occurs each time you start SQL Server 2000 and a manual recovery process that you initiate. Understanding the automatic recovery process will help you understand the manual recovery process.
Automatic Recovery Process
The automatic recovery process is designed to ensure that once SQL Server 2000 has started, the data in each database is logically consistent, regardless of how or why SQL Server 2000 was shut down. SQL Server 2000 accomplishes this task by using the transaction log. It reads the active portion of the transaction log for each database and examines all transactions that have occurred since the most recent checkpoint. It identifies all committed transactions and rolls them forward. This means reapplying them to the database. It then identifies all uncommitted transactions and rolls them back. This means ensuring that any uncommitted transactions that were partially written to the database are removed. This process ensures that a logically consistent state exists for each database. The automatic recovery process then issues a checkpoint to mark the transaction log as consistent as of this point.
SQL Server 2000 begins by recovering the master database. The master database contains the information it needs to locate, open, and recover the remaining databases. Next, it recovers the model and msdb databases (and the distribution database if it exists). Next, it recovers each user database. It finishes with clearing and starting the tempdb database. You can examine the restoration process by reviewing the SQL Server error log, a sample of which is shown in Figure 8.1.
Figure 8.1
The SQL Server error log.
You cannot control this automatic recovery process directly. However, you can control the maximum amount of time SQL Server 2000 will take to perform the automatic recovery. The default value is 0, which means that SQL Server 2000 will dynamically determine how often it issues a checkpoint. The more frequently checkpoints are issued, the smaller is the portion of the transaction log that must be rolled forward and rolled back. In general, you should rarely need to adjust this value. As with most SQL Server 2000 settings, letting SQL Server 2000 adjust itself dynamically will generally yield the best performance over time.
Manual Recovery Process
The manual recovery process involves applying one or more database backups and then manually recovering them either completely or to a specified point. At the end of the manual recovery process, the database will be logically consistent. The recovery might consist of applying a full database backup, optionally applying the most recent differential database backup, and then applying several transaction log backups. As each database backup is applied, it is marked for no recovery. This means that additional restoration will occur before recovery occurs. After recovery occurs, no further restoration is possible. When the final restoration occurs, it is marked for recovery and SQL Server 2000 rolls forward and rolls back appropriate transactions using the transaction log.
In between the application of each backup, the database is not recovered and is generally not usable. However, you can restore a database to standby (read-only) mode without performing recovery. This allows you to view the state of the data after the application of each backup to identify a point in the transaction log where data restoration should stop (such as the point where a user or application error occurred). Once the point of restoration is identified, recovery must occur to bring the database online in a logically consistent state. After recovery has occurred, no further restoration can occur.
Understanding Manual Recovery Options
All of the database backup types allow you to recover a database, a file, or a filegroup to the end of the most recent transaction log backup. In addition, certain types of backups allow you to recover your data to some point earlier in time than the end of the most recent transaction log backup (such as before unwanted data was entered or before certain data was deleted).
Restoring Databases
When you want to restore a database to the end of the most recent transaction log backup, you start with your most recent full database backup. You can restore this full database backup to any instance of SQL Server 2000, not just to the instance from which it was backed up. If you are using differential database backups, you then restore the most recent differential database backup. Finally, in sequence, you then restore each transaction log backup that is more recent than the most recently restored full or differential database backup. As part of the restoration of the final transaction log backup, SQL Server 2000 performs a manual recovery (you must specify this), rolling forward and rolling back outstanding transactions as appropriate. Your database is restored with no data loss.
Note
You can restore SQL Server 7.0 backups to a SQL Server 2000 instance (but not vice versa).
If the most recent full database or differential database backup is damaged or missing, you can still restore using earlier transaction log backups. Thus, if you maintain a complete chain of transaction log backups, you can always recover as long as a single full database backup exists along with all of your transaction log backups. Obviously, applying these additional transaction log backups will take additional time. You perform regular full and differential database backups to reduce the recovery time by requiring the application of fewer transaction log backups. Keeping and securing (and duplicating) a full chain of transaction log backups provides additional fault tolerance in case of damaged or lost backup media.
Restoring Files and Filegroups
When you want to restore a file or a filegroup to the point of the most recent transaction log backup, you start with the most recent backup of the file or filegroup. This recent backup can be either from a file or filegroup backup, or from a full database backup. Restoring a single file from a full database backup takes longer than restoring a file from a file backup. If you are using differential file or filegroup backups, you restore the most recent differential file or filegroup backup. Finally, in sequence, restore each transaction log backup that is more recent than the most recently restored differential file or filegroup backup. As part of the restore of the final transaction log backup, SQL Server 2000 performs a manual recovery (you must specify this), rolling forward and rolling back outstanding transactions as appropriate. Your file or filegroup is restored with no data loss.
Unlike full and differential database backups, file and filegroup backups must have transaction log backups applied to them to make the restored file or filegroup logically consistent with the rest of the database. If you are restoring an entire database using file or filegroup backups, the loss of any single backup media can render the entire database unrecoverable.
Note
Restoring all data files or all filegroups and applying all transaction logs is functionally equivalent to restoring an entire database.
Restoring and Recovering to an Earlier Point in Time
Sometimes you might want to recover to an earlier point in time because of some type of user or application error. You can accomplish this by recovering a database either to a specific point in time within the transaction log or to a named mark within the transaction log.
To recover to a specific point in time, you restore the full database backup and optionally a differential database backup. You then restore the transaction log backups in sequence to the point in time you want to recover to. When you restore the final transaction log that you want to restore, you specify recovery only to a specific point in time within that transaction log backup. Through the use of either the header information of each transaction log backup or the information in the backupset table in the msdb database, you can easily identify the transaction log backup that contains the time to which you want to recover.
Note
Point-in-time recovery is recovery of the database. Once you recover to a specific point in time, you cannot recover to a more recent point in time. This means that you cannot use point-in-time recovery to restore a database to standby mode, view that state of the database as of a certain time, and then apply additional transactions.
To recover to a named mark, you must insert marks into the transaction log as part of a transaction. The mark is recorded as a row in the logmarkhistory table in the msdb database. During recovery, you can recover and roll forward to the mark and either include or exclude the mark.
However, recovery to a specific point in time or a named mark is not supported if the final transaction log backup you want to restore contains a bulk-logged transaction that was logged using the Bulk-Logged Recovery model. In addition, you cannot restore a single file or filegroup to a point in time or named mark without restoring the entire database to that particular point.
Recovery from Several Different Disaster Scenarios
Understanding the restoration process from the following disaster scenarios will help you determine backup and restore (and fault tolerance) strategies you will use in your data restoration plan.
User Data Disk Failure
If a disk that contains a data file fails, your restoration path will depend upon whether you have employed RAID for fault tolerance. If you have, you simply replace the disk that failed, reset the RAID configuration, and let RAID rebuild the data. You measure your downtime by the length of time required to replace the disk and reconfigure RAID. If your implementation of RAID supported hot swapping or hot standby, there will be no downtime.
If you have not employed RAID or have employed RAID 0, you must restore your data using database backups. First, you must back up the currently active transaction log using the no truncate option to restore your data completely. Next, you restore the full database backup and optionally the most recent differential backup, specifying no recovery for each restoration. Finally, you restore each necessary transaction log in sequence, specifying no recovery for each restoration until the final transaction log backup. On the final transaction log, restore it specifying recovery. SQL Server 2000 will roll forward and roll back appropriate transactions, and your database will be restored with no data loss.
If your data file and your transaction log file are on the same disk, you can only restore up to your most recent transaction log backup. All other data will have to be regenerated using other means.
User Transaction Log Disk Failure
If a disk that contains a transaction log file fails, no data restoration is needed unless the transaction log file and a data file share the same disk. However, if the transaction log file was not mirrored using RAID 1, you have lost your ability to completely restore your database should a disk containing a data file also fail. In this case, you should immediately back up the entire database using either a full or a differential backup (and make a copy of the backup media). Then, you should replace the failed disk.
Master Database Disk Failure
If a disk containing the master database fails and the master database was not mirrored using RAID 1, you must restore the master database from backup followed by a restoration of each of the necessary system databases. Next, you can either restore each user database from backup or reattach them if their data and transaction log files are intact on other disks.
Lesson Summary
Restoring a database, file, or filegroup from backup requires the database to be recovered to a logically consistent state. SQL Server 2000 uses transaction log backups to perform this task in a manner similar to the way SQL Server 2000 employs automatic recovery each time SQL Server 2000 starts. You can use a full and a differential database backup along with all applicable transaction log files in sequence to completely restore your data from a disk or system failure. For larger databases, you can use file and filegroup backups (and differential file and filegroup backups) along with all applicable transaction log files to completely restore your data from a disk or system failure in a reasonable length of time. You can also recover an entire database to a specific point in time or a named mark in the transaction log provided that the final transaction log backup you want to apply does not contain minimally logged bulk operations.
EAN: N/A
Pages: 126