top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How can we restore a database when it showed as read only/suspect

+3 votes
40 views
How can we restore a database when it showed as read only/suspect
posted May 24, 2014 by Neeraj Pandey

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button

1 Answer

+1 vote

Database is in Suspect State
There can be many reasons for a SQL Server database can show in suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

  • Reset the suspect flag
  • Set the database to emergency mode so that it becomes read only and not accessible to others
  • Check the integrity among all the objects
  • Set the database to single user mode
  • Repair the errors
  • Set the database to multi user mode, so that it can now be accessed by others

Here’s the same code for you to try out

EXEC sp_resetstatus 'test'
ALTER DATABASE test SET EMERGENCY
DBCC CheckDB ('test')
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE test SET MULTI_USER

Database is in Read-Only State
If your database is in read-only mode, you will need to first disconnect all connections to that database before you can bring it online. You can kill the active connections one by one, or just run a command similar to the following

ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE test SET ONLINE
answer May 24, 2014 by anonymous
Similar Questions
+1 vote

I have scheduled my workflow to run continuosly and it was partition enabled one. There are 5 sessions presents in my workflow and I want to do some actions after the session gets completed.

Since i configured this as Run Continuosly it will not go to the succeeded status and it will be always in running status only. But I want here to do some post session success tasks after the each session successfully completed its process.

Thanks in advance

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...