Recover SQL Server Database Marked as Suspect

It is not uncommon that a SQL Server database got corrupted and cannot be accessed. This is normally not just an index corruption and cannot be fixed via rebuilding index or with following scripts:

DBCC CheckDB (‘DBName’, REPAIR_ALLOW_DATA_LOSS)

In most situation the best way is just to get the data out of the corrupted database into a new one. The best one to do this is to change the corrupted database to emergency state with following scripts:

EXEC sp_resetstatus ‘DBName’

GO

ALTER DATABASE DBName SET EMERGENCY

After this the database could be access just like a normal database, except you can only read database from the database. At this point the best way is just to read everything out and exported into a brand new database. Below is the scripts I used to generate the import and export statement. Just run the scripts in the MS SQL Management Studio and use the result scripts get the data out of the corrupted database to the new one:

use [NewDBName]
GO
declare @bIden bit
declare @IdenCol varchar(50)
declare @table varchar(50)
declare @col varchar(50)
declare @col2 varchar(50)
declare @select varchar(900)
declare @sql nvarchar(2000)

DECLARE cur_recover Cursor For

Select name from sysobjects where OBJECTPROPERTY(id, N’IsUserTable’) = 1
Open cur_recover
fetch next FROM cur_recover into @table
while @@fetch_status = 0
BEGIN
set @select=”
DECLARE cur_tb Cursor For
Select Ident_seed(St.name) Iden, ‘[‘+SC.Name +’],’ as ColName, case when ISNULL(SC.AUTOVAL, CASE WHEN SC.COLSTAT = 1 THEN 1 ELSE NULL END ) is null then ” else SC.Name end ColIden
from sysobjects ST
JOIN syscolumns SC ON SC.ID = ST.ID
JOIN systypes DT ON DT.xtype = SC.xtype and DT.xusertype = SC.xusertype
WHERE OBJECTPROPERTY(ST.id, N’IsUserTable’) = 1 and ST.Name=”+@table+”
open cur_tb
fetch next FROM cur_tb into @bIden, @col, @col2
while @@fetch_status = 0
BEGIN
set @select=@select+@col
if len(@col2)>0 Set @IdenCol = @col2
fetch next FROM cur_tb into @bIden, @col, @col2
END
close cur_tb
deallocate cur_tb

Set @select = left(@select,len(@select)-1)
If @bIden=1
Begin
print ‘Delete from [‘+@table+’]’
print ‘SET IDENTITY_INSERT “‘+@table+'” ON’
print ‘GO’
End
Set @sql = ‘Insert Into [‘+@table+’](‘+@select+’) Select ‘+@select+’ from [OldDbName].dbo.[‘+@table+’]’
print @sql
print ‘GO’
If @bIden=1
Begin
Print ‘SET IDENTITY_INSERT “‘+@table+'” OFF’
Print ‘GO’
set @sql = ‘declare @ID int Select @ID = Max([‘+@IdenCol+’] from [‘+@table+’] Set @ID = isnull(@ID,0)+1 DBCC CHECKIDENT ([‘+@table+’], RESEED, @ID)’
Print @sql
Print ‘GO’
End
fetch next FROM cur_recover into @table
END
close cur_recover
deallocate cur_recover

Advertisements

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