Tuesday, May 26, 2015

Repair a suspect Database in SQL

Problem:
Cannot access the SQL database
Cause:
The SQL database is tagged as (suspect) and become corrupted due an unexpected SQL Server Shutdown or power failure.
Resolution:
1.       Open Microsoft SQL Server Management Studio and connect to your database
2.       Click the New Query button
3.       Paste the following SQL script into your New Query.
EXEC sp_resetstatus [Maxreports];
ALTER DATABASE [Maxreports] SET EMERGENCY
DBCC checkdb([Maxreports])
ALTER DATABASE [Maxreports] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([Maxreports], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [Maxreports] SET MULTI_USER
4.       Click Execute

Now you should able to access the database :)