Recovering a “Suspect” Database in SQL Server 2008

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.

USE master
GO
ALTER DATABASE [DB_Name] SET EMERGENCY
GO
ALTER DATABASE [DB_Name] SET SINGLE_USER;
GO
DBCC CHECKDB ([DB_Name], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [DB_Name] SET ONLINE;
GO
ALTER DATABASE [DB_Name] SET MULTI_USER;
GO

Again I stress, DO NOT sue this method on a live environment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s