In this article, we look at resolving an error that you may experience on a Production SQL Server database
Facing an error in the Production server while using SQL Server is a great obstruction. You cannot continue working till you do not do away with the error. So here are a few things you should do every time you come across error in the production server.
- Find out the scope of corruption: The most important thing to do is to find out what is the scale of corruption. If it is large and widespread, you will have to switch hardware, and not waste time on repairing the damage. In-case of large scale corruption, spreading across databases, you can inform all stakeholders, managers and other team members through emails. At this point your only concern should be – where to failover.
- Act depending on the scale of damage: If you realize that the corruption is not very wide spread, you only need to intimate your teammates and manager. However, if it is spread across application, you will have to inform the owners as well as stakeholders.
- What to do if using SAN: The first thing you should do if you are using a storage area network is to alert the team managing the storage, so that they can prevent the corruption from spreading to other files on the same storage.
- Stop deleting backups: If there is anyone responsible for deleting backups after a certain point of time, that person needs to be put out of work right now. You will have to restore the backup without corruption, that might be the one old enough to be deleted. If you d not have that, you will probably have no way out to restore data.
- Review the last CHECKDB scan: A CHECKDB scan can tell you when was the last safe scan done, meaning when was the last time you got a corruption free scan. Once you know when was that, you need to find corruption free Full and Differential backups to be able to restore data. This will be your alternate recovery path.
- Find an easy fix: Once you have the details of the last CHECKDB scan, you should be able to figure out an easy fix like – dropping the index, recreating the index, etc. But do not run another scan right now.
- Allocate work: If you have found a solution, you need to create teams and allocate the repair work to those teams. One team should restore safe backups, another can engage with Microsoft support, one team can perform a CHECKDB scan etc. If it does not work, opt for a specialized fix mdf tool.
- Continue working and making updates: Do not panic or give up, you are now very close to solving the problem and repairing the database. Once the plan of action is ready, the work has been allocated, continue working and making progress. Keep updating your status about the work progress, make sure everyone in the team is well informed and there is no miscommunication happening. If you do all things right, with a calm mind, you will successfully be able to solve the problem.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix access and sql recovery software products. For more information visit www.datanumen.com