Monday, December 10, 2007

Bring the database which is in suspect

Steps intend to do to get the database online:

1. We then execute

Use Master
sp_configure 'allow updates', 1
reconfigure with override
- So that it would allow us to make changes directly to the SYSTEM tables(this option is used in extreme circumstances).

3. The following command lets us put the database in an emergency mode

Update SYSDATABASES set status = 32768 where name = 'SampleDB'
- In Emergency Mode it would allow us to perform certain actions which would not be possible in either Normal or Suspect Modes.

4. Having put the database in Emergency Mode we can then create a new log for the database since the current one can corrupt or inaccessible.

DBCC REBUILD_LOG('SampleDB', ‘C:\Data\SampleDB_newlog.ldf’)
- After this we can check the current files of the database using sp_helpfile command after changing the database context in the Query Analyzer to the affected database

5. We then check for consistency issues on the database by executing:
- If the above command fails it usually recommends the next available option to use. You have to clearly understand the option before you try it since it could result in data-loss

6. Turn off the option to make direct changes to the SYSTEM tables using:
sp_configure 'allow updates', 0
reconfigure with override

7. Then proceeded to take a complete database backup of the database.

No comments: