Captools/net Documentation

SQL Database Repair

SQL Database Repair

Previous topic Next topic  

SQL Database Repair

Previous topic Next topic  

If you have had a hard drive failure and have no recent backups on media separate from that hard drive ("don't do this again!"), it may be necessary to repair a copy of the database recovered off the failed drive.  This can be done using Microsoft's SQL Management Studio.  Here are some commands that can be executed in that product that may help.

DBCC CHECKDB - Run this to go through the database and list the errors. Here is a Microsoft link with some definitions. It doesn't give the best help for syntax but at least it describes certain terms: http://msdn.microsoft.com/en-us/library/ms176064.aspx

ALTER DATABASE (CaptoolsDB  SET SINGLE_USER WITH ROLLBACK IMMEDIATE) - This query sets the database into single user mode which is required to run any of the "repair" commands.  Our understanding is the rollback immediate part disconnects any other current connections to the database. This may not be completely accurate but it has to do with making sure no other user or program is attached and that you are the only one with access.

DBCC CHECKDB (CaptoolsDB, REPAIR_REBUILD) - This is a way to try and repair certain errors that may come up but it will not make changes if there is a possibility for data loss, or at least that is  our understanding.  Try this before doing trying the last query below. If this solves everything then you can move on to the last function to restore the multi-user mode.

DBCC CHECKDB(CaptoolsDB, REPAIR_ALLOW_DATA_LOSS) - This will attempt to repair all reported errors but can cause data loss and that is why it should be a last resort. There could be Captools backups or or SQL backups that can be restored within management studio if they are available. After this command the user would again need to set the database back to multi user mode.

ALTER DATABASE (CaptoolsDB, SET MULTI_USER) - This is necessary to revert the database back to multi user mode.