|
|
There can be many reasons for a SQL Server database to go in a 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 is the code to do the above tasks:
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
Did you like this post?
|
|
|
||
|
|
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
|
print this post |
|
follow me on twitter |






comments
7 Responses to "Repair SQL Server Database marked as Suspect or Corrupted":hugs: thank you thank you thank you Madhivanan
-_- Nina
Thanks Nina for the feedback
thanks dear , actually i facing this issue .. i wondering before do any action , is there any risk for data when i executing this solution ?
and should the SQL agent be stoped ?
message : Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
SQL Server 2008
wating for ur reply ?
Best Regareds ,
Musab,
You need to repair it before using the database. Follow the article
my problem is with MS-SQL 2005 and MSDB is marked corrupted and backup is missing at failure hard disk.
its posible recovery msdb?
Joe
joebanbino@yahoo.com
Hello Dear,
Well written, i m a product reviewer and i also write a article on the same topic. if you interested then check here
http://sql-server-recovery.blogspot.com/2010/10/repair-restore-sql-server-database-from.html
Post a Comment