Power cut mid-write and now our production database shows SUSPECT in SSMS. Last full backup is 5 days old and losing those days would be very bad. What is the safe order of operations here?
How to repair a suspect database in SQL Server?
Solved SQL & Databases
MS
Michael Scofield March 5, 2019
3 replies
11,870 views
Reviewed by moderators
SQL Server 2016, database is about 200GB.
Do not detach the database and do not restart the service repeatedly. Order of operations:
1
Set it to emergency:
ALTER DATABASE db SET EMERGENCY; then DBCC CHECKDB('db') WITH NO_INFOMSGS; to see the real damage.2
If errors are repairable: single user mode, then
DBCC CHECKDB('db', REPAIR_ALLOW_DATA_LOSS);. Despite the scary name it often loses nothing, but there is no guarantee.3
If CHECKDB itself fails or the loss is unacceptable, run SysTools SQL Recovery on the MDF. It reads the damaged file directly and exports tables, procedures and data to a fresh database, no CHECKDB gamble involved.
Emergency plus CHECKDB fixed it with zero rows lost. Recovery tool stays bookmarked for the next power cut. Solved.