SQL Server Database Marked Suspect Recovery - SQL

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

Comments

  1. Revolution Online.MVT.11 September 2012 at 00:55

    great blog. you have made it yourself? good info for techies.

    ReplyDelete
  2. Raj7 May 2013 at 21:19

    Suspect SQL database can be restored by following by following procedure also:

    EXEC 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.

    ReplyDelete

Post a Comment

Popular posts from this blog

HOW TO SUPPLY STAMPS TO BO FROM SO in SAP (CSI) ?

Image
SUPPLY STAMPS TO BO FROM SO in SAP (CSI) The procedure for supplying stamps from SO to BO for CSI Post offices   The entire process is divided into two parts              1.   Removing material from SO Location       2.   Placing material in to BO Location ===== Removing material from SO Location ===== Tcode : MIGO Select option “Remove from Storage” TF rem.fm str.toSLoc = “313” Doc header text : Perticular about sent stamps Material : RVS100 (For sending Revenue stamps, select other stamps as per necessity ) Plant : G131 Storaloc : SO CODE Destination : BO Code for whom stamp is to be sent Enter quntit Then press check and POST You will get document no (Send stamps with this document no to BO) ====== Placing stamps in BOs location =========== (This is to be done on the day when BO received actual stamps) Tcode : MIGO Action : Place in Storage Enter document no : Document no generated as above Press enter All the Stamp information will be displayed Select the stamps actually received
Read more

Fresh installation of CSI Applications and Database

After CSI Roll out, we often need to reinstall CSI Applications or create fresh database when existing server goes out of order like HDD Crash issues. By following the below steps, a fresh database and applcaitons can be installed easily in a new / formatted PC Copy Roll_out_2 folder into C:\POS_Installer Now Click on C:\POS_installer\Roll_out_2\POS\Others\Roll out\start.bat Click on get Started Button Enter Facility  ID of Office Click on Save button Download Download :             Source :            C:\Roll_out_2\POS\Software             Destination :    C:\POS_Installer           Click on Download button Application Source :            C:\Roll_out_2\POS\Application                Destination :    C:\POS_Installer              Click on Download button CASSO Source :            C:\Roll_out_2                Destination :    C:\POS_Installer Setup Configure Systems : Machine ID (Select Database,Backoffice,counter one by one) Select OS type Click on Add Butotn Copy Software
Read more