Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Disaster Recovery > Recovering from Failure

Lesson 2. Recovering from Failure

This lesson concentrates primarily on how to recover from a failure of SQL Server 2005 server. The most likely sort of SQL Server 2005 server failure that you will encounter is a corruption of the program files or the system databases. This lesson deals with the strategies that the database administrator can employ if either of these events occurs. The final part of this lesson provides information about the differences between database recovery models and various database backup methods.

After this lesson, you will be able to:

  • Reinstall an instance.

  • Determine whether to restore the system database.

  • Understand the differences between recovery models.

Estimated lesson time: 30 minutes


Restoring the System Databases

If the system databases become corrupt, SQL Server 2005 is unlikely to start correctly. You have two basic options in this situation: restore the system databases from the regular backups that you have taken of them, or use the SQL Server 2005 installation media to rebuild the databases from scratch.

Best Practices: Backing up system databases

Remember to back up your system databases as well as your user databases. Some administrators become so focused on protecting all the data in the user databases that they forget that important information is also stored within the system databases. You don’t need to back up system databases as rigorously as user databases, but it is prudent to back them up on a reasonably regular basis. You should back up the master database after you create a database, change configuration values, or configure SQL logins. It is not possible to perform a differential backup on the master database.


If SQL Server cannot start because of a problem with the master database and backups of that database exist, start the server in single-user mode and execute the RESTORE DATABASE statement to restore the master full database backup. Remember, if changes were made to the master after the last backup was taken, you will lose those changes when you restore the master database. For example, logins that you created after you last backed up the master database will be lost. You will need to create them again by using either SQL Server Management Studio or any script that you used to create the logins. Note that database users who were associated with lost logins will be orphaned and will be unable to access the database. After you restore the master database, the instance of SQL Server is halted.

If a backup of the system databases does not exist or has itself become corrupted, or single user mode cannot be reached, you can rebuild the system databases using the SQL Server 2005 installation media. To rebuild system databases for a default instance of SQL Server 2005 from the command prompt, perform the following steps:

1.
Insert the SQL Server 2005 installation media into the drive. (These instructions assume that the DVD-ROM drive with the media is drive D.)

2.
Open a command prompt, and issue the following command:

start /wait D:\setup.exe /qn INSTANCENAME=<MSSQLSERVER>
								REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

Use the instance name MSSQLSERVER for the default instance. After you rebuild the system databases, you should be able to start SQL Server 2005 properly. From this point, perform a restore of the most recent full backup of the system databases. If you have attempted these strategies and SQL Server still will not start, you might need to repair all files and rebuild the registry.

To rebuild the registry of a damaged installation of SQL Server, you need to use the REINSTALL=ALL and REINSTALLMODE=OMUS parameters. Using these parameters rebuilds, repairs, and verifies a Microsoft SQL Server instance. Not only are system databases repaired, but program files and registry entries are repaired as well. It is important when you execute this operation that you ensure you use the same package file and options you specified during the original installation. If the original package file options are unknown, uninstall SQL Server completely and then perform a clean install rather than attempting to rebuild.

To repair all files and rebuild the registry at the same time, perform the following steps:

1.
Insert the SQL Server 2005 installation media into the drive. (These instructions assume that the DVD-ROM drive with the media is drive D.)

2.
Open a command prompt, and issue the following command:

start /wait D:\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=ALL
								REBUILDDATABASE=1 REINSTALLMODE=omus SAPWD=<NewStrongPassword>

When you rebuild the system databases using the distribution media, all service packs and updates are lost. To expedite the rebuild process, you can use the /qn switch in the command shown earlier to stop the display of setup dialogs and error messages. Rebuilding the master database places all system databases in their default locations. If in the original installation you moved these databases to alternate locations, you must manually move them again after the repair process is complete. As soon as possible, you should reapply these service packs and updates.

If the restored backup of a database is not current, you will need to re-create any missing entries manually. At this point, if you had previously configured the server instance as a replication Distributor, you need to restore the distribution database as well.

Recovery Models

Database recovery models determine the granularity to which it is possible to restore a database in the event of a failure. There are three possible recovery models, and each has its benefits and drawbacks. Each recovery model is detailed in the next few pages.

Note: Default recovery models

The master, msdb, and tempdb databases use the simple recovery model by default. The model database uses the full recovery model by default.


Simple Recovery Model

