As a SQL database administrator, it’s your responsibility to maintain and secure the databases. However, you may still experience database corruption. If not handled correctly and on time, you may lose potential data. This post will discuss how you can repair database in SQL Server Management Studio (SSMS) when there is no backup. Also, we will discuss an alternative solution to repair a SQL database without data loss risk. But let’s first discuss the possible reasons that can result in database corruption.
Possible Reasons for SQL Database Corruption
- Server crashes or the system shuts down abruptly in the middle of processing data
- I/O subsystem failure
- Bug in the SQL software
You can call your hardware vendor for support to fix issues with the hardware. If the problem is with your SQL software, check out for updates released by Microsoft to fix the bugs in SQL Server. However, in case of a system crash or abrupt system shutdown, you will need to repair the corrupted database to recover its data.
Before We Proceed
Make sure that your system meets these prerequisites:
- SSMS must be installed on your machine. If it is not installed, you can download and install it from here.
- SQL must be installed on your PC.
Steps to Repair Database in SQL Server Management Studio
- Launch SQL Server Management Studio and connect to a server instance.
- In the Object Explorer window, click the ‘+’ sign to expand databases.
- Right-click on a database that you want to repair, and then select Properties.
- In the ‘Database Properties’ dialog box, click the Options tab from the left pane. In the Options screen, scroll down to the bottom of the screen.
- To repair a SQL database, you will need to set it to single-user mode. For this, Restrict Access to SINGLE_USER from MULTI_USER. Click OK.
ALTER DATABASE Test SET SINGLE_USER |
Here ‘Test’ is the name of the database that needs to be repaired, replace it with your corrupted database.
REPAIR OPTIONS | Description |
REPAIR_FAST | It only maintains the syntax for backward compatibility and does not perform any repair actions. |
REPAIR_REBUILD | It helps perform quick repair tasks without any data loss. It can be used for repairing missing rows in nonclustered indexes and rebuilding an index. It cannot repair errors associated with FILESTREAM data. |
REPAIR_ALLOW_DATA_LOSS | It can repair all the errors reported by DBCC CHECKDB. But, Microsoft advises on using this option as a last resort to repair the SQL database, resulting in data loss. |
Click ‘New Query’ from the main menu.
In the query window that opens, copy and paste the command below:
DBCC CHECKDB ('DB_Name’, REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
In the above command, replace ‘DB_Name’ with the name of your corrupted SQL database. And choose one of the repair options. Here’s an example of using the DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’option to repair the database.
- Click the Execute button to run the query.
- This will repair the database. Change the database mode from ‘SINGLE_USER’ to ‘MULTI-USER’ by performing steps 4–6 above. Make sure to close and re-launch the SQL Server Management Studio for the changes to take effect.
How Can I Recover Missing Data after Running DBCC CHECKDB?
If you find missing data in the repaired database after running the DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command, consider using a SQL repair tool. Recommended by DBAs and MVPs worldwide, Stellar Repair for MS SQL is a reliable software you can use to handle corruption errors in a SQL database. It helps repair a severely corrupted MDF/NDF file on Windows as well as Linux systems.
The software recovers all the database objects, like tables, views, keys, stored procedures, etc., without making any changes to the original database structure. It also helps recover deleted records.
Conclusion
This article outlined the most common reasons leading to SQL database corruption. While restoring from backup is the most recommended and obvious choice to deal with database corruption, there are times when the backup is not updated or has turned corrupt. In that case, you need to try repairing the database. This article covered the step-wise instructions following which you can repair the database in SQL Server Management Studio. Also, it advised using a SQL recovery tool as an alternative to DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS command.
