Chapter 15
Recovery



by Mark Spenik

In the previous chapter, you learned how to back up a database, table, and transaction log and how to set up a backup schedule to prevent data loss. In this chapter, you learn how to use the database, table, and transaction log backups to restore a corrupted database or move a database or table to another server. This chapter builds on what you learned in the previous chapter and puts your backup and recovery plans to the test. A restore operation is also referred to as a database load (after the Transact SQL LOAD statement used to restore a database, table, or transaction log).

This chapter explains the steps to follow when a database becomes corrupted and the DBA restores (recovers) the database. But first, let's look at another type of database recovery performed by SQL Server: automatic recovery.

Automatic Recovery

Whenever SQL Server is started, a process called automatic recovery occurs. During the automatic recovery process, SQL Server checks each database for uncommitted transactions to roll back or for committed transactions to roll forward.

You may be wondering what it means to roll a transaction back or to roll a transaction forward. First, you must understand what a transaction is. In simple database terminology, a transaction is defined as a unit of work. A transaction can consist of a single SQL statement that modifies one row or 10,000 rows or a transaction can consist of many SQL statements bunched together as a single unit of work.

The following is a single SQL statement:

Insert jobs
Values(12, "Write SQL Books", 1,4)

The following are multiple SQL statements:

Begin TRAN
Insert jobs
Values(12, "Write SQL Books", 1, 4)
if(@@error != 0)
begin
Rollback tran
else
Update employee
set job_id = 12
end
if(@@error = 0)
Commit TRAN
else
Rollback TRAN


NOTE: Data modification statements such as INSERT, DELETE, and UPDATE are treated as transactions. To group several data modifications together to be treated as a single transaction, use one of the following syntaxes:

BEGIN TRAN

BEGIN TRANSACTION

To commit the changes (that is, to save them in the database), use one of the following syntaxes:

COMMIT TRAN

COMMIT TRANSACTION

To roll back the changes (that is, to not save them in the database), use one of the following syntaxes:

ROLLBACK TRAN

ROLLBACK TRANSACTION


If a transaction is written to the database, the transaction is said to be committed. A transaction is said to be rolled back when the transaction is written to the data cache and transaction log and then removed from the log without being written to the database. Using the diagrams in Figure 15.1, walk through a series of transactions and a SQL Server automatic recovery.

Step 1: Start Transactions

Figure 15.1 shows two users issuing two separate transactions to SQL Server called TRAN1 and TRAN2. SQL Server receives the transactions and marks the start of transactions TRAN1 and TRAN2 in the transaction log. TRAN1 modifies page 5 in the cache; TRAN2 modifies page 7 in the cache. No data has been written to the transaction log or the database.

Step 2: Commit Transaction TRAN1

User 1 sends a statement to SQL Server to commit TRAN1. SQL Server writes the committed transaction to the transaction log and marks page 5 in the cache as a dirty page. The transaction is still not written to the database.

Figure 15.1.
Transactions and automatic recovery.


NOTE: SQL Server uses a write-ahead log scheme. Data is written to the transaction log first before it is written to the database. The term dirty page refers to a page in the disk cache that has been modified by a committed transaction; the page has been written to the transaction log but not to the database (pages modified by uncommitted transactions are considered active and not dirty).

Step 3: Power Outage--SQL Server Unexpectedly Shuts Down

Before SQL Server issues a checkpoint in the database and User 2 can commit TRAN 2, a power outage occurs, bringing down the NT server and SQL Server.


NOTE: Dirty pages are written to the database when SQL Server performs an automatic checkpoint or when a user (the database owner or sa) issues a CHECKPOINT command in the database. A checkpoint causes all dirty pages to be written to the database and typically takes a second or two to execute. The frequency of automatic checkpoints depends on the SQL Server configuration parameter recovery interval. The recovery interval parameter determines the maximum number of minutes required to recover a database. The CHECKPOINT process checks every minute to see which databases have matched or exceeded the recovery interval and performs a checkpoint in each of these databases. For more information on the recovery interval, see Chapter 19, "Which Knobs Do I Turn?"

Step 4: Restart and Automatic Recovery

