SQL is one of the most popular relational databases. If you’re using it to power your website or application, you must have experienced the SQL corruption errors. At times, the server becomes inaccessible due to corrupt files. To resume using it, you need to repair it. As you see in this article, there are two ways of repairing SQL database corruption, one of them being by using Microsoft SQL Server Management Studio Express.
Why SQL Databases Get Corrupted?
Seeing your SQL database getting corrupted every now and them is frustrating. And there are a number of reasons for this error. You should learn about them to potentially avoid them. The common reasons are:
As already said, one of the ways to fix a corrupted SQL database is by using Server Management Studio Express. In this section, learn how to use it.
First and foremost, you need to download and install it if you haven’t already. Once that’s out of the way, follow the steps:
If you get a message saying there were too many connection attempts, you should close Studio Express and reopen it.
So that’s how you repair corrupted SQL database.
Repairing SQL Server Database Marked as Suspect
At times, you’ll see the SQL database marked as suspect mode. When so, the database will stay unavailable for use. So you need to repair it as soon as possible. For this purpose, you have to utilize the DBCC Check command. Here are the steps to deal with this error:
Set SUSPECT to Emergency Mode
In the first step, you need to set the database to Emergency mode using this command -
In this mode, the system administrators only have read-only privileges. The Emergency mode can only be set by those that are included in the sysadmin list.
Check the Damage Level
As the next step, you have to check the damage sustained by the database. For this purpose, use this command -
It will analyse all the errors and list them in the terminal.
Execute Repair
This part is going to be exhaustive and long. When you run DBCC CHECKDB, you need to choose three repair modes. Those are:
REPAIR_FAST
Using this mode will allow you syntax to be backward compatible. So you can run SQL Server 2005 file in Server 2008.
In this repair mode, no heavy actions are performed. Before executing this syntax, you should ensure the database is in SINGLE USER and not Emergency status.
Once you’ve set the database to SINGLE_USER, execute the following command:
REPAIR_REBUILD
Use this command when you want to minimize the data loss. This mode repairs most of the things that REPAIR_FAST does include some time-consuming repairs. So it may take longer.
Use this command -
REPAIR_ALLOW_DATA_LOSS
You should use this repair mode when you’re okay with data loss. Besides repairing the corrupt parts, this mode will allocate and de-allocate pages and fix page errors. If there are any structural errors, it would fix them too.
Use this syntax to begin repair in this mode -
Set Database to MULTI_USER
In the final step, after the repair is over, you need to set the database back to MULTI_USER. It’d ensure all the intended users can access the database.
Use this command for this purpose -
DBCC CHECKDB Might Not be Ideal in Every Case
While the above method is helpful, it doesn’t work in every case. Furthermore, it doesn’t guarantee that the data will be recovered. Therefore, you need to use third-party utilities like SQL Database Recovery to recover the data. Otherwise, you run the risk of losing them forever.
In conclusion, that’s how you repair corrupted SQL database. Consult with your hosting provider and figure out the best solution for your case.
Why SQL Databases Get Corrupted?
Seeing your SQL database getting corrupted every now and them is frustrating. And there are a number of reasons for this error. You should learn about them to potentially avoid them. The common reasons are:
- Power cut - When there’s a sudden shutdown because of an unaccounted power cut, it can lead to SQL database corruption.
- Buggy code - If bugs make it to the database, then they can corrupt the database itself.
- Corrupted File - If the bugs corrupt any of the files associated with the database, it can corrupt the SQL database as well.
- Hardware crashes - When the hardware that run and support the database crash or get damaged, it can lead to SQL database corruption.
As already said, one of the ways to fix a corrupted SQL database is by using Server Management Studio Express. In this section, learn how to use it.
First and foremost, you need to download and install it if you haven’t already. Once that’s out of the way, follow the steps:
- Launch Studio Express by navigating to Start > Programs > Microsoft SQL Server 2005/2008 > SQL Server Management Studio Express
- Use Windows Authentication to log into your database
- Expand the database by right-clicking on “wt_sched” and then selecting “Properties”
- Next click on “Options” and scroll all the way down to the bottom (for the right hand side pane)
- Change the option for Restrict Access to “SINGLE_USER”
- Repeat the same procedure for wtMaster by clicking “OK”
- From the toolbar, locate and click on “New Query” and enter these commands:
Code:
dbcc checkdb(‘wtMaster’,REPAIR_REBUILD)
dbcc checkdb(‘wt_sched’,REPAIR_REBUILD)- Select “Execute” to start repairing the database
- Now change the database back to MULTI_USER
If you get a message saying there were too many connection attempts, you should close Studio Express and reopen it.
So that’s how you repair corrupted SQL database.
Repairing SQL Server Database Marked as Suspect
At times, you’ll see the SQL database marked as suspect mode. When so, the database will stay unavailable for use. So you need to repair it as soon as possible. For this purpose, you have to utilize the DBCC Check command. Here are the steps to deal with this error:
Set SUSPECT to Emergency Mode
In the first step, you need to set the database to Emergency mode using this command -
Code:
ALTER DATABASE DATABASE_NAME SET EMERGENCYCheck the Damage Level
As the next step, you have to check the damage sustained by the database. For this purpose, use this command -
Code:
DBCC CHECKDB (DB_Name)Execute Repair
This part is going to be exhaustive and long. When you run DBCC CHECKDB, you need to choose three repair modes. Those are:
Code:
REPAIR_FAST
REPAIR_REBUILD
REPAIR_ALLOW_DATA_LOSSUsing this mode will allow you syntax to be backward compatible. So you can run SQL Server 2005 file in Server 2008.
In this repair mode, no heavy actions are performed. Before executing this syntax, you should ensure the database is in SINGLE USER and not Emergency status.
Once you’ve set the database to SINGLE_USER, execute the following command:
Code:
DBCC CHECKDB (N ‘Database_Name’, REPAIR_FAST) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GOUse this command when you want to minimize the data loss. This mode repairs most of the things that REPAIR_FAST does include some time-consuming repairs. So it may take longer.
Use this command -
Code:
DBCC CHECKDB (N ‘database name’, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;You should use this repair mode when you’re okay with data loss. Besides repairing the corrupt parts, this mode will allocate and de-allocate pages and fix page errors. If there are any structural errors, it would fix them too.
Use this syntax to begin repair in this mode -
Code:
DBCC CHECKDB (N’Database_Name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GOIn the final step, after the repair is over, you need to set the database back to MULTI_USER. It’d ensure all the intended users can access the database.
Use this command for this purpose -
Code:
ALTER DATABASE DATABASE_NAME SET MULTI_USERWhile the above method is helpful, it doesn’t work in every case. Furthermore, it doesn’t guarantee that the data will be recovered. Therefore, you need to use third-party utilities like SQL Database Recovery to recover the data. Otherwise, you run the risk of losing them forever.
In conclusion, that’s how you repair corrupted SQL database. Consult with your hosting provider and figure out the best solution for your case.
