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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 How do I repair a "suspect" SQL 2005 database
 Reply to Topic
 Printer Friendly
Author  Topic Next Topic  

HoodGordon
Starting Member

1 Posts

Posted - 04/25/2015 :  03:35:37  Show Profile  Reply with Quote
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

United Kingdom
22859 Posts

Posted - 04/25/2015 :  07:32:09  Show Profile  Reply with Quote
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

Edited by - Kristen on 04/25/2015 07:33:20
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 04/26/2015 :  01:26:19  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 04/26/2015 :  05:17:56  Show Profile  Reply with Quote
Good point
Go to Top of Page

FranklinGilm
Starting Member

1 Posts

Posted - 04/26/2015 :  08:09:37  Show Profile  Reply with Quote
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
Go to Top of Page
   Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.13 seconds. Powered By: Snitz Forums 2000