PRB: Using DISK REFIT Versus DBCC DROPDB on Damaged Databases

ID: Q46468

The information in this article applies to:

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',..)

Drop those databases with DBCC DBREPAIR( dbname, DROPDB).

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.

Additional query words: Windows NT 


Keywords          : kbother SSrvGen SSrvWinNT 
Version           : 4.2 | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 6, 1999