Recovering From A Suspect Database

Recovering From A Suspect Database


Tormented problem that occurred due to database showing as suspect could be recovered with a hope of luck. Various articles on the WWW depict the similar T-SQL queries to be run on the suspected database. Simply the suspected database could be restored with the latest backup available and could solve the issue in minutes. Worst case scenario you will not have a back up or the latest back up is taken after the database mode has already gone to suspect mode.


We really came across the worst case scenario to one of our most critical database. We purchased a software (recovery tool for sql server) which created scripts which could be executed to populate the tables and data from the suspected data backup. Believe me running them is a task.


Why this suspect mode in a database does occur?


Actually to our scenario we really could not find the real answer why. But we suspect to be the low disk space in the server.


During a WWW search I found these...


A database can be marked suspect for one of the following reasons (this is from SQL Server Books Online):


  • If one or more database files are not available.



  • If the entire database is not available.



  • If one or more database files are corrupted.
  • There is not enough space available for the SQL Server to recover the database during startup.



  • Database cannot be opened due to inaccessible files or insufficient memory or disk space.



  • Database files are being held by operating system, third party backup software etc.



  • Unexpected SQL Server Shutdown, Power failure or a Hardware failure.




How you could recover?


Run this query


USE Master

GO


-- Determine the original database status



SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases


GO


-- Enable system changes


sp_configure 'allow updates',1


GO

RECONFIGURE WITH OVERRIDE


GO


-- Update the database status


UPDATE master.dbo.sysdatabases


SET Status = 24


WHERE [Name] = 'YourDatabaseName'


GO


-- Disable system changes


sp_configure 'allow updates',0


GO


RECONFIGURE WITH OVERRIDE


GO


-- Determine the final database status


SELECT [Name], DBID, Status


FROM master.dbo.sysdatabases


GO


We ran this on on our suspect mode database. But there occcred a situation where it did not display any tables after some time. But mode of the database has been chaged from suspect mode. We did this several times. With some luck this would correct the problem. But to our scenario the recovered database could not be backed up. So what we did was made a DTS transfer to another database. And made a scripts of the sp’s, views, indexes and constraints and built up simultaneous database to bring back the database with the most current data.










0 comments:

Post a Comment

 
Copyright © My Beat