Damaged database - how to restore the database

Damaged database could be damaged in many different ways, so it is hard to have one solution for this:

try to online the database

ONLINE db_name

if not working try to force online:

sp_forceonline_db db_name, sa_on

this procedure will try to bring the database online without checking for consistency errors or suspected pages. Those operations should allow the administrator to see the content of database, to perform the last backup of the database, may be do some export and so on

The next point is usually dropping the database and recreating it as an empty database and then  loading the dump. To drop database use:

DROP DATABASE db_name

If there are problems with this command following command could be started:

dbcc dbrepair (db_name, dropdb)

So, the next point is recreating the database. Command displaying information regarding backup can be very helpful as it shows which devices have been used by the database:

LOAD DATABASE db_name FROM '/path_to_backup.dmp' WITH  VERIFYONLY=crc

During database creation all the pages in the database are initialized. During database restore, all pages containing some data are copied from dump file and the empty pages are initialized again. As you see initialization will be for some pages done twice. To avoid this database can be created using the FOR LOAD key word:

CREATE DATABASE db_name
ON DataDev=10000
LOG ON LogDev=1000
FOR LOAD

Now it is time to restore the database!

Note additional points below:

1. database can have a long history ont he server. After it was initially created, it could be expanded by adding additional space. A simple CREATE DATABASE is physically not the same as CREATE DATABASE and a coupple of ALTER DATABASE adding storage. To hace the data be loaded in exactly the same way all CREATE and ALTER statements should be run one after another. Following query can help to determine which commands should be started:

SELECT
    x.segmap
    ,x.SizeMB
    ,cast(x.segmap & 1 as bit) as IsSystem2
    ,cast(x.segmap & 2 as bit) as IsDefault2
    ,cast(x.segmap & 4 as bit) as IsLog2
    ,cast(x.segmap & 8 as bit) as IsFirstUserDefinedSegment2
FROM
    (
    SELECT
       segmap
      ,size * (SELECT low FROM spt_values WHERE number=1 and type='E')/1024/1024 AS SizeMB
    FROM sysusages
    WHERE dbid = DB_ID('ALFA')
    ) AS x

--1 = system segment
--2 = default segment
--4 = log segment
--8 = first user defined segment

system & default determine a data expanding operations

logs mean log expanding operations

2. In the dump there is no information about database options. Options for databases are normally stored in sysdatabases table in master database. After database has been created all the options should be changed again using sp_dboption command as described in this article.