In this step, NT server is restarted and restarts SQL Server. SQL Server begins to go through the automatic recovery process. The master database is first checked, followed by the model database and the temporary database; tempdb is cleared; the scheduling database is recovered, followed by the pubs database and the distribution database for servers configured as a replication distributor. Last, but not least, the user databases are recovered. In the user database, the committed transaction TRAN1 is rolled forward, that is, it is written to the database. The uncommitted transaction, TRAN2, is rolled back, that is, it is removed from the transaction log without being written to the database. You can view the results of the SQL Server automatic recovery process by viewing the error log shown in Listing 15.1 or by viewing the NT event log.

Listing 15.1. Partial error log listing of the SQL Server automatic recovery process.

96/05/07 20:27:40.67 spid10 Recovering database `pubs'
96/05/07 20:27:40.69 spid11 Recovering database `msdb'
96/05/07 20:27:40.70 spid12 Recovering database `test'
96/05/07 20:27:40.74 spid11 Recovery dbid 5 ckpt (1283,20) oldest tran=(1283,19)
96/05/07 20:27:40.75 spid11 1 transactions rolled forward in dbid 5.
96/05/07 20:27:40.75 spid10 Recovery dbid 4 ckpt (777,24) oldest tran=(777,23)
96/05/07 20:27:40.76 spid10 1 transactions rolled forward in dbid 4.
96/05/07 20:27:40.77 spid12 Recovery dbid 6 ckpt (1522,27)
96/05/07 20:27:41.62 spid1 Recovery complete.


TIP: When the transaction log is truncated, only committed transactions up to the oldest open active transaction are removed. An outstanding transaction can result in the inability of the transaction log to be cleared when truncating. If you suspect you are having this problem, use the DBCC command option OPENTRAN for information on the oldest outstanding transaction. If you have an open transaction preventing you from clearing the transaction log, in SQL Server 6.x you can use the spid ( system process ID) displayed in the DBCC output and terminate the offending process using the KILL command. In SQL Server 4.2x, all users were required to log off SQL Server and the server had to be shut down and restarted to close the open transaction.

Loading a Database or Table Backup or Applying a Transaction Log Backup Using the Backup History Entry

Before covering the steps required to recover a corrupted database, examine how to use the SQL Enterprise Manager to load a database or table backup or apply a transaction log backup using a backup history entry. The term backup history entry is used to represent the backup history information maintained by SQL Server 6.5 and stored in the msdb database in the system tables sysbackupdetails, sysbackuphistory, sysrestoredetails, and sysrestorehistory. When a successful backup of a database, table, or transaction log occurs in SQL Server 6.5, the information is stored in the database msdb system tables. Using the backup history entry simplifies the restoration process: you don't have to know which, what, or how many devices the backup is on. The backup history information is a new SQL Server 6.5 feature. The steps to load an existing database using the backup history differ slightly from loading a database using backup devices. I highly recommend using the backup history entry except in the case where backup history does not exist (such as in the case of moving a database to another server).


NOTE: You can perform the following to load or recover databases; however, to recover a damaged master database, you must run a special utility, described later in this chapter.

From the SQL Enterprise Manager, select Tools and then select Database Backup/Restore. The Database Backup/Restore dialog box appears (see Figure 15.2). Click the Restore tab (see Figure 15.3).

Figure 15.2.
The Database Backup/Restore dialog box.


To load a database, table, or transaction log backup, perform the following steps:

  1. Using the Database drop-down combo box in the Restore page of the Database Backup/Restore dialog box, select the database to which you want to restore. The backup history for the selected database is displayed in the Backup History frame (see Figure 15.4).

  2. To load a database or transaction log backup, select the Database and/or Transaction Logs radio button. To load a table, select the Single Table radio button and then select the table you want to load from the drop-down combo box.

  3. Select the proper checkbox for the database, transaction log, or table backup history entry from which you want to restore in the Backup History frame. The Backup History frame displays various information about the backups for the database (for example, the type of backup, the start and end time of the backup, and the size of the backup to name a few of the items). For additional information about the selected backup, click the Info button; the Backup History Information dialog box appears (see Figure 15.5). The Backup History Information dialog box displays further information for the selected backup such as the device or devices on which the backup is stored. By using a backup history entry to restore from, you no longer have to worry about the device on which the backup resides--in the case of a striped backup set, you no longer have to remember which devices comprise the backup set--because the information is stored in the backup history entry.

    Figure 15.3.
    The Restore page of the Database Backup/Restore dialog box.


    Figure 15.4.
    The Restore page of the Database Backup/Restore dialog box, showing the backup history.


    Figure 15.5.
    The Backup History Information dialog box.

  4. After you select the proper backup history entry, click the Restore Now button. A Restore Progress dialog box appears (see Figure 15.6), showing the progress of the recovery process. To cancel the recovery process, click the Cancel button. Once the recovery has completed, a successful restore dialog box appears, informing you that the operation has successfully completed.

Figure 15.6.
The Restore Progress dialog box.


NOTE: A new feature of SQL Server 6.x speeds up the load and recovery process. This feature loads database or transaction dumps in 64K chunks. Also note that although a table can be loaded from a database backup, the Microsoft documentation discourages doing so because of possible inconsistencies that can result when using a database backup (the entire table is not locked during a database backup--as it is for a table backup).

The Transact SQL command to load a database backup is the LOAD DATABASE command, which has the following syntax:

LOAD DATABASE { dump_device_name | @dump_device_namevar}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH [[,] {UNLOAD | NOUNLOAD}]
[[,] {SKIP | NOSKIP}]
[[,] {FILE = fileno}]
[[,] STATS [ = percentage]]]

For LOAD DATABASE, LOAD TRANSACTION, and LOAD TABLE, dump_device can have the following format:

dump_device =
{dump_device_name | @dump_device_namevar} |{DISK | TAPE | FLOPPY | PIPE} =
{`temp_dump_device' | @temp_dump_device_var}}
[VOLUME = {volid | @volid_var}]


