MS SQL server 2008 R2 is one of the most widely used database servers. This database server is primarily designed for business use. However, sometimes, this database gets corrupt due to various reasons. When the server database gets corrupt, you lose access to it and may encounter different error codes such as SQL Server Error 8946, 5172, 823, 2, 824 (I/O error), and 825 (read retry).
|In this post, we’ll discuss the methods to repair the corrupt SQL Server 2008 R2 database. But before that, let’s take a look at some common reasons for SQL server database corruption.
Common Reasons for SQL Server 2008 R2 Corruption
- Drive containing SQL database is corrupt or has physical problems
- You ran a query against a partitioned table that has data compression enabled in SQL Server 2008 R2
- Virus infection on SQL server
- Sudden server shutdown
Special Offer from Stellar
Get an exclusive offer on SQL data recovery services, drop a email at firstname.lastname@example.org to avail offers and discounts.
Methods to Repair SQL Server 2008 R2 Database
When the database fails or gets corrupted, you can use the backup to restore it. However, if the backup is not available, you can use DBCC CHECKDB command to fix the database. If this doesn’t work, contacting a SQL database repair expert such as Stellar is the only option. They can help you repair the MS SQL database safely and effectively.
Method 1. Use DBCC CHECKDB
DBCC CHECKDB is a command-line utility that checks the physical and logical consistency of database. It checks the database pages, rows, system tables, referential integrity, allocation pages, index relationships, etc.
To fix the SQL database issues, DBCC CHECKDB can be used with any of these arguments:
- Whichever argument you use with DBCC CHECKDB, make sure that you set the database to single-user mode before running the complete command.
- Once you’re able to repair the corrupt database, change the database mode to multi-user.
- While executing the commands, replace the name of corrupted SQL server 2008 R2 in example commands with the name of your actual database.
- Back up your SQL server 2008 R2 database before running the DBCC CHECKDB.
Way 1. Use REPAIR_FAST Argument
This argument in the DBCC CHECKDB command helps in fixing backward compatibility issues with the database. Given below is an example of complete command that you need to run on SQL server.
DBCC CHECKDB (N ‘Database_Name’, REPAIR_FAST) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Way 2. Run DBCC CHECKDB with REPAIR_REBUILD Argument
Running DBCC CHECKDB with REPAIR_REBUILD repairs missing rows in non-clustered indexes, rebuild an index, etc. Using this argument doesn’t repair issues involving FILE STREAM data. It doesn’t involve a risk of data loss. Follow the example to execute the repair query.
DBCC CHECKDB (N ‘Database_Name’, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Way 3. Repair SQL DB by Using REPAIR_ALLOW_DATA_LOSS Argument
[CAUTION]: Using REPAIR_ALLOW_DATA_LOSS argument to repair MS SQL database might lead to data loss.
REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors or corruption. While repairing, it might deallocate a row, page, or series of pages to clear the errors, resulting in data loss.
Run the command with REPAIR_ALLOW_DATA_LOSS as shown below:
DBCC CHECKDB (N’Database_Name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
If you’re able to repair the corrupt SQL database by using the above DBCC CHECKDB commands, you need to change the database to multi user mode. To do so, run the command as shown in below given example:
ALTER DATABASE Database_Name SET MULTI_USER
Method 2. Seek an Expert’s Advice
If the problem still persists, get the help of a professional SQL database repair service provider such as Stellar. They can fix SQL Database errors and corruption in primary (MDF), secondary (NDF), and transaction log (LDF) database files. Experts at Stellar can help you fix any type of corruption in database files such as corruption of file header, tables, non-clustered indexes, data pages, metadata, page level corruption, etc. These experts can repair the database even if it has got corrupt due to malfunctioning SQL DB storage disk.
Stellar uses proprietary tools to effectively repair your MS SQL server 2008 R2 database files without affecting the Schema or data. The database repair service offered by Stellar is safe and secure.
When the MS SQL server 2008 R2 database get corrupted, it becomes inaccessible or throws errors. Using DBCC CHECKDB utility, as discussed in this post, can fix corruption or errors in MS SQL server. However, it’s not an effective solution as it can’t fix major issues with the database files or may result in data loss. Thus, contacting a SQL database repair expert is the best approach to get your database repaired with safety and effectively.