Saturday, May 16, 2009

Recover Corrupt SQL Server Database


This is quiet common while working on VPC (MS CRM VPC) that when the VPC gets close abnormally.

When you restart the VPC and try to open MS CRM Web you will see some SQL SERVER (Database Corrupt) related error. When you open SQL Server Enterprises studio and we will observe that the database is in SUSPEND mode. And this is due to the VPC gets close abnormally.

But still you can recover the DATABASE. Use the below set of SQL Statement .

EXEC sp_resetstatus 'MicrosoftCRM_MSCRM';
ALTER DATABASE MicrosoftCRM_MSCRM SET EMERGENCY

DBCC checkdb('MicrosoftCRM_MSCRM')
ALTER DATABASE MicrosoftCRM_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('MicrosoftCRM_MSCRM', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MicrosoftCRM_MSCRM SET MULTI_USER

MicrosoftCRM_MSCRM is database name (for example purpose). When you use this script to recover the database replace MicrosoftCRM_MSCRM with your database name.

Enjoy. Happy recovering corrupt SQL Server Database.

No comments: