How do I repair a "suspect" SQL 2005 database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

How do I repair a "suspect" SQL 2005 database

Author

Topic

HoodGordon
Starting Member

1 Post

Posted - 2015-04-25 : 03:35:37

I have a SQl 2005 Server that had a fan problem and went into continuous reboot for a time. I have replaced the fan, but now when I bring it up and start SQL services I have a few databases marked as suspect. I am not a DB person, and our DB person is out of office for a few weeks. How do I repair these DB's so I can get this running again.

Thanks.

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 07:32:09

First of all I would check if they are actually damaged. They might have been set to SUSPECT without actually being corrupted (here's hoping

)


USE MyDatabaseName
GO
DBCC CHECKDB WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY

Repeat for each database you need to check (probably worth doing ALL of them if you had a hardware problem, even if not marked SUSPECT they might be corrupted.

If you get no messages at all from DBCC CHECKDB then the database integrity is fine (that is NOT to say that some transactions being performed when the server rebooted have not partially-completed and corrupted the LOGICAL integrity of your application data. If ALL [without exception!!] you database logic is protected within BEGIN TRANSACTION ... COMMIT blocks then there should be no problem, either an entire transaction will be present, and any partially-completed transactions will have been rolled-back)

Next problem is that you have to change the database from SUSPECT to normal, so that you can run DBCC CHECKDB on it

You can get the database out of Suspect mode and you should then set it to Emergency Mode - this is READ ONLY so will prevent anything connecting to the database and trying to update it etc.!

Make sure you are connected to the master database:


USE master
GO

then change the state of the database:



-- Older versions of SQL used:    EXEC sp_resetstatus 'MyDatabaseName'

ALTER DATABASE MyDatabaseName SET EMERGENCY

If you get any errors from DBCC CHECKDB you need to FULLY understand the ramifications of fixing them. My advice is NOT to attempt anything without first seeking advice (here, from Microsoft, etc.). Once you go down that route there is no going back.

Above all do NOT detach the database under ANY circumstances. There is a real risk that you will not be able to reattach it, and then all bets will be off ...

If the database is corrupted your BEST bet is to recover from a recent backup. If you have a backup that you know is good (i.e. taken shortly before the hardware problem arose) I would recover that anyway. It will ensure that your database is logically consistent (from your application's point-of-view).

SO if you will recover from backup just go ahead and do that. I'm not sure if you can restore over a SUSPECT database directly, if not change the database to OFFLINE


ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE

and then you will be able to restore over the top of it.

After restore it would be worth doing the DBCC CHECKDB to check that all is well before allowing users to connect.

If you have backups but there is "new" data in the database (and the database uses FULL recovery model) then you MAY be able to take "tail backup" of the log. If that is the case then you MAY be able to restore last good FULL backup, ALL TLog backups after that and finally your TAIL LOG BACKUP. If you then do DBCC CHECKDB and find the database is clean then you have a zero-loss restore - and THAT, for my money

, is the reason to use FULL Recovery Model and Log Backups

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-26 : 01:26:19

I would add one extra step i.e before taking any actions , check all relevant error log sources. This may give you some extra clues

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2015-04-26 : 05:17:56

Good point

FranklinGilm
Starting Member

1 Post

Posted - 2015-04-26 : 08:09:37

quote:
Originally posted by HoodGordon

I have a SQl 2005 Server that had a fan problem and went into continuous reboot for a time. I have replaced the fan, but now when I bring it up and start SQL services I have a few databases marked as suspect. I am not a DB person, and our DB person is out of office for a few weeks. How do I repair these DB's so I can get this running again.

Thanks.

I would like to suggest you to use of SQL Server Repair Toolbox is efficient remedy against the corruption of MS SQL databases and it knows very well how to repair .MDF file in SQL. For more:unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources