Chapter 12
Methods of Restoring Databases
Certification Objectives
*Analyzing Requirements *
Sizing Data to be Recovered
*Determining Value of Data
*Data Usage Patterns
*Environmental Issues
*Performance Issues
*Evaluating Backup and Restore Devices
*Using Multiple Backup Devices
*From the Classroom *
Full versus Differential Backup
*Testing Methodology
*Using File and Filegroup Backups
*File or Disk Corruption
*Environmental Disaster
*From the Classroom *
Hardware Failures
*Database Restores *
Database Restore Syntax
*Database Restore Using the Graphical Interface
*Restoring a User Database and Its Transaction Log *
Syntax for Restoring Transaction Logs
*Graphical Interface for Restoring Transaction Logs
*Restoring a Full Database and a Differential Backup *
Restoring a Filegroup Backup *
Restoring the Master Database with a Valid Backup
*Recovering the Master Database without a Valid Backup
*Setting Up a Standby Server *
Copying a Database Using Backup and Restore *
Syntax Changes *
Copying Databases Between Different Code Pages and Sort Orders
*Copying Databases from Earlier Versions
*Restoring a Database
*At this point, you have most likely figured out that the primary reason we perform the backup processes outlined in Chapter 11 is to prevent unexpected data loss. It is critical that you have solid backups and that you know the proper method for performing your restoration.
In this chapter we are going to focus on showing you the proper methods for restoring your data. We will break this into two primary categories: understanding the technology, and best practices. It is important to remember the world of an administrator: performance tuning may make you a star, but it is good backup and restore practices that provide you with job security.
Planning a Recovery Strategy
It is not uncommon for database administrators to fall into the trap of developing database backup strategies rather than developing what is really needed, a database recovery strategy. While this sounds like a game of semantics, it runs much deeper than that. The definition of a successful backup strategy is as follows: a repeatable process that provides a reliable and efficient method of restoring all user data in the event the database must be restored to its state at an earlier point in time. The only reason you perform backups is to restore files if needed. Given this, the importance of understanding restoration requirements becomes obvious. Lets take a closer look.
It is recommended that your recovery plan include the following:
When developing your recovery plan, it is important to address the issues outlined below. After you have addressed these issues, you will have the necessary guidance to design and implement the best SQL Server backup and recovery methods, utilize the appropriate devices, and backup media to use in your environment.
It is important to understand the volume of data that will need to be restored on your database server if a recovery is necessary. To gather this information, you need to address the following questions:
With the introduction of SQL Server version 7.0, more companies are migrating their mission-critical applications to the SQL Server environment. These applications bring a more challenging set of business requirements to the database administrator. It is not uncommon for these requirements to include availability 24 hours a day, 7 days a week, or close to it. These types of requirements make it increasingly difficult for the DBA to perform routine (scheduled) maintenance, much less deal with unexpected problems. It is critical that the DBA keep the downtime to a minimum in such situations as a hardware failure that require the database to be restored. This challenge is often compounded by the increased size of the databases that are used in mission critical applications.
The time required to recover a database and minimize the amount of lost data is dependent upon the amount of time and volume of transactions between backups. The optimal amount of time between backups is the time interval that results in the best balance of two opposing requirements:
The use of a combination of database, differential database, and transaction log backups can minimize the number of backups that need to be applied to bring the database to the point of failure. It can be tempting as an administrator to use a large number of transaction log and differential databases to minimize the time spent performing backups. The weakness of this strategy is exposed if the database must be restored during production hours. Restoring from multiple backups requires additional time as the same page may be replaced multiple times. This strategy also increases the potential for a backup to be corrupt, leading to data loss.
The time it takes to run backup and restore operations may vary greatly depending on the speed of the physical devices (tape or disk) used to store the backups. Typically, vendors will supply performance information that can be used to calculate optimistic estimates of the time required to back up a database. Because the vendors typically provide backup times for ideal conditions, it is a much better practice to manually run backup and restore operations to determine accurate recovery (or system duplication) times. There may be strict service level agreements (SLA) or guidelines that specify how long data can be unavailable. If so, then the restore process must operate well within the time limits.
Evaluating Backup and Restore Devices
SQL Server backs up databases, transaction logs, and files to backup devices. There are three separate types of devices supported for this function: disk, tape, and named pipe. By knowing the strengths and limitations of each, you can match the appropriate solution for your requirements. Lets take a look at each in more detail.
Disk backup devices are files on hard disks and are normal operating system files. Disk backup devices are referenced in exactly the same way as any other operating system file. Disk files can be defined on a local disk or on a network share on another server. The maximum file size for a disk device is equivalent to the free disk space available on the drive. If the backup is to be performed to a disk on a remote computer, use the universal naming convention name (OUNCE). Because disk devices are standard files, any user with the proper permissions can delete them. These files are only locked during the time that a backup is being run - not the entire time the SQL Server service is running.
Tape backup devices are used the same way as disk devices, with the following two exceptions:
To back up SQL Server data to tape, you must use a tape backup device that is supported by Windows NT.
Exam Watch: Tape backup devices must be physically attached to the server. It is not possible to use tape backup devices on remote computers.
Named pipe backup devices are supported to provide third-party vendors with a way to connect their own software and provide specialized backup and restoration capabilities. To use a named pipe backup device, you must provide the name of the named pipe as a part of a RESTORE Transact-SQL statement. Typically this is handled by the interface provided with the third-party vendor.
SQL Server backup supports the use of multiple devices simultaneously. This allows SQL Server to use parallel I/O to increase the speed of backup and restore operations. This is because each backup device can be written to or read from at the same time as other backup devices. For example, if it takes four hours to restore a database from a single tape drive, then the recovery time when two tape drives are used is likely to be two hours.
There are some limitations to this functionality. All backup devices used together to perform a single backup or restore must be of the same type - disk, tape, or named pipe. For example, to back up the Northwinds database daily using database and differential database backups to tape, only multiple tape drives can be used. As you see in Figure 12-1 below, restoring backups using multiple backup devices is the same as creating and restoring backups using a single device. The only difference is that you must identify all backup devices that are a part of the RAID set.
Figure 1: Restoring a Backup Set
In figure 12-1, if the database backup was created using three disk backup devices such as Northwind, Northwind2, and Northwind3, then each of the disk devices needs to be specified as part of the restore operation.
On The Job: When using multiple backup devices to perform backup and restore operations, the backup media involved can only be used for SQL Server backup operations. They cannot be shared with Windows NT backups.
Full versus Differential Backup
When designing a backup plan, it is always difficult to decide which is better, a Full or Differential Backup. It is normally much easier to have a full backup when restoring data. The following table can help guide you in selecting the best backup plan for different situations.
Situation | Recommendation |
Backing up a very large database a full backup takes 3 days, but daily backups are required. | Use differential backups, since your backup window will not allow for a full daily backup. |
A database of 2 GB needs to be backed up daily and is required 12 hours per day. | Ensure that your tape drive can backup more than 2 GB of data, and can perform a full backup daily. This will simplify your restore plans because you will need to restore data from only one tape. |
David Smith, MCSE + Internet
Once you have identified all potential events that may result in an outage, it is important to run tests and verify that your procedures work exactly as documented. It is also important to complete the recovery within the resource and time limits available. Best practices dictate that the procedures should be tested by someone other than the person who wrote the procedures. It is important to discuss the recovery times and user impacts with the business users prior to an outage. It is difficult to plan a restore procedure in the tense times that accompany a server outage.
It is not sufficient to perform these tests only once. A schedule for running tests that verify the procedures work is perhaps the most important step. Testing the restore procedures periodically will verify that as the amount of data increases, the following items occur: the data availability constraints (amount of allowable down time) are met, the number of permissible lost transactions are not exceeded, and the media being used is still reliable. Backup media such as magnetic tape do not have unlimited life. If the backups being performed do not verify the integrity of the backup, it is critical that you follow the specified limits for your tapes and take them out of service after the specified number of uses.
Using File and Filegroup Backups
Another technique for reducing the time required to perform database restoration is to reduce the amount of data being backed up. While a DBA cant limit the volume of data that the user community needs, he or she can analyze the data and determine if portions of the data could be restored at a later point or from alternative locations. SQL Server provides the ability to do this through file and filegroup backups as well as transaction log backups. These techniques allow just those files that contain the relevant data to be backed up. By limiting the backups in this manner, restores complete much faster.
Typical Scenarios
When planning recovery procedures, it is important that you consider all of the types of failures that might possibly affect your system. Very often people only consider the issue of file or disk corruption.
The most common incident that requires a system to be recovered is file or disk corruption. Disk corruption is typically caused by a hardware failure on the part of either the physical disk or the controller. Other anomalies such as power surges may also result in disk corruption.
File corruption describes a situation similar to disk corruption, but the impact is limited to isolated files. This may be the result of environmental problems such as a power surge, but is often the result of user error. If the MSSQLSERVER service is not started, all database files can be deleted from a command prompt or via Windows Explorer. Server Administrators have been known to delete these files as they work to free disk space. (NOTE: This is a good reason to use descriptive folder names.) When dealing with file or disk corruption, you may choose to make hardware repairs which means that your recovery plan must account for obtaining the parts. This may mean onsite hardware backups or a vendor agreement that guarantees replacements with a set time period.
If you have determined that it is critical that all transactions be saved, then you may opt for the use some form of RAID technology to protect your transaction log from being exposed to a single point of failure.
All servers have some vulnerability to environmental disasters such as fire, hurricanes, tornadoes, or flooding. It is important to identify which of these factors have the potential to impact your server and plan accordingly. For example, if you live in Illinois, you shouldnt spend a lot of time worrying about hurricanes. On the other hand, systems in Florida are not likely to experience earthquake damage.
When considering these potential disasters, it is critical to define the potential scope of the damage and factor this into your recovery plan. If a fire destroys the server, it is likely that all backup tapes that are stored onsite will be destroyed. If this is the case, your recovery plan must include using an offsite source for restoring data to hardware that will also need to come from an external location.
This will increase the time that it takes to restore your data. It will also severely limit your ability to recover transactions made after your last offsite backup was implemented. If this is not acceptable, you will need to either harden the building your server is stored in, or setup an offsite standby server that receives immediate updates.
Restoring a User Database and Its Transaction Log
As we mentioned earlier, this chapter will focus on restore procedures and how they fit into the scope of a recovery plan. In order to perform restores, it is important to understand the methods available and the impact they will have on the data in your system.
As with most administrative functions to be performed within SQL Server, there are two primary methods of performing any type of restore: through DB-Library syntax and via the Graphical User Interface provided in the SQL Server Enterprise Manager. We will discuss both methods as we step through each type of restore supported by SQL Server.
Most database restores are necessary because of hardware failures. If your database is running on a server class machine, and you have purchased a redundant drive system, you are well protected. It is very important to monitor your computer hardware for either real or predicted failures. Most servers come with software (such as Compaqs Insight Manager) that monitors your system continuously and reports problems back to your server manager via e-mail or pager. Monitoring your hardware can help avoid the job-threatening problem of having one drive in your mirror set failing, but not noticing the problem until the second drive fails.
David Smith, MCSE + Internet
In this section we will take a closer look at the SQL Server functionality that allows administrators to restore an entire user database. This is useful for situations where you have a need to restore the database to a single point in time. We will outline the SQL syntax that is used to perform this function, and we'll show the Graphical Interface tools that ship with SQL Server.
In the following code example you see the syntax that can be used to restore a database.
RESTORE DATABASE {database_name | @database_name_var}
FROM
<backup_device> [, ... n]]
DISK | TAPE | PIPE =
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]
]
In the syntax outlined above, you see a variety of variables that are available. These are defined below:
Exam Watch. Omitting the FROM clause can be used to attempt recovery of a non-suspect database that has been restored with the NORECOVERY option, or to switch over to a standby server. If the FROM clause is omitted, either NORECOVERY, RECOVERY, or STANDBY must be specified or you will receive error messages and no action will be taken by the server. .
RESTORE DATABASE Northwind
FROM DISK = 'c:\mssql7\backup\Northwind.bak'
RESTORE DATABASE Northwind
FROM TAPE = '\\.\tape0'
RESTORE DATABASE Northwind
FROM Northwind1
WITH NORECOVERY,
MOVE 'Northwind' TO 'd:\mssql7\data\NewNorthwind.mdf',
MOVE 'MyNorthwindLog1' TO 'e:\mssql7\data\NewNorthwind.ldf'
RESTORE LOG MyNorthwind
FROM MyNorthwindLog1
WITH RECOVERY
For example:
-- This database RESTORE halted prematurely due to power failure.
RESTORE DATABASE Northwind
FROM Northwind1
-- Here is the RESTORE RESTART operation
RESTORE DATABASE Northwind
FROM Northwind1 WITH RESTART
Exam Watch. When performing a Restore Database command, the sp_dboption settings are reset to the settings that were in use when the database backup was executed. When other restore commands are executed, these database settings are not affected.
Database Restore Using the Graphical Interface
Now that you have seen the syntax that can be used to restore your user databases, lets take a look at how this is performed using the Enterprise Manager. From the default form shown in Figure 12-2, you need to do the following: highlight the database that was used to create the backup you plan to restore and select Restore Database from the Tools menu.
Figure 2: Starting the Backup Process in Enterprise Manager
After you have selected the Restore Database option from your Tools menu, you will receive the form displayed in Figure 12-3. In this form you will have the opportunity to change the database from which you wish to use backups. When you have selected the appropriate database, Enterprise Manager automatically checks the MSDB to determine what types of backups have been performed on the database. It also makes the appropriate restore options available to you. In Figure 12-3, we have chosen to restore the database and transaction logs. Enterprise Manager also provides a list of the backups that have been performed, and allows you to select the backup that you wish to restore.
In comparison to our previous syntax, on this screen we select to do a database restore, provide the name of the database and backup devices, and select the File number if multiple backups have been performed on the same backup device.
Figure 3: Restoring a Database and Transaction Logs
After we have selected the proper database and backups that we wish to restore, we can set further options. As you see in Figure 12-4, by selecting the Options tab, we have the following options: the ability to determine if the tape ejects, whether or not we want to restore over an existing database, and what state the database should be left in. When we compare this to the previous syntax, we are selecting UNLOAD or NOUNLOAD, REPLACE or not, and we are selecting between RECOVERY, NORECOVERY, and STANDBY. You also have the opportunity to restore the data to a different database name.
Figure 4: Setting Restore Options
To process the restore, refer to the form shown in Figure 12-5. This provides a detailed status on the restore command. When the process is complete, you will be prompted with a message box telling you it was successful, or you will be provided with a detailed explanation of why the restore failed.
Figure 5: Restore in Progress
Restoring a User Database and Its Transaction Log
In many situations it is not sufficient to restore the database to the point-in-time when a full database restore was completed. For these situations, it is important that SQL Server allows you apply a backup of the transaction log after restoring your database. In this section, we will take a closer look at the SQL Server functionality that allows administrators to restore the database to a more current point in time. We will outline the SQL syntax that is used to perform this function, as well as show the graphical tools that ship with SQL Server.
Syntax for Restoring Transaction Logs
To restore your database to a more current state, you begin by performing a full database restore as outlined in the previous section (remembering to use the NORECOVERY option). When that has completed, it will be time to restore one more transaction log backup to bring the database up to date. Lets take a look at the options for performing his function.
Restore a transaction log:
RESTORE LOG {database_name | @database_name_var}
[FROM <backup_device> [,
n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] RESTART]
[[,] STATS [= percentage]]
[[,] STOPAT = {date_time | @date_time_var}]
]
Most of the syntax found in the Restore Log command provides the same function as discussed above in the Restore Database syntax section. We have outlined the different parameters below.
RESTORE LOG NorthwindFROM Northwind_logWITH RECOVERY, STOPAT = 'Jan 1, 1999 10:00 AM'
Graphical Interface for Restoring Transaction Logs
The process of restoring a transaction log through the graphical interface is very similar to that of restoring the database. After selecting Restore Database from the Tools menu, you will see the screen shown in Figure 12-6. By selecting to Restore backup sets from devices, you have the option shown at the bottom of the screen to restore a transaction log.
Exam Watch: It is not possible to restore a partial transaction log backup using the Enterprise Manager interface.
Figure 6: Restoring a Transaction Log
When restoring a transaction log, you can select the Options tab and receive the same set of options that we saw in Figure 12-4. This allows us to set the state of the database (RECOVER or NORECOVER) and that of the tape device if one is used (eject the tape or not).
Restoring a Full Database and a Differential Backup
Microsoft added the ability to perform differential database backups and restoration in version 7.0. This functionality is particularly useful for databases that are likely to incur heavy transactional load on a subset of data. In this situation, a transaction log backup could be as large as the database as it traps every update. A differential database backup has the advantage of only backing up pages that changed one time rather than every time they were modified.
There is no syntax or interface differences between restoring a full database backup and restoring a differential database backup. All changes occur during the backup. In implementation, a differential backup is applied in the same way that a transaction log backup is following a database restore. The example below shows how you can restore a full database backup, and then follow it with a differential backup restore.
RESTORE DATABASE NorthwindFROM Northwind_1WITH NORECOVERYRESTORE DATABASE NorthwindFROM Northwind_2
Microsoft added the ability to perform backups of files or filegroups in SQL Server 7.0. This functionality is useful for managing databases that contain a combination of tables with data from other systems, and tables with user generated data. In this situation, you may determine that you only need to use SQL Server backup tools to manage the tables with user generated data. This is due to the fact that the other tables could have their data restored from other locations. This technique reduces the amount of data that an SQL Server DBA must take responsibility for. The syntax for restoring files or filegroups is shown in the following code.
RESTORE DATABASE {database_name | @database_name_var}
<file_or_filegroup> [, ...m]
[FROM <backup_device> [,
n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] NORECOVERY]
[[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE]
[[,] RESTART]
[[,] STATS [= percentage]]
]
FILE = {logical_file_name | @logical_file_name_var}
FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
If you use this option, you must apply the transaction log to the database files immediately after the last file or filegroup restore operation. This is in order to roll the files forward so they are consistent with the rest of the database.
Recovering the Master Database
It is critical to maintain a current backup of your master database. The master database stores configuration information about SQL Server as well as information about all user databases. Because of this, as databases or backup devices are added, the Master is updated and thus a new backup should be obtained. Lets take a closer look at the steps to recover the master database.
If the master database becomes corrupted, then you must use this backup. If the Master Database is accessible enough for SQL Server to be started, and at least partly usable, then it is typically possible to restore the Master from your backup. If SQL Server cannot be started, however, because of severe damage to the Master, it is not immediately possible to restore a backup of the Master. In this situation, you will need to use the SQL Server Rebuild Master Utility. This tool can be found in the BINN folder on your hard drive, or on the CD-ROM. When you run the program (file name RebuildM.exe) you will be prompted by the screen shown in Figure 12-7.
(12)Figure 7: Rebuilding the Master Database
You must select the same character set, sort order, and Unicode Collation that you had previously selected if you intend to restore your backup of the master.
Restoring the Master Database with a Valid Backup
After you have rebuilt the master, you must restart SQL Server. At this point you will be able to complete a standard restore of the Master database, and you will then be able to access the data stored in your user databases.
Recovering the Master Database without a Valid Backup
If you do not have a current backup of the database, the recovery process becomes more involved and less reliable. For every user database that existed prior to the corruption of the master database, you must run the sp_attach_db stored procedure. In the example below, we are reattaching the Northwind2 database.
EXEC sp_attach_db @dbname = Northwind2,
@filename1 = 'c:\mssql7\data\Northwind2.mdf',
@filename2 = 'c:\mssql7\data\Northwind2.ldf'
When you have completed this task, all user databases should now be accessible. Depending upon your security model, you may need to re-add some users as well.
Specialized Data Restoration Scenarios
Beyond standard data recovery, there are a few situations where the backup and restore software provided with SQL Server can be used to meet special business needs. Lets take a look at a few of these situations.
For some production systems (such as a 911 call-center) it is critical that the system be available 24 hours a day, 7 days a week. While SQL Server provides very good availability, it is not reasonable to have hours of downtime for a hardware or software failure. For this reason, it is not uncommon to set up a standby server to help in these situations. This configuration keeps two servers constantly up to date. A standby server configuration is basically a poor-mans cluster server configuration. Exercise 12-1 will take you through the steps to set up a standby server.
Exercise 12-1: Setting Up a Standby Server
On the primary server:
On the standby server:
Copying a Database Using Backup and Restore
In many test environments, it is desirable to have multiple copies of a database with the same information. SQL Server allows us to create these from a single database using the backup and restore statements. We explained this syntax in prior sections so we will just hit the highlights here. In the example below, we will make a copy of the Northwind database. The move statement causes the data and log file to be restored to the locations we specify.
BACKUP DATABASE Northwind
TO DISK = 'D:\SQL\Nwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'D:\SQL\Nwind.bak'
RESTORE DATABASE Northwind2
FROM DISK = 'D:\SQL\Nwind.bak'
WITH MOVE 'Northwind' TO 'e:\mydata\Northwind.mdf',
MOVE 'NorthwindLog1' TO 'e:\mydata\NorthwindLog1.ldf'
GO
Compatibility Issues
In todays heterogeneous environments, we are constantly confronted with compatibility problems. The release of SQL Server 7.0 is no exception. We are going to outline some syntax changes from prior versions and discuss the impact they will have going forward. We will also discuss issues that impact sites with multiple SQL Server machines.
There are a few changes to the SQL Server syntax used with the restore process that you need to be aware of when working with an environment that has a mix of SQL Server 7.0 and SQL Server 6.5 database servers.
In earlier versions of SQL Server, the dump statement was used to create database or transaction log backups. The dump database and dump transaction statements are synonymous with backup database and backup log statements. The current release supports the dump statements for backward compatibility, but that support may be removed in a future release.
Earlier versions of SQL Server used the load statement to restore database or transaction log backups. The load database and load transaction statements are synonymous with the restore database and restore log statements. Support for the load statements has been built into the current version, but may be removed in a future release.
In the latest release of SQL Server, the create database ... for load syntax is supported for backward compatibility only. SQL Server 7.0 now creates the destination database within a restore operation. Therefore it is recommended that the destination database not be created before execution of the restore operation.
In prior versions of SQL Server, the volume option of the dump and load statements indicated the volume ID for a dump device. The volume keyword has been replaced by the medianame option and the use of the volume option results in an error.
As SQL Server environments grow to include multiple servers, it is not uncommon for administrators to have the need to move data from one server to another. This process also gets additionally complicated by different configurations or versions. Lets take a look at the issues that most sites must deal with.
Copying Databases Between Different Code Pages and Sort Orders
A database can only be backed up from one server running Microsoft SQL Server and restored on another if the servers use the same sort orders, Unicode collation, and code pages (character set), set up during installation. If a different sort order, Unicode collation, Unicode locale, or character set is used, the restore operation detects this and will terminate the database restore. To restore the database, the destination server must be reinstalled using the same sort order, Unicode collation, and character set as the server that performed the database backup. If the servers use a binary sort order, it is not necessary for the character sets to match. If the character sets are the same, all characters are interpreted identically between the two servers. If the character sets, however, are different, then any extended characters are interpreted differently on the two servers.
Copying Databases from Earlier Versions
SQL Server 7.0 does not support restoring a database backup from earlier versions of SQL Server. Database backups from SQL Server 6.5 or earlier are stored in a format that is incompatible with SQL Server 7.0. To convert a database from SQL Server 6.5 or earlier to SQL Server 7.0, you have several options:
In Exercise 11-1, you created a backup of the Northwind database to a device located at D:\MSSQL7\BACKUP\NW.BAK. Exercise 12-2 shows you how to restore the database. If you have not completed Exercise 11-1, you will need to complete it now before performing Exercise 12-2. If you stored your database backup in a different location in Exercise 11-1, you will need to adjust the following steps accordingly.
Exercise 12-2: Restoring a Database
Figure 8: Restoring the Database
Certification Summary
In this chapter we discussed both the planning and technology that combines to form a solid recovery scheme. Appropriate planning is very important to this process. By understanding the value of the data, and the usage patterns, a database administrator can provide the appropriate recovery plans for any conditions.
With the release of SQL Server 7.0, Microsoft has expanded the restore options that are available to the DBA. SQL Server 7.0 supports the following types of restores:
SQL Server supports three different types of backup devices: disk, tape, and named pipe. To assist in managing large databases with high availability requirements, backups and restores can be performed using multiple backup devices simultaneously. This allows databases to be restored much faster than in prior versions, thus minimizing down time.
Two-Minute Drill