Introduction:
In this blog, we will discuss step by step how we can repair MS SQL Databases. I already installed MS SQL Server 2019 on my computer. Now, we will study with MS SQL Server 2019.
The article will cover how to repair the SQL Server database 2019 step by step.
Summary:
First of all, we will discuss why we need to repair SQL database. Here, I show you some main reasons that make corruption in SQL Database.
If the user forgets the password of the database and the user enters the wrong password multiple times.
Sometimes the user removed the files from the directories accidentally and overwrote with existing files.
Some viruses and malware also corrupt the database files.
Files are corrupt when your computer drive is damage.
Now, I list out the solutions that will help you to reduce corruption. To solve the issues, there is no need to download and install any other software.
Recover the database from the backup file.
Using DBCC CHECKDB Commands.
Recreate the .mdf and .ndf files.
Recover the database from a backup file
The users of MS SQL Server work on the large size of the database and take the backup of the database daily. Sometimes the user connects to the server and sees some corruption in the database. Now the solution is this restore your database from your backup file and remove the corruption. I show you some sample databases see the screenshot.
In the list, you can see Northwind and pubs. Now I show you how you can restore your database from your backup file. Right Click on the corrupted database and, you will see some option.
Task-> Restore-> Database……
If there is any backup file that already exists related to the selected database. Then MS SQL Management Studio will select the backup file automatically.
After pressing the “OK” button you will see a message on the screen “The database Northwind restored successfully”.
Now you will see there is no corruption in your database.
DBCC CHECKDB Commands
Run DBCC CHECKDB commands for your database. See the index id if the index is greater than 1 Drop and Recreate it
If the index is 0 or 1- re-run DBCC CHECKDB with suitable repair options such as NOINDEX, REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST | REPAIR_REBUILD, REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST and etc.
Here is the standard syntax that also you find on the Microsoft official website “DBCC CHECKDB”.
When you open the code on the official site there is an option you can copy and use the code in anywhere.
Recreate the .mdf and .ndf files
Stop the MS SQL Server’s services and close SQL Server Management Studio. Copy your .mdf and .ndf file and past the files on different locations. Now, delete the previous/original files. Now run SQL Server Management studio again with a new instance and create a new database. Now overwrite the pasted files in the new database. Let’s turn on your server’s emergency mode. Successfully enter the command
DBCC CHECKDB (databaseName,REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS.
Stellar Repair for MS SQL:
Stellar Repair for MS SQL is a tool that helps us to repair our corrupted files and recover our deleted files records and etc. When you will start the Stellar Repair for MS SQL you will see a message. In the message the stellar tells us to copy the file and move it on another location. Also, when you will use this SQL Recovery tool you need to stop all the MS SQL Services.
Supported file extensions .mdf and .ndf.
Repair our corrupted database.
Support multiple versions of MS SQL Server including 2019.
Recovers database components.
Save the result in multiple formats like MDF, CSV, Html, XML.
How to repair SQL Server 2019 Database using SQL recovery software
This is the main interface of Stellar Repair for MS SQL
Select SQL Server 2019 database file
Click Repair
The software will start the repair process and, you can check it on status bar
After completion of the repair process, you can check preview of your database
Software provides 4 options MDF, HTML, CSV and XLS to save the repaired database.
Note: You can save the database as NEW or Live with MDF saving option. The software provides two authentication options (Windows and SQL Server) to connect with the database.
Conclusion:
In this blog, we studied how we can repair our database manually or without any tool. In method one we learned the repair of a database with the backup file. In method two we studied the command DBCC CHECKDB and the syntax of the command provided by Microsoft. In method three we studied how we can overwrite .mdf file in a new database after running the command the corruption will remove from the database. At the last of the blog, we studied SQL database repair software.