TIP: You can load dumps from temporary dump devices.

The Transact SQL command to load a transaction log is the LOAD TRANSACTION command, which has the following syntax:

LOAD TRANSACTION { dump_device_name | @dump_device_namevar}
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH [[,] {UNLOAD | NOUNLOAD}]
[[,] {SKIP | NOSKIP}]
[[,] {FILE = fileno}]
[[, ] {STOPAT = date_time | @date_time_var}]

The Transact SQL command to load a table is the LOAD TABLE command, which has the following format:

LOAD Table [[database.]owner.]Table Name
FROM dump_device [, dump_device2 [..., dump_device32]]
[WITH [[,] {UNLOAD | NOUNLOAD}]
[[,] {SKIP | NOSKIP}]
[[,] {FILE = fileno}]
[[, ] {SOURCE = source_name}]
[[, ] {APPEND}]
[[,] STATS [ = percentage]]]


Restrictions and Rules for Loading a Table
There are several rules and restrictions that apply when loading a table from a backup. The rules and restrictions for loading an individual table are as follows: A table load cannot occur on a table that has indexes or columns with the image or text datatypes or that has been published for replication. You can load an individual table from a database backup. Declarative referential integrity (DRI), rules, triggers, and defaults are not enforced during a table load. You cannot load a table into a system table, only into user-defined tables. The database select into/bulkcopy option must be set. The source and target tables can have different names. Once you load a table, you must perform a full database backup before you can use the DUMP TRANSACTION statement.

Loading a Database or Table Backup or Applying a Transaction Log Backup Using Backup Devices

You may encounter a situation in which you do not have any backup history information for a database, table, or transaction log but you want to recover the database, table, or transaction log using backup devices (for example, if you lost the msdb or the master database). When you select a backup device or file, the header information is read so that you can validate what type of backup and what databases are on the device or file. Another example of a database with no backup history information is a database created for the sole purpose of loading a database backup from another server. In this case, the newly created database has no backup history from which you can restore it. To restore a database, table, or transaction log using backup devices, follow these steps:

  1. From the Restore page in the Database Backup/Restore dialog box (refer back to Figure 15.3), click the From Device button; the Restore From Device on Server dialog box appears (see Figure 15.7).

  2. From the Destination Database drop-down combo box, select the database to restore to.

  3. Select the backup device(s) or files from which you are going to restore the database, transaction log, or table. Press and hold the Ctrl key and select multiple backup devices. If the database or transaction log backup was performed using a parallel striped set, you must select all the devices in the backup stripe set before you can restore. Select the backup from which you want to load in the Backup Information window.

  4. Once you have selected the destination database and backup device(s) and selected the correct backup to load from in the Backup Information window, click the Restore Now button to load the selected backup from the selected device(s).