The simple recovery model has the least administrative overhead because it does not require you to back up the transaction log. When you implement the simple recovery model on a database, it is not possible to back up the transaction log and SQL Server automatically truncates the transaction log (that is, it drops inactive parts of the log to free up space) after each backup completes.

If the database is damaged, you can recover data only to the point at which it was backed up. For example, if the database is backed up only once a day at 3 A.M., and the database fails at 11 P.M., you will lose 20 hours of updates. When using the simple recovery model, you must strike a balance between the impact that backing up has on performance and the size of the window of time in which it will not be possible for you to recover data.

The simple recovery model is most appropriate for development databases or databases containing read-only data, such as data warehouses. If the loss of recent changes to the database is unacceptable, you should not use the simple recovery model.

Full Recovery Model

The full recovery model is the best possible option to prevent data loss. The full recovery model requires database and transaction log backups. The full recovery model also provides protection against media failure. To ensure that you do not lose transactions, you should host the transaction log on a fault-tolerant volume such as RAID 1 or RAID 5.

SQL Server 2005 allows you to back up the transaction log during a normal data or differential backup. The Enterprise Edition of SQL Server 2005 allows you to restore a database without the necessity of taking it offline. You can perform an online restore using SQL Server Enterprise Edition only if you have configured the database to use the full or bulk-logged recovery models.

Bulk-Logged Recovery Model

The bulk recovery model is a subset of the full recovery model. Under this model, SQL Server minimally logs bulk operations such as index creation or bulk imports. This arrangement improves performance and log space consumption; otherwise, SQL Server would need to enter each individual transaction in its entirety into the log. The drawback of the minimal logging of such operations is that only minimal records of the individual transactions that occur in the bulk operation exist. This means that it is impossible for you to perform a point-in-time restore. When a log backup contains bulk-logged operations, you can recover the database only to the end of the log backup. If something goes wrong in the middle of the bulk operation, it is impossible for you to run the log forward to a point just prior to the failure. The bulk-logged recovery model is in some respects an all-or-nothing approach.

To back up a log that contains bulk-logged operations, you need access to the data files that contain the bulk-logged transactions. This added data can make the log backup large.

Before implementing the bulk recovery model, you should be aware of the following restrictions:

  • If you make a filegroup containing bulk-logged changes read-only before performing a log backup, all subsequent log backups of that filegroup will contain the extents changed by the bulk operation as long as the filegroup remains read-only. A way of avoiding this limitation is to implement the full recovery model prior to making the filegroup read-only and backing up the log. After the backup is finished, you should then make the filegroup read-only.

  • If a log backup contains bulk-logged changes, you cannot perform a point-in-time recovery.

  • If bulk operations are performed while the bulk-logged recovery model is implemented, all files must be online or defunct during log backup.

  • Online restore sequences work only if all log backups were taken prior to the damage and the bulk changes were backed up prior to starting the online restore sequence.

Selecting a Recovery Model for a Database

Configuring a database to use a specific recovery model is relatively simple. To configure a database’s recovery model, perform the following steps:

1.
Open SQL Server Management Studio.

2.
Expand the Databases folder.

3.
Right-click the database for which you want to configure the recovery model, and choose Properties.

4.
In the Select A Page pane, select the Options page.

5.
Use the Recovery Model drop-down list to select the chosen recovery model, as shown in Figure 4-3. Click OK to save your changes.

Figure 4-3. Configuring file recovery options.


Quick Check

1.What factors can make a bulk recovery log backup large?
2.Which recovery model should you use if it is always necessary to restore to a particular named transaction?
3.In what situation should you NOT use the REINSTALL=ALL and REINSTALLMODE=OMUS repair options?

Quick Check Answer

1.Bulk recovery logs require access to the data files that contain the bulk-logged transactions.
2.The full recovery model should be used in this situation.
3.You should NOT use these repair options if you are unaware of which package options were used for the SQL Server installation.


Files and Filegroups

Understanding how files and filegroups work in SQL Server 2005 is an important part of understanding how to recover from a failure of SQL Server 2005. SQL Server databases are not stored as large binary blobs of information on a disk, but rather are stored as individual files and filegroups.

Files

Database data and log information are never collected in the same file. Individual files are never shared between databases. A SQL Server 2005 database uses three types of files:

  • The primary data file is the database starting point. Each database has a single primary data file. Primary data files use the .mdf extension.

  • Secondary data files host all data that is not located in the primary data file. Not all databases have secondary data files, and you must explicitly create them. Using secondary data files can improve performance. You often place secondary files on different volumes from the one that stores the primary data file. Secondary data files use the .ndf extension.

  • Log files host information used in the recovery of databases. Each database must have at least one log file. Log files use the .ldf extension.

