Today I broke my SharePoint development environment by shutting down the SQL Server forcibly and corrupting my transaction logs, making the databases show in Management Studio show as “Suspect”. Thankfully this was a development environment and not our live environment! After a lot of googling and perseverance, I managed to cobble together a solution to get my databases back online and continue to work.
The best way to recover from this issue is to restore from a Backup. Stupidly I didn’t have a good backup available for the databases in my Dev environment.
I DO NOT recommend this method on a live system!!!
To recover the database, which involves rebuilding the transaction log I executed the following query in Management Studio. Obviously change the database name to reflect your own database name.
ALTER DATABASE [DB_Name] SET EMERGENCY
ALTER DATABASE [DB_Name] SET SINGLE_USER;
DBCC CHECKDB ([DB_Name], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
ALTER DATABASE [DB_Name] SET ONLINE;
ALTER DATABASE [DB_Name] SET MULTI_USER;
Again I stress, DO NOT sue this method on a live environment.