Are You Embarrassed By Your How To Recover Suspected Database Sql 2000/2005/2008 Skills? Here's What To Do

How to Recover Suspected Database SQL 2000/2005/2008

        “Database suspected” a very annoying issue in the field of Data management. Some time due to sudden power failure or some other reasons our database gets suspected and we can not use it anymore.

     To over come this issue, there are some tricks that may be helpful to recover your suspected database

1.       Restore latest backup

                 The easiest way to make suspected database is to restore it with latest available healthy backup. It’s always very easy and secure method.. but what if you don’t have backup ?

         Then you need to try out second option

2.       Using SQL Query

              You need to execute following query in query analyzer to overcome this issue

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Note : Kindly take approval from competent authority before execution of above query as this query may result in some of data loss

3.       The another way to recover suspected database is

Step 1 : This is for replacing dbname/ldf path name

Run the Query using Query Analyser (QA)

                        Use master

                        Go

                        sp_configure 'allow updates', 1

                        Reconfigure with override

                        Go

Step 2: execute the following script

                        Update sysdatabases set status= 32768 where name = 'your db name'

Step 3:

                        Restart MSSQLSERVER service, the database will be in Emergency mode

Step 4: Rebuild Log. From QA execute script

                        DBCC REBUILD_LOG ('your db name', 'db path log name{eg: E:\postman_Log.LDF}')

                        after running this -You got a

                        --Message - Warning: The log for database 'postman' has been rebuilt.

Step 5 From QA execute following script

                        Use master

                        Go

                        sp_configure 'allow updates', 0

AFter this your database is recovered but the same is in single user mode. Please go to properties and set it to multi user mode , or use the query

                        ALTER DATABASE yourDBname SET MULTI_USER

Hope any of  the above method will help you to recover your suspected database. Kindly comment if you have any another interesting method for this matter

Thanks