Both the database’s primary file and the master database keep records of the location of each file that constitutes an individual database. SQL Server 2005 does not enforce the file name extensions mentioned in the preceding list, though these extensions are helpful for identifying different kinds of files.

SQL Server 2005 files are identified by both a logical and physical file name. The differences between these names are as follows:

  • logical_file_name A nickname you assign to the file. You use this nickname to refer to the file in Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers. It must be unique among logical file names in the database.

  • os_file_name Name of the file and its location in the file system.

For example, when you install it using the default settings, the AdventureWorks primary database file has the two names listed in Table 4-1.

Table 4-1. Logical and Physical File Names
logical_file_nameAdventureWorks_Data
os_file_nameC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf


The state of a file is independent of the state of the database. Files can have the six states described in Table 4-2.

Table 4-2. SQL Server 2005 File States
StateDefinition
ONLINEThe file is available for all operations.
OFFLINEThe file is not available for access. Files are set offline when they are corrupted but can be restored. You can set an offline file to online only by restoring it from backup.
RESTORINGThe file is being restored.
RECOVERY PENDINGRecovery of the file has been postponed. Files enter this state because of a piecemeal restore process in which the file is not restored and recovered.
SUSPECTFile recovery failed during online restore. If the file is located in the primary filegroup, SQL Server marks the database as suspect. The file remains in this state until you make it available via restore and recovery, or DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS.
DEFUNCTThe file was dropped when offline. When an offline filegroup is removed, all files in that filegroup become defunct.


Filegroups

Database filegroups allow you to group database objects and files together. SQL Server 2005 uses the following two types of filegroups:

  • Primary filegroup Contains the primary data file and all other files that are not assigned to other filegroups. System tables and their pages are allocated to the primary filegroup.

  • User-defined filegroups Contains tables, indexes, and large object data that have been associated with a specific filegroup. You can place data in partitioned tables and indexes in separate filegroups to improve performance.

Log files are not part of any filegroup and are treated as objects distinctly different from database files. One filegroup in each database is set as the default filegroup. The default filegroup stores all tables or indexes that you create without specifying a filegroup. If you do not configure a default filegroup, the primary filegroup automatically takes this role.

Filegroups are online only if all files within the filegroup are online. Files in the primary filegroup are online if the database is online. If a file in the primary filegroup goes offline, the database goes offline. Any attempt to access an offline filegroup with a Transact-SQL statement results in an error.

You can mark all filegroups, except the primary filegroup, read-only. A read-only filegroup cannot be modified. You should store tables that contain fixed data, such as those in a data warehouse, in read-only filegroups. Read-only filegroups also support NTFS compression. Note, though, that compression reduces performance, and you should consider compression only in cases where the data is not accessed often. SQL Server system databases such as master, model, msdb, resource, and tempdb do not support compression.

Backup Types

SQL Server 2005 allows four categories of backup: data backup, differential backup, transaction log backup, and copy backup.

Note: Backups

Although the objectives for Exam 70-444 do not actually mention the word backup, a description is provided in this book to make understanding the mechanics of possible restore operations easier.


Data Backups

Data backups include the entire image of one or more data files or filegroups. There are three kinds of data backups:

  • Database backup A full backup of the entire database. This includes a part of the transaction log, which allows the full database backup to be recovered. These backups are complete and represent the entire database at the time the backup completed. You can estimate the size of a full database backup by using the sp_spaceused system stored procedure.

  • Partial backup Partial backups differ from database backups in that they do not contain all the filegroups. Partial backups must contain all data in the primary filegroups, every read-write filegroup, and nominated read-only files. A partial backup of a read-only database contains only the primary filegroup.

  • File backup File backups are often used to increase recovery speed by allowing only the restoration of damaged files rather than the restoration of the entire database. This is useful when the database contains several files located on different volumes and one of those volumes fails. In such a situation, you need to restore only the file on the failed volume.

Differential Backups

