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

February 6, 2018 | Views: 4141

Begin Learning Cyber Security for FREE Now!

FREE REGISTRATIONAlready a Member Login Here

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 1

Database ‘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 Server

Many 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 Server

When 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.

SysTools SQL Recovery Software

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.

Conclusion

The 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.

Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Use Cybytes and
Tip the Author!
Join
Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Ready to share your knowledge and expertise?
2 Comments
  1. Kernel for SQL Recovery, an excellent third party software, allows its user to easily repair and recover data from corrupt or damaged MDF/NDF files. Its free trial version is also available to download at- https://www.kerneldatarecovery.com/sql-recovery.html
    The software is compatible with all MS SQL versions, including 2016/2014/2012/2008 R2/2008/2005.

  2. I had restarted my SQL server since the database was not attaching but restarting further complicated the things for me. Now my SQL instance was giving 3 error, even after many hours of troubleshooting  I could not get my server running, so finally I repaired my SQL database with Stellar’s SQL Database Repair. Pretty relieved now! 

Comment on This

You must be logged in to post a comment.

Our Revolution

We believe Cyber Security training should be free, for everyone, FOREVER. Everyone, everywhere, deserves the OPPORTUNITY to learn, begin and grow a career in this fascinating field. Therefore, Cybrary is a free community where people, companies and training come together to give everyone the ability to collaborate in an open source way that is revolutionizing the cyber security educational experience.

Cybrary On The Go

Get the Cybrary app for Android for online and offline viewing of our lessons.

Get it on Google Play
 

Support Cybrary

Donate Here to Get This Month's Donor Badge

 
Skip to toolbar

We recommend always using caution when following any link

Are you sure you want to continue?

Continue
Cancel