As you can see in the above screen capture, the T-SQL query gave the warning message upon execution:
You must recover this database prior to access
The next step was to set the SUSPECT database into an EMERGENCY mode. This was done by executing the below SQL query against the master database.
Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it. The basic purpose for this is to facilitate troubleshooting. I did not want other users updating the database while it was being worked on.
ALTER DATABASE test_dr SET EMERGENCY
As you can see from the above screen capture, once the T-SQL query got executed successfully the state of the database changed from SUSPECT to EMERGENCY.
Once the database state was changed to EMERGENCY. I then performrf a consistency check by executing the below T-SQL query against the master database.
Which resulted in the below output:
As seen from the above screen capture there is no issue with respect to consistency of the test_dr database. Also, this confirmed that the logical and physical integrity of the database was intact.
The next step was to set the database to SINGLE USER mode with ROLLBACK IMMEDIATE. To do this the below SQL query was executed against the master database.
The above query will rollback any transactions if any are present in the test_dr database and will bring the database named test_dr into Single User mode.
ALTER DATABASE test_dr SET SINGLE_USER WITH ROLLBACK IMMEDIATE8
Please refer to the screen capture below:
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.
This query will attempt to repair all reported errors. These repairs can cause some data loss.
DBCC CheckDB ('test_dr', REPAIR_ALLOW_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.
Please refer the screen capture below.
ALTER DATABASE test_dr SET MULTI_USER
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.
THE ULTIMATE SQL SERVER PERFORMANCE TOOL http://www.sqlsentry.com/download-trial/trial?utm_source=ssp&utm_medium=email&utm_content=3-feature-bullets-free-trial&utm_campaign=201505-complete-performance-solution&ad=201505-email-ssp-complete