Differential backups are taken in relation to a specific differential base. Differential bases include a full database backup, a partial database backup, or a file backup. Occasionally, differential backups are taken in relation to a set of bases (multibased differential). A differential backup covers the same set of files as the base backup, but it backs up only the extents that have changed since the base was created by the original data backup. The three types of differential backups are as follows:

  • Differential database backup Backs up all files in the database containing extents modified since the most recent database backup

  • Differential partial backup Backs up data extents from a previous partial backup that were modified since that partial backup was taken

  • Differential file backup Backs up files containing data extents changed since the most recent full database backup of each file

Transaction Log Backups

You can perform transaction log backups only during a full database backup. There are three types of transaction log backups:

  • Pure log backup Contains only transaction log records for an interval. A pure log backup does not contain bulk changes performed under the bulk-logged recovery model.

  • Bulk log backup Includes log records and data pages changed by bulk operations. Point-in-time recovery on bulk-logged backups is disallowed.

  • Tail-log backup A backup taken just before restoring a database to capture records that have not been backed up. Tail-log backups have the following properties:

    1. They are the last backups of interest in a recovery plan.

    2. Restoring a database without performing a tail-log backup results in an error unless the restore statement contains either the WITH REPLACE or WITH STOPAT clauses.

    3. They can be attempted if the database is damaged or not online. They succeed when the log files are undamaged and the transaction log does not contain any bulk-logged changes.

    4. They can be created independently of regular log backups by using the COPY_ONLY option. The transaction log is not truncated when using the COPY_ONLY option.

    5. They can contain incomplete metadata if the database is damaged. This can occur if the log backup is taken with CONTINUE_AFTER_ERROR, because backup occurs independently of the state of the database.

Truncating the Transaction Log

Truncating a transaction log is the process whereby you free up space in the log by deleting the inactive parts of the log. Active parts of the log are unaffected by truncation. If all parts of the transaction log are active, truncation is delayed. When using the bulk-logged or full recovery model, inactive parts of the transaction log are truncated after each automatic checkpoint. Automatic checkpoint intervals are based on the amount of log space used and the time elapsed since the last checkpoint. If few modifications are made to the database, the time interval between checkpoints can be long. If the database is constantly being modified, automatic checkpoints occur more frequently.

After an initial data backup has occurred, SQL Server does not truncate inactive parts of the transaction log until they have been backed up. This happens regardless of any automatic checkpoints that might occur. The only way to stop the transaction log from filling is to ensure that you regularly back it up. Backing up the transaction log automatically truncates it, freeing up space.

To determine how full the transaction log is, use the DBCC SQLPERF (LOGSPACE) command as shown in Figure 4-4.

Figure 4-4. Checking the amount of transaction log space.


Copy Backups

Copy backups are a feature new to SQL Server 2005, and they do not affect the overall backup and restore procedures. For example, you might have a backup scheme where you take a full database backup every third day and differential backups every six hours. From the perspective of the next differential backup, taking a copy backup of the full database does not count as a database backup, and SQL Server backs up only extents that have changed since the original full database backup.

You often use copy-only backups when preparing an online restore because a copy-only backup does not affect the sequence of regular transaction logs. You should note that you cannot directly perform a copy backup using the SQL Server Management Studio interface. You can accomplish the creation and restoration of copy-only backups only by using Transact-SQL statements. COPY_ONLY backups are recorded in the backupset table in the is_copy_only column.

Snapshot Backups

Snapshot backups provide an almost instantaneous copy of the data that they back up. Snapshot backups require the installation of extra software and hardware from independent software vendors on the computer running SQL Server 2005. The benefit of snapshot backups is that they minimize the use of SQL Server resources in accomplishing the backup. Snapshot backups work either by splitting a mirrored disk set or by creating a copy of a disk block at the time of writing.

Note: Database snapshots and snapshot backups

Although they are similarly named, do not confuse snapshot backups with database snapshots. The name snapshot backups is evocative of the speed of the backup. A database snapshot is an image of the database taken at a specific point in time.


The benefits of snapshot backups are as follows:

  • Creates an almost instantaneous backup with minimal impact on the server

  • Restores backup almost instantaneously

  • Backs up to tape on another host without affecting the performance of the production system

  • Generates a copy of the production database almost instantly for the purposes of reporting or testing

You cannot perform an online restore from a snapshot backup. Restoring a snapshot backup takes the database offline. Piecemeal restores can incorporate snapshot backups, but all restore sequences will be offline restores. Snapshot backups are tracked in the msdb database and are identified by the entry backupset.is_snapshot = 1.

Practice: Use the Full Recovery Model and Back Up a Database

