Recovering a SQL Server Database from Suspect Mode
The next step was to perform a DBCC Checkdb along with Repair
with Data Loss by executing the below T-SQL query
against the master database.
DBCC CheckDB ('test_dr', REPAIR_ALLOW_DATA_LOSS)
This query will attempt to repair all reported errors. These
repairs can cause some data loss.
Once the DBCC CheckDB with the Repair with Data Loss
option were executed, the Database went into Single User mode as shown below:
After performing the above step the database was brought
ONLINE and Multiple Users access was enabled by executing the below
T-SQL query against the master database.
ALTER DATABASE test_dr SET MULTI_USER
Please refer the screen capture below.
As you can see from the above screen capture the database named
test_dr is back ONLINE. I am even able to view its objects as shown below:
As final step for safety, I again
checked the consistency of the database which was just repaired and brought
ONLINE (i.e. the test_dr database) by executing the below T-SQL
query against the master database.
DBCC CheckDB ('test_dr')
After performing the above steps I ensured that all the
required logins had access to the database with proper privileges. The
application started working fine and the business was back on track. It took
just 38 minutes to bring the SUSPECT database back ONLINE.
Please let me know if you have any comments on this approach or alternative approaches you have used in the past.
Pages: 1 2