Figure 15.7.
The Restore From Device on Server dialog box.

Point-in-Time Recovery

SQL Server 6.5 allows you to perform point-in-time recovery when applying (restoring) a transaction log to a database. Point-in-time recovery allows you to specify a point in time from which the transactions in the transaction log are to be rolled forward. Suppose that a transaction log spans from 11:30 a.m. to 2:30 p.m. In previous versions of SQL Server, you had to load the entire transaction log (that is, all or nothing). All the transactions that occurred from 11:30 a.m. to 2:30 p.m. are applied to the database. In SQL Server 6.5, you do not have to load the entire transaction log. For example, you can choose to load only all the committed transactions from 11:30 a.m. to 12:30 p.m. In a normal restoration process, you will always want to load the entire transaction log; however, in some instances when the database has become corrupted (for example a user error such as an invalid UPDATE or DELETE), you can use point-in-time recovery to bring the database back to the point before the data corruption occurred. To perform point-in-time-recovery, follow these steps:

  1. Start at the Restore page in the Database Backup/Restore dialog box (see Figure 15.3). From the Database drop-down combo box, select the database to which you want to restore the transaction log. The backup history for the selected database is displayed in the Backup History frame.

  2. Select the Database and/or Transaction Logs radio button.

  3. In the Backup History frame, select the proper checkbox for the history entry of the transaction log backup from which you want to restore.

  4. Once you have selected the proper transaction log backup history entry, select the Until Time checkbox. Adjust the date and time. Transactions committed after the date and time setting will be rolled back (see Figure 15.8).

    Figure 15.8.
    The Restore page in the Database Backup/Restore dialog box, showing point-in- time recovery.

  5. Click the Restore Now button. A Restore Progress dialog box appears, showing the progress of the recovery process. Once the recovery has completed, a successful restore dialog box appears, informing you that the operation has successfully completed.

Preparing for a Database Load (Restore)

Whether you are trying to recover a corrupted database or moving a database to another server, certain conditions must be met to load the backup. First, any existing data in the database is overwritten by the loaded backup. A backup cannot be loaded while the database is in use; the database to which you are loading the backup must be at least as large or larger than the database that created the backup. The SQL Server to which you are loading the backup must have the same sort order and character set as the SQL Server that backed up the database.


TIP: If you are creating a database to load a database backup, use the SQL Server 6.x FOR LOAD option on the CREATE DATABASE Transact SQL command. The FOR LOAD option saves time when creating the database by not initializing the data pages.

One of the most overlooked aspects of preparing a database for recovery is that the database must not only be the same size or larger than the dump database, but it must have the exact same device allocation order and size.


The Restore Database Myth
Requiring the exact device size and allocation is one of the deep dark secrets of SQL Server database recovery. I have run into many DBAs who were trying to restore databases and were having problems because the device allocation was not the same. Many assumed that the database only had to be the same size or larger!

What does having the same device allocation and size mean? Consider a few examples. In the first example, you create a database called test1 that is created with 40M for data on data_dev1 and 10M for the transaction log on log_dev1. To load a database backup of the database test1 to another database, you must create a database at least 50M in size (40M of data followed by at least 10M for the transaction log). Now suppose that you performed an alter database command and added 5M for data on data_dev2 and 2M for the log on log_dev2 and 10M of data on data_dev1. Now the test1 database contains many different device fragments (that is, the space allocated to the database by the create and alter statements) as shown in Table 15.1.
Table 15.1. Database test1 device fragment map.
Device Type Size in Megabytes
Data 40
Log 10
Data 5
Log 2
Data 10
Total Database Size 67


To create a database to load the test1 database dump, you need a database of at least 67M in size with a 40M data fragment followed by a 10M log fragment, followed by 5M data fragment, followed by a 2M log fragment, followed by at least a 10M data fragment--get the picture? If all of this sounds complex, relax. Microsoft has added two system stored procedures in SQL Server 6.5 (sp_coalesce_fragments and sp_help_revdatabase) that simplify the process of determining the proper device allocation required to create a database to load a database backup. Before discussing the two new stored procedures, let's review the manual process of determining database device allocation.


NOTE: To load or recover a database from a backup, the actual device on which the database is located is not important. However, the size and type of device fragmentation allocation is. The actual device(s) on which a database resides is important if the device fails and must be re-created.

To manually determine the device allocation for a database, perform the following device fragment query in the master database. (Note that the following query is for the database test1. Substitute your database name for test1):

select segmap `fragment type', size `fragment size'
from sysusages
where dbid = (select dbid
from sysdatabases
where name = "test1")

Running the query on SQL Server displays the following output:

Fragment Type Fragment Size
3 1024
4 512

The Fragment Type column translates as follows:

The fragment size is displayed in 2K blocks (512 = 1M). If you don't want to compute the size of the device segment, execute the system stored procedure sp_helpdb, which has the following syntax:

sp_helpdb [database name]

Following is the output of the sp_helpdb command on the test1 database:

name db_size owner dbid created status
------------------------ ------------ ------------------------ ------ ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test1 3.00 MB sa 6 Aug 6 1995 no options set
device_fragments size usage
------------------------------ ------------ --------------------
test1 2.00 MB data only
test1_log 1.00 MB log only

SQL Server 6.5 has added two system stored procedures to aid you in determining how to correctly create a database with the proper device allocation. The two stored procedures are sp_coalesce_fragments and sp_help_revdatabase. The stored procedure sp_coalesce_fragments deletes and updates rows in the system table sysusages. The purpose of sp_coalesce_fragments is to reduce the number of rows in sysusages for database fragments on the same device and physically contiguous by combining them into a single row in the sysusages table. The syntax for sp_coalesce_fragments is as follows:

sp_coalesce_fragments [DataBase Name Pattern]

Run the sp_coalesce_fragments stored procedure before executing the stored procedure sp_help_revdatabase. sp_help_revdatabase analyzes a database and generates a script to re-create the database with the proper device allocation. The format for sp_help_revdatabase is as follows:

sp_help_revdatabase [DataBase Name Pattern]

The optional parameter Database Name Pattern specifies a database name pattern and follows the rules used for a LIKE statement. sp_help_revdatabase may not be able to generate the correct script to re-create the database in cases where device fragments are not evenly divisible by 512. Check the script for cautionary comments that indicate that the ALTER database size has been rounded up. You can avoid this problem by creating devices in multiples of 512 2K pages. A sample output from the stored procedure sp_help_revdatabase is shown in Listing 15.2.

Listing 15.2. A sample script generated by the stored procedure sp_help_revdatabase.

/********1*********2*********3*********4*********5*********6**
Reverse generated at 1996/04/29 08:11:39:130
Server / Database / Default sortorder ID :
KSCSNT / Sales / 52

DBName FromLPage ToLPage segmap
------------------------------ ---------- ---------- ----------
Sales 0 1023 3
Sales 1024 2047 4
Sales 2048 3071 3

@@version: Microsoft SQL Server 6.50 - 6.50.201 (Intel X86)
*********1*********2*********3*********4*********5*********6**/
go
USE master
go
---------------- Space and Log allocations --------------
CREATE Database Sales
on Sales_Datat = 2 -- 1024 of two Kb pages
go
ALTER Database Sales
on sales_log = 2 -- 1024 of two Kb pages
go
EXECute sp_logdevice Sales ,sales_log
go
ALTER Database Sales
on sales_data2 = 2 -- 1024 of two Kb pages
go
-------------------- DB Options ------------------
EXECute sp_dboption Sales ,'ANSI null default'
, false
EXECute sp_dboption Sales ,'dbo use only'
, false
EXECute sp_dboption Sales ,'no chkpt on recovery'
, false
/***
EXECute sp_dboption Sales ,'offline'
, false
***/
/***
EXECute sp_dboption Sales ,'published'
, false
***/
EXECute sp_dboption Sales ,'read only'
, false
EXECute sp_dboption Sales ,'select into/bulkcopy'
, false
EXECute sp_dboption Sales ,'single user'
, false
/***
EXECute sp_dboption Sales ,'subscribed'
, false
***/
EXECute sp_dboption Sales ,'trunc. log on chkpt.'
, false
go
-------------------- sa is dbo --------------------
go
--


