Friday, March 25, 2011

Repair a SQL Server 2008 Suspect database after upgrading

Lately, I've installed SQL Server 2008 Developer Edition in my pc and migrated all sql server 2005 databases.

Upon attaching/restoring all databases, some were marked as suspect. Below is the sql script to repair those databases.
1:  EXEC sp_resetstatus 'Test';  
2:    ALTER DATABASE Test SET EMERGENCY  
3:    DBCC checkdb('Test')  
4:    ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
5:    DBCC CheckDB ('Test', REPAIR_ALLOW_DATA_LOSS)  
6:    ALTER DATABASE Test SET MULTI_USER  

But as a precautionary measure,I have back-up plans for each database,as to have another copy when things go wrong.

Test is the database name.

Source: how-to-repair-sql-server-2008-suspect

2 comments:

  1. sql repair can repair mdf files from any version of SQL Server. The program doesn't modify source data during recovering. It can show restored mdf and starts under all available MS Windows.

    ReplyDelete
  2. Alex,

    Thanks for the input. I'll take a look into this tool.

    Cheers!

    ReplyDelete