Keeping up to date with patches and fixes is the best way, but if this has failed you there are several options available for fixing your SQL Server databases.
The first thing that needs to be understood is why your database got into this state in the first place. If it was an act of nature (power outage) then you’re luckier than most. Someone may have deleted some tables or data, infected your SQL Server with a virus or trojan horse. Whatever the reason, without MS support it’s unlikely you’ll ever know what happened exactly. Therefore I recommend exporting your live data before attempting any repairs; otherwise you run the risk of running queries on corrupted data, making matters worse!
If all else fails at least try and remember how your database looked before it got into this state. That way you can compare and contrast any changes made whilst attempting repairs.
The following list is by no means conclusive; they are just some of the more popular tools available on the web:
SQL Check and Repair (Freeware):
http://www.mssqltips.com/sqlservertip/2892/quickly-repairing-a-damaged-sql-server-database/. This utility actually copies your corrupt SQL Server database file to a new file, validates and then attempts to repair. You can use it even if you don’t know the password for the original SQL Server instance that hosts the damaged database. SysTools SQL Recovery (Commercial): http://www.systoolsgroup.com/sql-recovery
This is a popular commercial product and has the great advantage of being able to recover even encrypted databases. It can be used on all versions of SQL Server from 7 to 2008 R2, including Express editions which are free! However it does not support AF or spatial data types so may fail on some corrupt tables. I have tried this tool myself with good success in the past but would always back up my live database before attempting any repairs.
SQL Repair Toolbox for SQL Server (Free Open Source):
https://www.sqlrepairtoolbox.com. This is an open source freeware product that attempts to repair both SQLite and MS SQL Server databases. Again, use this product with caution as it cannot repair AF or spatial data types.
Regenerate Oracle (Free):
https://www.regenerate-oracle.com/en. This open source freeware tool is for repairing corrupt SQL Server database files; the good news is that it does support all data types including geo data types and can be used on all versions of SQL Server from 2000 onwards! Note however that it will also need to copy your ORIGINAL table space file so if you have deleted or truncated any user created table spaces then you must ensure they exist in the live instance prior to running this utility.
Table Nexus Data Repair Tool (Commercial):
http://tabledataretriever.com. This tool works on most versions of SQL Server from 2000 onwards and has a nice interface with several repair modes: Standard, Advanced and Custom. It also supports AF and Spatial data types and the ability to undo the last repair operation, which is useful if you make things worse!
SQL Repair (Commercial):
https://www.sqlrepairtoolbox.com/products/sql_recovery_software. This product comes in two flavors for Oracle and MS SQL Server as well as many different editions such as Standard, Workgroup, and Developer & Enterprise. However be aware that this product is now over 10 years old so it may no longer support modern versions of SQL Server or SQL Server 2017. As always it’s advisable to back up your data before repairing it.
SQL Data Recovery (Commercial):
This product is available for SQL Server and Oracle and can even recover important emails from a corrupt MS Outlook database! However as with most commercial products, use this one with caution and always back up your live database first!
Recovery Toolbox for MySQL (Free):
Yet another worthwhile open source freeware tool which has been around since 2010 so should support the current builds of MySQL as well as earlier versions such as 5.6 & 5.7; it also supports AF and Spatial data types so if you have corrupted tables in either of that format then give this one a try.
Recovery Toolbox for Postgre SQL (Free):
Yet another open source freeware tool which has been around since 2010 and like the MySQL version should work with most versions of Postgre SQL too! Again, if you have corrupt AF or Spatial data types then this utility will fix them but please note it can’t repair tables that are part of a cluster so if your desired table is in a cluster you must move it to an individual database first before attempting any repairs.
Conclusion:
I hope you have found this article helpful in sorting through the many options for repairing SQL Server database files. Once again I recommend that you always back up your live database before attempting repairs so do please leave me a comment if you believe I have missed any other useful utilities or repaired databases!