SQL Server is a popular and one of the most used databases around the world. There are millions of users using this popular DB worldwide.
However, during the day, your database can suddenly be corrupted. It can be a virus, a malware, a sudden shutdown an upgrade interrupted or a hardware problem. Also, a malicious employee or a hacker can attack your database. The hard disk failure is a very common problem to corrupt a database. That is why a good DBA should monitor not only the database and the operative system, but also the hardware using monitoring tools.
If your database is corrupted and the corruption is severe, no user will be able to access to the database and you may need to find a solution.
When the database fails, and it is corrupted, you can use a backup to restore the database. If your backup is corrupt or infected by a virus, it is possible to run the DBCC command to repair the database. If the DBCC also fails, it is possible to use the Stellar Repair for MS SQL.
In this article, we will give an introduction about this tool with a step by step about how to use it.
Preventing corruption
One of the most common causes of a database corruption is a hardware failure. Monitoring your disk counters using the system monitor can help to monitor how it works using a baseline. Also, the CHKDSK command can help to check your disks.
If you want to prevent database corruption then, a good firewall, using non-default ports, a great antivirus can help you to minimize attacks.
Running the DBCC CHECKDB can help you to detect errors. Note that, there are some corruption problems not detected by the DBCC CHECKDB named silent corruption.
You can also enable CHECKSUM for page verification in SQL Server. This option allows detecting corruption when it happens.
The CHECKSUM is available in SQL Server on-premises, Azure SQL and also in Azure SQL Datawarehouse.
Some differences between SQL Server versions about database corruption
One database that is different from the SQL Server versions is the Resource database. This database is almost invisible for the users. It is a read-only database and cannot be back up. It is difficult to corrupt this database. The only way would be a hardware failure. This system database contains the object files and other internal information. The name of the data file associated is the mssqlsystemresource.mdf and you can find it in a path similar to the following:
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
In SQL Server 2005 this database was attached to the master database, then it was easier to be corrupted than the next versions.
SQL Server in the different versions has enhanced the security which reduces the risks of attacks and may reduce the possibilities of the database to be corrupted by attacks.
For example, SQL Server 2008 included the Transparent Data Encryption that allows encrypting your database. Also, SQL Audit was introduced in SQL Server 2008.
In SQL Server 2012, you can easily store your backup in Azure which increases the backup security in case of disasters. SQL Server 2012 also supports Managed Services Account, which limits the privileges of the SQL Server services with restricted access that improves the security and avoids attacks that can corrupt the database. The AlwaysOn was also introduced in this version that was a great way to have the database online in case of a problem with one of the nodes.
Also, in SQL Server 2014 the backup to Azure was improved and enhanced.
When the database is corrupted, however, the behaviour is similar in any SQL Server version. The corruption levels are the following:
Getting started
When we have a corrupted database, we can use the Stellar SQL Database repair.
This software works in any SQL Server version included SQL Server 2005, 2008 R2 and all the other new versions and editions in Windows.
If you have a database, you just need to find the MDF file. These files contain the corrupt database.
You can search the files using the Find button and specify the path to search the files. By default, the option to search in subfolders is activated:
By default, the data files are stored in a path like the following:
C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\DATA
The software detects the file size, modified data, last accessed date and creation date.
You can select individually which file to repair. Select a data file and you can press the repair button to repair your SQL database. Optionally you can check the option to include deleted records.
There is also a button to clear the list of mdf files detected.
If your database is online, you will receive an error message that the application is being used. If that is your case, you may need to stop your SQL Server Service.
We recommend using the SQL Server Configuration Manager for this purpose:
Once stopped, run the Repair button again in Stellar Repair for MS SQL.
When you try to Repair, you will receive a message to specify your SQL Server version:
If you do not know your SQL version, you can go to the SQL Server Configuration Manager go to the menu and select help and about.
If everything is OK, you will receive a success repair message:
The log report at the bottom of the application will give you details of the tables, collation and objects repaired:
When you press the Save icon, you have the option to save the data in SQL Server (MSSQL), in a comma-separated values file (CSV), in HTML and, an Excel file:
In this example, we will save the information in CSV files:
You also have the option to update your software to a more current version of Stellar Repair for MS SQL.
Conclusion
In this article, we have learned how to use a tool to repair a database when it is corrupted. Also, we have learned how to prevent corruption problems and what can cause a database corruption. To repair a database, we need to find the mdf file that contains the database information and then stop SQL Server and finally, we repair it. With Stellar Repair for MS SQL, you can store the data in SQL Server, HTML files, CSV files and Excel format. All the repair process is stored in a log that can be saved for further analysis. Stellar Repair for MS SQL is a powerful tool to repair the data.
4 min read
4 min read
4 min read
