Cry How to...
Repair a database
To determine whether a database needs to be repaired run:
dbcc checkdb('DB-NAME') with no_infomsgs
replacing 'DB-NAME' with the name of the database.
If this completes without displaying any errors then the database does not need to be repaired.
If the errors that come back contain lines saying:
... Run DBCC UPDATEUSAGE
Then the database does not need to be repaired, simply run:
dbcc updateusage('DB-NAME') with no_infomsgs
If a database does need to be repaired then:
To repair a database the database must first be placed into single user mode:
alter database DB-NAME set SINGLE_USER
if the database is the MASTER or MSDB then instead consult "How to set a single database into single user mode".
once the database is in single user mode it can be repaired. There are a number of repair options but the two typically used are "REPAIR_REBUILD" and "REPAIR_ALLOW_DATA_LOSS". I suggest in the first instance using:
this will make any repairs that SQL Server can perform without the loss of data.
once the database has been repaired it should be switched out of single user mode and back into multi-user mode:
set database DB-NAME set MULTI_USER
These notes have been tested against SQL Server 2005 running under Windows 2008 Standard Server.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.