PRB: Using DISK REFIT Versus DBCC DROPDB on Damaged Databases

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SYMPTOMS

In the "Microsoft SQL Server Administrator's Guide" under "Loading a Database," it states that a damaged database can be dropped with DBCC. However, later in the chapter, the instructions state that DISK REFIT can be used, and no mention is made of DBCC.

When DISK REFIT is used, the owner and version of all databases and SYSUSAGES are changed, and there are many more entries than before, some with incorrect segment map information.

CAUSE

When DISK REFIT is used, it attempts to make the recovery procedure as decision free as possible. As a result, the procedure is not always optimal for all situations.

WORKAROUND

DISK REFIT was originally intended to be used after the master database was lost. It attempts to extract information from the physical database device files and reconstructs SYSDATABASES and SYSUSAGES, which, after a rebuild of MASTER.DAT, contain only master, tempdb, and model. As a result, some information is lost, such as database owner-id and segmap data. DISK REFIT also assumes consecutive VDEVNOs starting with 1.

If the master database is intact, a better approach is to not use DISK REFIT, and instead use the following procedure:

Determine which databases had space allocated on the lost devices either by examining SYSDEVICES, SYSUSAGES, and SYSDATABASES, or by executing the following SQL command:

   SELECT DISTINCT DB_NAME(DBID) FROM SYSUSAGES,SYSDEVICES WHERE
     VSTART>=LOW AND VSTART<=HIGH AND NAME IN
         ('devname1','devname2',..)

Then, reload the databases from a backup.

This technique will preserve the information in the system tables for those databases not affected. The databases that are re-created will have VDEVNO, segmap, dbowner, version, and so forth, which are specified during the DISK INIT, CREATE DATABASE, sp_logdevice, and so forth, commands.