SQL Server databases can become badly damaged if a storage system crashes or write operations are not delivered to the disk. In order to recover from database corruption, the best remedy is a good backup and recovery solution. Popular systems like Symantec Backup Exec have well-supported modules available for SQL database backups.
If you are in the unfortunate situation of a damaged database file and no good backup available, you can try the SQL “Emergency Mode Repair” procedure. SQL Server 2005 and newer have a supported emergency database repair procedure which is documented on the following pages. Try this if standard database recovery has failed and a good backup is not available. This procedure will attempt to repair a corrupted transaction log and/or minor damage to a database file.
- Know How and When to Use Emergency Mode Repair (technet.microsoft.com)
- Ta da! Emergency mode repair (SQL Server Storage Engine Blog)
- EMERGENCY-mode repair: the very, very last resort (Paul Randal on sqlskills.com)
Corrupted SQL databases may be unrecoverable, so if you are lucky enough to recover with an emergency mode repair – make sure to set up a good backup schedule for your important databases and store those backups in one or more secure and safe locations! Don’t forget to test a database restore from backup periodically to make sure those backups are usable.
If you’re running a version of SQL Server older than the 2005 version, there is an undocumented emergency recovery process that you should be able to find with a quick web search. I highly recommend that you upgrade to SQL 2005 or newer to take advantage of the many enhanced features including the one listed here. Good luck with your SQL solutions!