TIP: Keep a script generated by sp_help_revdatabase for each database. Check the script to make sure that no cautionary comments have been added because of fragments that are not multiples of 512. In the event of a recovery or a simple database move, you will be well prepared because you will have the Transact SQL statements required to properly create the database. Update your database scripts whenever you alter the size of a database or transaction log. It is also a good idea to keep a device fragment and device allocation map for each database generated by the device fragment query shown earlier in this section as a fallback.

Recovering a Database

Now that you understand how to use the tools and commands required to load a database, review the following steps to recover a damaged database:

  1. If the transaction log for the corrupt database is on a separate device, use the DUMP TRANSACTION command with the NO_TRUNCATE clause to dump the transaction log to a dump device so that the transactions can be recovered (that is, load it as the last transaction log).

  2. Perform the device fragment query described earlier in this section to determine the correct fragment map required to re-create the database.

  3. Drop the damaged database using the Enterprise Manager or the DROP DATABASE command. If the DROP DATABASE command fails, use DBCC DBREPAIR (database name, DROPDB) or the system stored procedure, sp_dbremove.

  4. Create the database with the correct device fragments using the CREATE DATABASE command or the SQL Server Enterprise Manager and, if necessary, the ALTER DATABASE command.

  5. Reload the database using the Enterprise Manager or the LOAD DATABASE command.


TIP: You can load a database dump from SQL Server 4.2x or SQL Server 6.0 to SQL Server 6.5, but you cannot load a SQL Server 6.5 formatted database to a SQL Server 4.2x database. You can load a SQL Server 6.5 database into a SQL Server 6.0 that is using SQL Server 6.0 service pack 3.0. You can create a backup from SQL Server 6.5 in SQL Server 6.0 format that can be read by SQL Server 6.0 without SQL Server 6.0 service pack 3.0. For more information on producing a backup in SQL Server 6.0 format from SQL Server 6.5, refer to Chapter 14, "Backups."

Loading Transaction Logs

After you load the most recent database backup, you can begin to load (apply) the transaction log dumps to the database. When you load a transaction log dump, database modifications made in the transaction log are reexecuted in the database. To maintain database integrity, transaction logs must be loaded in correct order. SQL Server checks the timestamp value of each transaction log and ensures that the transaction sequence is correct. If you try to load a transaction log out of sequence, you get an error and the transaction load halts. To load transaction logs, use the Enterprise Manager or the LOAD TRANSACTION command.

Moving a Database

If you plan to move a database to another server or another database using a database dump, follow the guidelines outlined in "Preparing for a Database Load," earlier in this chapter. Create the database using the correct segment map and load the database using the steps outlined in "Loading a Database or Table Backup or Applying a Transaction Log Backup Using Backup Devices," earlier in this chapter.

The SQL Server Enterprise Manager provides another tool to move a database: the Transfer Manager. Chapter 16, "Importing and Exporting Data," explains how to use the Transfer Manager to move a database when the character set or sort order of the two database servers is different. You can also use the Transfer Manager to transfer databases between servers on different platforms such as from an Intel platform to an Alpha platform.


CAUTION: If you use the DUMP command and then the LOAD command to move a database from one server to another, remember that the database user IDs travel with the database and the mappings, between the server login IDs, and that database IDs may not be correct on the new server. Use aliases or the system stored procedure sp_change_users_login to correct the problem.

Recovering the master Database

You have probably guessed by now that the master database is definitely not just another database. To recover the master database, you need a separate recovery procedure. You will know when the master database is corrupted because you will see output from a DBCC command, you will find an error message in the error log or the NT event log, or SQL Server will not start.


