suspect mode: database

---once the database goes to suspect mode
--follow the steps to recover the database

--1. reset the database from suspect mode
--sp_resetstatus turns off the suspect flag on a database.
--This procedure updates the mode and status columns of the named database in sys.databases.
--Also note that only logins having sysadmin priveleges can perform this :


use master
GO
exec sp_resetstatus 'db1';
--2.set the database to emergency mode
--Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy
--only members of sysadmin fixed server roles have privileges to access it.
--The basic purpose for this is to facilitate troubleshooting

alter database db1 set emergency;

--3.run the consistency test
dbcc checkdb(db1)

--4. Roll the databse to single user mode
use master
GO
ALTER DATABASE db1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE


--5. this query will attempt to repir all the reported loss. This query can cause some data loss
dbcc checkdb('db1',REPAIR_ALLOW_DATA_LOSS)

--6.finally bring the database ONLINE and set to multiuser mode
ALTER DATABASE db1 SET MULTI_USER
WITH ROLLBACK IMMEDIATE