asked
sql server 2005 - Suspect database
HI TEAM, I use sql server 2005 standard edition 32 bit, my database went into SUSPECT MODE. please help me
Well, you could try resetting the database to non-suspect mode, but the chances are this won't achieve anything as SQL Server will (probably) put it back into suspect mode. EXEC sp_resetstatus 'DBname' Your only real strategy is to restore from a clean backup. You do have backups, right?
Appart from @ThomasRushton method, another way is as follows
-- Take the database into Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO
-- Change into single user mode
ALTER DATABASE [Database Name] SET SINGLE_USER
-- Then run DBCC CheckDB command. Warning! REPAIR_ALLOW_DATA_LOSS can cause some data to be lost!.
DBCC CheckDB (<[Database Name]> , REPAIR_ALLOW_DATA_LOSS)
-- Change from multiuser
ALTER DATABASE dbName SET MULTI_USER
+1 You can use the DBCC CheckDB with REPAIR_REBUILD to see if the problem can be fixed without data loss. If the corruption is in a non clustered index you may drop it and recreate it. If it is in a clustered index you need to restore a backup.
I would strongly caution against using REPAIR_ALLOW_DATA_LOSS as a first step on a suspect database. There are a number of things that should be attempted prior to that and even then, doing the repair that way should be enetered into pretty carefully, with full assurance of backups and all that stuff. Sending them over to Paul Randal's blog is a good start to the possibility of a safe recovery.
If there isnt too big a gap since your last backup then I would go for the restore option as first call. **Dont** restore over the currently suspect database, restore it as a different name and check its all OK. If that restores in good order then you know that you can start to explore with DBCC CHECKDB safe in the knowledge that you have a good backup at a known point in time. You may get some data from the suspect database and be able to get it online successfully, however, you may also fry it completely and need the restored version. If you have to use the restored database it can easily be renamed or restored again but this time overwriting the broken copy.
Restoring from the Backup DB might be easy but it will be difficult to restore if you haven't got a recent backup. For example ..you are backing up DB for every 4 hours however if your db is become SUSPECT after 3 hours the backup run and users already made changes in your DB. However is that easy to detach the DB and reattach again to re-slove the issue rather then using Repair_allow_data_loss?
A DB in suspect state cannot be (directly) detached. It needs to be put into emergency mode to do this, but it is not something I would suggest you do. It is much better to try what everyone else has said. As for the data loss after 3 hours for a 4 hour backup window - this is something that has been given the nod by management. They have said they can live with up to 4 hours loss by mandating a 4 hour backup window. Maybe that would be reviewed after the first data loss though!
If dealing with production, I recommend Fatherjack's approach. It is generally the safest and closest to gaurunteed success without dataloss you are going to get. I have in test and development environments had some success with simply detaching and reattaching the database file, depending of course on what made it go into suspect mode. Again, I would absolutely not recommend this for production, but if it is a test environment or similar where you care more about not using a lot of DBA time then you do about gaurunteed success this is fast.
