Free Download
You might need to perform SQL database repair operation in the following scenarios:
- Repair a database that is marked as suspect or stuck in recovery pending state, preventing you from accessing the database files.
- Repair a database that has turned corrupt due to events like server crash, I/O subsystem failure, software bugs, virus/malware intrusion, etc.
- If you receive SQL errors like 824, 8992, 5172, etc.
- If you have a corrupt log file that has rendered the db inaccessible.
Also read:SQL Server Production Database Corruption – Causes & Fixes
通常任何数据库管理员或SQL用户will have a backup-and-recovery strategy in place. However, you canrestore SQL databasefrom a backup only if is healthy. Also, you must restore to the most recent backup to avoid data loss. But if any corruption exists within the backup or it is not updated, you will need to repair it.
Now, let’s discuss in detail how to repair SQL Server 2016 database step-by-step.
Repairing SQL Server Database Step by Step
圣eps to Repair SQL Database via SSMS
You canrepair a database using DBCC CHECKDBcommand with ‘REPAIR_ALLOW_DATA_LOSS’ by executing the following steps in SQL Server Management Studio (SSMS):
圣ep 1:Launch SSMS. Connect to SQL Server 2016 instance using Windows Authentication or SQL Server Authentication.
圣ep 2:Click the New Query tab from the toolbar. In the query window that opens, copy and paste the following commands in the same sequence as below:
EXEC sp_resetstatus [DB_Name]
ALTER DATABASE [DB_Name] SET EMERGENCY
DBCC CHECKDB [DB_Name]
ALTER DATABASE [DB_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
ALTER DATABASE [Database Name] SET MULTI_USER
Note:Running the REPAIR_ALLOW_DATA_LOSS command can lead to data loss. If DBCC CHECKDB does not work or to prevent data loss, using a SQL repair tool may help.
圣ep 3:Hit the"Execute"按钮开始修复数据库。
The database will get repaired and will open without any errors.
圣eps to Repair SQL Database Using Stellar Repair for MS SQL
An advancedSQL repair toolrecommended by MVPs and DBAs,圣ellar Repair for MS SQLhelps repair severely corrupted database files (MDF/NDF) in a few simple steps. Also, it helps recover all the database objects along with deleted records.
How to Repair SQL Server 2016 Database Using SQL Recovery Software
圣ep 1:Launch the software. Read the instructions displayed on the software main interface window, and then clickOK.
圣ep 2:On the‘Select Database’screen,browseand select the database primary file (.mdf) you want to repair. Alternatively, clickSearchto locate an MDF file if its location is unknown.
Note:If you wish to recover deleted records, ensure that“Include Deleted Records”checkbox is checked.
圣ep 3:After selecting an MDF file, clickRepairto begin the repair process.
圣ep 4:Choose an appropriate scan mode to repair the MDF file. Selecting the圣andard scanoption quickly scans the file for corruption errors. For thoroughly scanning the file, you may selectAdvanced Scan. ClickOKto proceed.
步骤5:Click theOKbutton once the‘Repair Complete’message box pops-up.
圣ep 6:Preview all the repaired file data. Next, select all or specific objects you want to save by clicking on theSavebutton underFile.
圣ep 7:The software provides different options to save the repaired database:
- Choose the file format (MDF/CSV/HTML/XLS) in which you want to save the repaired file.
- On selecting the MDF file saving format, you can select to save the database as a New or Live db. Further, enter details to connect to your SQL Server instance.
- Specify the location where you want to save the repaired db.
圣ep 8:ClickSave. After successful completion of file saving process, the following screen will appear.
The repaired database will get saved with the same name but with ‘Repaired’ prefix at the specified location.
Conclusion
在本文中,我们讨论了常见的实例that might require you to repair a SQL database. Before trying to run a repair operation, you must first try to restore an updated backup. But if the backup is corrupted or obsolete, perform the steps discussed in the article to resolve the issue. You can run DBCC CHECKDB with the recommended repair option, usually REPAIR_ALLOW_DATA_LOSS, to perform the repair process. However, it involves risk of data loss. As an alternative, use Stellar Repair for MS SQL to fix common corruption errors that might be preventing you from accessing the db.
FAQs
Q.My database log file turned corrupted and made the database unusable. I needed to rebuild the DB. To do so, I executed the following command:
alter database DB1 SET single_user with immediate rollback
Subsequently, I ran the below command:
use master
dbcc checkdb('DB1',REPAIR_ALLOW_DATA_LOSS)
Executing this query is giving me an error message:
“Msg 924, Level 14, State 1, Line 2. Database 'DB1' is already open and can only have one user at a time.”article
After researching, I found that the database needs to be set up in EMERGENCY mode first and then in SINGLE_USER mode. What should I do next?
A.Try reverting your database to MULTI_USER mode, and then run the following:
alter database DB1 SET single_user with rollback immediate
dbcc checkdb('DB1',REPAIR_ALLOW_DATA_LOSS)
Chances are that some other process is using the database connection, before you try to connect to the db. Running the above commands together can help you get that connection.
