SQL Server database can become marked suspect for several reasons, therefore regular database backup is mandatory. Possible causes include:
1. Denial of access to a database resource by the operating system
2. Anti-virus/3rd party software blocking access
3. The hardware/network issue or corruption of database file(s)
Prior to resetting the status, the database file issue should be resolved. First check error logs and Event Viewer for related messages. If logs offer no help, then one way of resolution: create an empty database, copy over the damaged database files and restart the server. The (copied over) database may come up in the suspect mode. The recovery methods below are last resort only. At minimum the server should be rebooted (Windows restart - hardboot), if possible at all, a database copy should be created. If backup files exist, point-in-time recovery is preferred to emergency repair.
The following script resets the status of the database and checks the database for integrity.-- Check suspect state (status) of all databases
SELECT DBName=NAME, [Status]=state_desc
FROM master.sys.databases
WHERE state_desc='SUSPECT'
------------
------------
-- SQL Server 2000/2005/2008 - Recover database suspect sql server
USE master;
GO
EXEC sp_resetstatus 'CopyOfAdventureWorks2008';
GO
USE CopyOfAdventureWorks2008;
DBCC CHECKDB WITH NO_INFOMSGS;
GO
great blog. you have made it yourself? good info for techies.
ReplyDeleteSuspect SQL database can be restored by following by following procedure also:
ReplyDeleteEXEC sp_resetstatus ‘yourDatabasename’;
ALTER DATABASE yourDatabasename SET EMERGENCY
DBCC checkdb(’yourDatabasename’)
ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDatabasename SET MULTI_USER
Note: Database may end up with some data loss.
If database is very crucial then you may try SQL recovery program offered by data recovery companies.