SQL Server Database - Recover From Suspect mode

Sometimes you find your SQL database in suspect mode. This normally happens when your PC abnormally shutdowns or any other major hardware failure. Here i will explain how to recover your precious data from this.

In many cases there will be no change in the size of MDF and LDF files, but more likely LDF is corrupted.

So here is the solution of your problem.

• First make sure that database is in suspect mode
  • Create a new empty database at some safe location
  • Now stop the SQL Server service
  • Replace the old MDF file over newly created
  • Start SQL service and database will try to recover it. (During this database will go to suspect mode)
• Now we will put the database to emergency mode (This can be done by using ALTER DATABASE or following commands)

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ''
GO
sp_dboption '', 'single_user', 'true'
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Try to use DBCC CHECKDB ('', REPAIR_ALLOW_DATA_LOSS) This will rebuild log file and run full repair of the MDF file)

DBCC CHECKDB('', REPAIR_ALLOW_DATA_LOSS)
Go

(*If it gives error use next step)

• Then use DBCC REBUILD_LOG ('','new LDF path') to rebuild the LDF file

DBCC REBUILD_LOG('', 'new LDF path')
Go

• Almost done. Now we will push database to normal mode (Again use either ALTER DATABASE or following commands)
  

Sp_configure 'allow updates', 1
GO
Reconfigure with override
GO
Update sysdatabases set status = 16 where name = ''
GO
Sp_configure 'allow updates', 0
GO
Reconfigure with override
GO

• Restart the engine of Microsoft SQL Server (I have successfully checked on Microsoft SQL 2000)
• Verify your database by checking its tables and data.

* Don't forget to make a daily backup schedule next time :)