NOTE: The procedure to restore the master database is much improved in SQL Server 6.x than it was in previous versions. You no longer have to use the utility buildmaster or run the installmaster and installmodel scripts. As a matter of fact, these utilities are no longer shipped with SQL Server.

To rebuild the master database, use the SQL setup program. Start the SQL setup program and follow these steps:

  1. Select the Rebuild Master Database checkbox in the SQL Server Setup dialog box and click the Continue button (see Figure 15.9). The Rebuild Master Database dialog box appears (see Figure 15.10).


    Figure 15.9.
    The SQL Server Setup program dialog box.


    Figure 15.10.
    The Rebuild Master Database dialog box.


    NOTE: The Rebuild Master Database dialog box warns that you will lose all previously created databases. You recover the previous databases by loading a backup of the master database later in the rebuild process.

  2. Click the Resume button. The SQL Server 6.5 Rebuild Options dialog box appears (see Figure 15.11).


    Figure 15.11.
    The SQL Server 6.5 Rebuild Options dialog box.

  3. To recover the master database and all your other databases, you must select the same sort order and character set that were used by the corrupted master database. Once you have selected the correct character set and sort order, click the Continue button. The SQL Server Installation Path dialog box appears (see Figure 15.12).

    Figure 15.12.
    The SQL Server Installation Path dialog box.

  4. Select the correct path of your current SQL Server installation and click the Continue button. The Rebuild Master Device dialog box appears (see Fig-ure 15.13).


    Figure 15.13.
    The Rebuild Master Device dialog box.

  5. Enter the path and the size of your current master device and click the Continue button. The master device and database are then re-created. When the process finishes, a completion dialog box appears. Click the Exit to Windows NT button.

  6. Start SQL Server and log on to the server using the Enterprise Manager. The master database is in the same state as when SQL Server was first installed. You now must use a previous database dump of the master database to restore it. Add to the master database the dump device from which you plan to restore.

  7. Shut down SQL Server. To load the master database, either with the Enterprise Manager or from an ISQL command prompt, you must start SQL Server in single-user mode. To start SQL Server in single-user mode, use the SQL setup program and the Set Server Options selection to add the single-user flag /m or enter the following on the command line:


    startserver /c /dpath and filename of the master device /m

  8. Once SQL Server is started in single-user mode, use the Enterprise Manager or the LOAD DATABASE command to load a backup of the master database. When using the Enterprise Manager, use the Restore from Backup Devices File option to load the previous backup files. When you use the Restore from File option, the header information for each file is read so that you can determine the database and the date and time at which the backup was created. When the load operation completes, the completion message box appears (see Figure 15.14).


    Figure 15.14.
    The completion message box.

  9. Restart SQL Server in multi-user mode. The master database has now been successfully rebuilt.

If changes such as new logins or database modifications have been made since the master database backup, you must manually apply the changes or use a script file. Some changes, such as new devices added after the loaded backup can be recovered using the DISK REINIT command.

To recover database CREATE or ALTER database statements, use the DISK REFIT command. Remember, if you loaded from a current master database dump, recovery is complete. Always maintain a current backup of the master database.


TIP: If you are a new DBA or a DBA in training, find a test system on which you can practice recovering the master database. Think about how crucial the master database is to the proper operation of your SQL Server. The first time you rebuild the master database should not be on a production database while you are under a lot of stress and pressure to get the database back up and running. Practice makes perfect! I contracted once for a Fortune 100 company that was using a UNIX Sybase system (before the release of Windows NT). The DBA was new and inexperienced and the UNIX system had many system and drive problems during the first month. The DBA had to rebuild the master database twice. (Unfortunately, I was not allowed to help because I was contracted to support another group!) The first time resulted in failure, frustration, and then just an "Oh heck, it's not production. Let's reinstall." The second time, the DBA had learned from the previous experience and was able to rebuild the master database. Fortunately, Microsoft has now made rebuilding the master database a simple and graphical task!

Between the Lines

Here are some of the important points to remember about SQL Server recovery:

Summary

Database backups and recovery are serious business and an important part of a DBA's job responsibility. Set up a database backup plan; from time to time, practice your recovery procedures so that, in an emergency, you are prepared to properly recover the database.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.