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.September 11, 2012 at 1:25 PM

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

    ReplyDelete
    Replies
    1. sql data recoverySeptember 16, 2012 at 11:15 AM

      You can also try one of the best SQL Database Recovery softwares here :

      http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

      This software will show you the preview of recovered data in trial version.

      Thanks

      Delete
  2. RajMay 8, 2013 at 9:49 AM

    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

Applet Not initialized properly’ Error in Finacle – Solution

-
Image
Some time , even after installing all java updates and configurations we face issue of  "Applet Not initialized properly’ Error in Finacle"           the best solution for the same is 1..  Use java 7 updt 17 (It always works fine) - if any other version is installed , kindly uninstall the same 2.  now  navigate to following path C:\Users\Administrator\AppData\LocalLow Delete the folder named SUN. Now clear cache and history of internet explorer and re launch internet explorer.  Give a tick in check box and click Later Give a tick and click RUN Here also give a tick and click on RUN Sometimes if you response was delayed it will show Error loading applet. But no problem. Just give user id and password and login. Now when you select FINCORE, another pop up will come as shown below Here also, give a tick in the check box and click RUN.          ...
Read more

Create an HTML document giving details of your name, age, telephone number, address, TLC code & enrolment number aligned in proper order.

-
Create an HTML document giving details of your name, age, telephone number, address, TLC code & enrolment number aligned in proper order. <html> <body> <ol> <li>Name : Ketan D Joshi/li&gt; <li>Address : At &amp; Po Gadh</li> <li>Mo NO- +91 0000000000</li> <li>Desi.: Student at IGNOU Nootan Bharati Madana</li> <li>En. No: 145566778</li> </li> </ol> </body> </html> Out put Hope you all will enjoy this Post. Any kind of positive suggestions regarding this post are heartly accepted. If any one has problem with this post content, kindly let me know through email , i will update or remove this post as soon as possible Thanks Regards Ketan Joshi SA Palanpur HO Mo no:+91 8866984506 eMail : k9724675876@gmail.com
Read more

Fresh installation of CSI Applications and Database

-
Image
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         ...
Read more