Don’ts and Do's to Prevent Database Corruption in SQL Server

Database corruption is the most common problem encountered by the DBAs in their day to day life. Often users trying to repair a corrupt SQL Server database using DBCC repair option (REPAIR_ALLOW_DATA_LOSS), which can cause some unexpected data loss or make it completely inaccessible. So, it is always advised to be aware of whatever you are going to try in your database, Especially when the database is inaccessible.Here is a common scenario of such issues:Case 1“Today I stuck with one of my databases with following error message:Msg 945, Level 14, State 2, Line 1Database 'customer' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.I blindly detached the database by setting the database offline and tried to reattach it and again I got a new error msg 5123. Now I don't know how do I get my database back?”Case 2“We had ransomware attack and all .mdf / .ndf files changed to .wallet extensions. Unfortunately, SQL backup file also gets encrypted which was kept at the same place in MSSQL 2014. Now, No idea how to decrypt and recover the database from database files.”As everybody knows problems always arise without any warning and to fix these issues, an appropriate corrective measure needs to be performed. Therefore, whenever there is a database corruption, just follow some Don’ts and Do’s discussed in this blog to manage database health.

Don’ts to Safeguard your Database

Don’t Restart SQL Server We all have a bad habit that when nothing works properly we restart it.It will not be the same with inaccessible SQL database. There are very less chances that corrupted database will become accessible when SQL Server restart performed. Restart may end up with other issues like database permanently inaccessible or objects gets corrupted.Avoid to Upgrade ServerMany times, users upgrade their SQL Server version in a way to remove the corruption of a database. However, doing this is not an accurate solution. As upgrading, the version of server may create a great hurdle to remove the corruption issue.Don’t Run Repair Option Blindly Although DBCC CHECKDB option eventually repair your database, if all the other efforts fail. But the repair option of this command may also lead you to the data loss. In some cases without knowing what this option actually do, many DBA end up by losing all their data. So, be alert when going to use repair option, as you need to have proper knowledge of  its syntax.Don’t Shut Down SQL ServerWhen the corruption in the database is identified then, almost everyone tries to shut down their SQL Server. However, it is not recommended to shut down sql server as items in the database may get lost or unable to come back. Moreover, one can render their database in an inconsistent state if database recovery is ongoing.

Do’s to Prevent Data Loss in SQL Server

The above mentioned operations can be the cause of data loss. In such scenario, one can go with an automated solution like SysTools SQL Recovery, to prevent data loss in SQL server.

Why SysTools SQL Recovery?

This is a trusted utility in the field of database recovery. Which is tested and reviewed by SQL Server MVPs on their severally corrupted databases. In comparison to other database recovery utilities available in the market, This utility provides you incomparable features that a SQL Server user can use in their day to day life.

What Makes SysTools SQL Recovery Incomparable?

The software not only recovers database from .mdf/.ndf files, It also provides you the option to migrate the recovered database from one Server to another and you can also generate .sql script files of all the database objects like Tables, Stored procedures, functions, Views etc. while exporting the database.

Other Highlighted features of this SQL Database Recovery tool:

• Repair MDF and NDF files without any file size limitations
• Move Table’s from one database to another
• Recover deleted Records from SQL Server Tables
• Recover all database objects like Tables, Index, Views, Stored Procedures etc
• Recover data from ransomware affected MDF and NDF files
• Support to Repair .mdf / .ndf files of SQL Server 2016 & all below versions
• Export database objects into SQL Server Database or .SQL script file. Read here for more features

Upcoming features Of SysTools SQL Recovery

On the conversation with SysTools Chat support I got to hear that the developer team working on several upcoming useful features, which will be available in its new version on the first quarter of 2018, I.e:

• Export database into CSV format
• Support to repair corrupted .mdf /.ndf file of SQL Server 2017
• Grid Search for a specific column in multiple tables.

ConclusionThe database inaccessibility can be happened at any time with  SQL Server database. Repairing such failed database with a wrong step may lead to data loss. The article discusses some important don’ts that are used to repair database. You should have proper knowledge of what you are going to act on your corrupted database. In do’s we recommended a robust solution to perform complete database objects recovery. Also this SQL Recovery Tool facilitates you to perform database to database migration between SQL Server.