Business-critical data should be safe and always available without downtime or interruption. In that case, working as an MS SQL Database Administrator (DBA) is not easy as it involves keeping the SQL Server database up and running 24*7*365. If database entries do not happen correctly these lead to damage or corruption in the SQL database (MDF and LDF) files resulting in a nightmare for DBAs as well as the entire business. So to getting error is very much welcomed and to fix the error with proper steps is also a trick. Below is the steps how Stellar Phoenix SQL Database Repair software helped to recover corrupt MSSQL database error.
Problem at hand
A few days back, while handling the MS SQL Server I encountered the following SQL error:
Error – 104 having Severity level of 15
This was the first time I faced this error in my career as a SQL database administrator. However, I was not completely ignorant about what it signified and what could be done to fix it, I tried to find more about the error. The description of the error was that ORDER BY data items should be present in the ‘select list’ provided the statement comprises a UNION operator. The excerpt was the SQL Error 104 signifies corruption in the SQL’s database (MDF) file and needed to be fixed.
How I proceeded to perform SQL Database Recovery
As usual, I first tried to restore the database using the backup that I create regularly and keep it updated practicing as a SQL Server administrator. However, after giving some time to the restore process when I found that it was taking a bit longer than normal/usual, I resorted to the old and built-in ‘T-SQL mechanism’ to repair the damaged MDF file. The process involves multiple steps and is quite lengthy, but being a frequent and knowledgeable user I do not take much time executing it except the time that the T-SQL process naturally consumes.
To repair the corrupt SQL database, I followed all the steps sequentially that involved creating a new database of same size as the damaged one and to the one I was trying to attach, shutdown the SQL Server, replaced the MDF file of the new SQL database with old one, brought the server online (most of the databases do not go online, but in SUSPECT mode), placed the database into Emergency, and finally executed the DBCC CHECKDB command that rebuilds the log and removes corruption. However, at the end I found that there was data loss, thereby, making the database inaccessible.
I had heard about Stellar Phoenix SQL Database Repair software, used for repairing the damaged SQL database. Since the specifications to install the software were minimal, I could download and install the SQL recovery tool on my system with ease.
Next, I ran it on the MDF file that I had backed up before using the ‘T-SQL mechanism’ to repair the corrupt SQL database.
Below is the first screen that appeared: Image – 1
Image – 1
Well, I was happy to see that to repair the SQL Server database, only three short and simple steps: ‘Select’, ‘Repair’, and ‘Save’ were required. Plus, the GUI was so interactive that I neither visited the Help menu nor contacted the Support Center. I moved forward according to the instructions provided by the software in the interface itself. Finally, I reached at a point wherein I was asked to save the database, and that too in MSSQL, CSV, HTML, and XLS i.e. a total of four file formats. Check the ‘Save As’ dialog box below: Image – 2
Image – 2
In the ‘Saves As’ screen, I selected ‘MSSQL’, chose the storage location using the ‘Browse’ button, unchecked ‘Automatically open the destination folder after saving’ checkbox, and clicked on the ‘OK’ button.
The next screen that appeared is as follows: Image – 3
Image – 3
On it, I selected ‘New database’ though I had the option to choose Live Database, entered the name of the server, Authentication, further chose the default location (I could have chosen new location to save the repaired SQL database) of the SQL Server, and clicked on the ‘Connect’ button to connect to the server. Next, a dialog box appeared conveying the message that the file has been saved. See the dialog box below: Image – 4
Image – 4
Finally, when I checked the database and I was surprised to find that all data was present. This meant data loss did not occur. Frankly speaking, I was elated to have used the Stellar software that helped me recover all the SQL database objects like triggers, tables, indexes, keys, rules, stored procedures, etc.
As a SQL DBA I must say that Stellar Phoenix SQL Database Repair software is the best way to deal with corruption issues in MSSQL Server. The ease that it provides in addressing the SQL errors and repairing even the severely corrupt SQL database is unmatched as compared to the inbuilt utilities. Moreover, it helps out when the backup restore process fails due to corruption or any other reason, thereby, making recovery of needful SQL database possible. Stellar Phoenix SQL Database Repair fulfills all purposes: repairs the damaged database file, recovers all objects without data loss, and on time. I suggest my peers and other SQL DBAs to use this software for addressing all the issues that they may face with SQL Server in future. In precise, it is a user-friendly, effective, and quality SQL recovery tool.
Administrators can download free demo of the software for testing purpose from:
You may also like:-