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