In this practice, you configure the AdventureWorks database to use the full recovery model. You then take a full database backup. After you take the full database backup, you perform a differential backup of the database.

1.
If necessary, open SSMS and connect to the local instance.

2.
Expand the Databases folder.

3.
Right-click the AdventureWorks database and choose Properties. This opens the Database Properties dialog box.

4.
In the Select A Page pane, select the Options page.

5.
Ensure that the recovery model is set to Full and then click OK.

6.
Right-click the AdventureWorks database, choose Tasks, and then choose Back Up. This opens the Back Up Database dialog box, as shown in Figure 4-5.

Figure 4-5. The Back Up Database dialog box.


7.
Ensure that the Backup Type drop-down list shows Full as the selected option, and ensure that the Backup Set section shows the Name selected as AdventureWorks-Full Database Backup. Click OK.

The backup process takes several moments to complete.

8.
Click OK to close the Successful Database Backup message box.

9.
Right-click the AdventureWorks database, choose Tasks, and then choose Back Up. This again opens the Back Up Database dialog box.

10.
Set the Backup Type to Differential, and then click OK to start the backup.

11.
Click OK in the successful database backup message box.

12.
Right-click the AdventureWorks database, choose Tasks, and then choose Back Up. This again opens the Back Up Database dialog box.

13.
Change the Backup Type to Transaction Log.

14.
In the Select A Page pane, select the Options page.

15.
Ensure that the Truncate The Transaction Log option button is selected.

16.
Click OK to start the transaction log backup.

17.
Click OK in the Successful Database Backup message box.

Lesson Summary

  • You can restore the system databases in single-user mode and use the SQL Server 2005 installation media.

  • If SQL Server 2005 still does not start after you have rebuilt the system databases, you might need to rebuild the registry and repair the instance. You can do this by using the REINSTALL=ALL and REINSTALLMODE=OMUS parameters from the start command of the SQL Server media.

  • The simple recovery model requires the least administrative effort because it does not require you to back up the transaction log. When you implement this model, SQL Server automatically truncates the transaction log after each backup completes. The simple recovery model does not allow point-in-time or named transaction restore. Restore is possible only to the point where the database was backed up.

  • The full recovery model provides the best option for recovering data. It supports point-in-time, named transaction, and online restore. The transaction log is truncated only after it is backed up.

  • The bulk logged recovery model is similar to the full recovery model except that it will only minimally log bulk operations. The disadvantage of this model is that it cannot provide point-in-time or named transaction restore unless no bulk operations have occurred since the log was last backed up.

  • A database backup is a full backup of the entire database. This includes a part of the transaction log, which allows the full database backup to be recovered. These backups are complete and represent the entire database at the time the backup completed.

  • Partial backups differ from database backups in that they do not contain all the filegroups. Partial backups must contain all data in the primary filegroups, every read-write filegroup, and nominated read-only files.

  • File backups are often used to increase recovery speed by allowing the restoration of only damaged files rather than restoring the entire database.

  • Truncating a transaction log is the process whereby space is freed up in the log by deleting the parts of the log that are inactive.

Lesson Review

You can use the following questions to text your knowledge of the information in Lesson 2, “Recovering from Failure.” The questions are also available on the companion CD if you prefer to review them in electronic form.

Note: Answers

Answers to these questions and explanations of why each answer choice is correct or incorrect are located in the “Answers” section at the end of the book.


1.You are unsure of which options were used when SQL Server 2005 was installed. An error on the disk has caused many program files to become corrupt. Which course of action should you pursue to get SQL Server 2005 working again?
  1. Restore the system databases in single-user mode.

  2. Use the installation media to rebuild the system databases.

  3. Use the installation media with the REINSTALL=ALL and REINSTALLMODE=OMUS switches.

  4. Uninstall SQL Server 2005, and perform a clean reinstall.

2.Which recovery model always allows the database administrator to recover to a particular named transaction?
  1. Simple

  2. Bulk-logged

  3. Full

3.Under which circumstances can a file in the primary filegroup be offline?
  1. The database is online.

  2. The database is offline.

  3. The primary filegroup is marked read-only.

  4. The primary filegroup is compressed.

4.What sort of backup should you always try to take prior to performing a database restoration when a database uses the full recovery model and is online?
  1. Tail-log backup

  2. Bulk log backup

  3. Full database backup

  4. Differential backup

  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint