Re-creating and Reloading Lost Databases

You restore a database by re-creating the database and then reloading the most recent database dump and the succeeding transaction log dumps.

To re-create and reload an affected database

If the transaction log of the damaged or inaccessible user database is on an undamaged device, use DUMP TRANSACTION with the NO_TRUNCATE clause to dump it.
Examine the device allocations and uses for the damaged database by reviewing the information you gathered in Finding Information About Lost Devices and Databases, earlier in this chapter.
You will have to assign the same blocks of space, for the same purposes. Examine the query output. Each row with 3 in the segmap column represents a data allocation; each row with 4 in the column represents a log allocation. The size column indicates the number of 2K blocks of data. Note the order, use, and size of this information. For example:
```
segmap       size
-----------  ----------
          3       10240
          3        5120
          4        5120
          3        1024
          4        2048
```
This translates into these sizes and uses, in megabytes:
```
Data -- 20
Data -- 10
Log  -- 10
Data -- 2
Log  -- 4
```
Note A 7 in the segmap column indicates that the data and log are on the same device, and you can recover only up to the point of the most recent database backup. If the transaction log and database are on the same device, you can perform a DUMP DATABASE, not a DUMP TRANSACTION statement. (You should perform step 4 using the LOG ON option with CREATE DATABASE. Instead, be sure that you allocate sufficient space.)
Use the DROP DATABASE statement to drop the database from the failed device. If the system reports errors, use the DROPDB clause of the DBCC DBREPAIR statement, or use the sp_dbremove system procedure.
Re-create the database, using the CREATE DATABASE statement to duplicate all the rows from the old sysusages table up to and including the first log device. For example:
```
CREATE DATABASE mydb on datadev1 = 20, datadev2 = 10
    log on logdev1 = 10
```
If you need more space, use the ALTER DATABASE command to re-create the rest of the entries. For example, to allocate more space on DATADEV1:
```
alter database on datadev1 = 2 
```
To allocate space on another device:
```
alter database on datadev3 = 2
```
When you allocate space on existing devices, the device is automatically assigned the same usage: data or log. When you allocate space on a device that's not already in use by the database, it's always allocated as a data device. For example, to re-create the final allocation on LOGDEV1, which is already in use by the database, type:
```
alter database mydb on logdev1 = 4
```
LOGDEV1 is automatically entered into sysusages as a log device.
To allocate log space on a device that is not already in use by the database, follow the ALTER DATABASE command with the sp_logdevice procedure, like this:
```
alter database mydb on logdev2 = 4
sp_logdevice mydb, logdev2
```
Reload the database using LOAD DATABASE, and then load previously dumped logs and the newly dumped current log using LOAD TRANSACTION.

For information about using ALTER DATABASE, CREATE DATABASE, DBCC DBREPAIR, DISK INIT, DROP DATABASE, LOAD DATABASE, LOAD TRANSACTION, sp_dbremove, and sp_dropdevice, see the Microsoft SQL Server Transact-SQL Reference.