Our data recovery customers often ask that we target their databases, and Microsoft SQL Server databases are among the most frequently requested files. In many cases, a SQL Server database is the only important thing on a drive or server — clients don’t need anything else, and they ask our engineers to do anything and everything possible to get these critical files up and running again. As a result, we’ve developed some specialized methods for SQL Server recovery, including key software tools, and we’re always looking for ways to improve our processes.
There are a few factors to consider when planning Microsoft SQL Server data recovery. SQL Server can be very sensitive to any issues such as bad sectors, mis-aligned or stale data. The databases can include large files, which increases the risk associated with recovery — any small issue in the recovered data will affect the entire database. Even with a 100 percent clean recovery, SQL Server may have trouble interpreting the recovered data if the database wasn’t shut down correctly (this problem has subsided somewhat with new revisions in recent versions of the software).
A database that will not attach — or that reports numerous errors when accessing data — is essentially useless to our clients. Our No Data, No Charge policy covers this situation, so we take the necessary steps to provide recovered database files that our clients can reattach and use cleanly. The goal is to allow them to easily reintegrate the data into their systems. SQL Server functions with a variety of front-end applications, however, so we are not able to provide full reintegration support.
Different Configurations and Microsoft SQL Server File Formats
In our laboratory, we work with hundreds of SQL database configurations due to the enormous popularity of the software. We often find that our clients run SQL Server on RAID systems, but it is not uncommon to find the software on standard consumer computers. Common versions of SQL server treated in our laboratories include:
- SQL Server 7 (1998)
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008
- SQL Server 2008 R2
- SQL Server 2012
- SQL Server 2014 (12.0)
- SQL Server 2016 (13.0)
- SQL Server 2017 (14.0)
- SQL Server 2019 (15.0)
- All Future Versions
In the paragraphs below, I’ll go a little further in-depth into our processes for the more difficult cases, but first I’ll attempt to provide a little background info on the actual database files associated with SQL Server.
The critical file types associated with SQL Server are MDF and LDF files (and rarely NDF). Both are required for a functioning database. The MDF file is the primary database file, and the LDF is the log file. The LDF records past transactions that occurred which allows for rolling back actions or recovering from problems since last backup, and it helps to guarantee data consistency. Generally the MDF file is the larger file, but we have seen that sometimes the LDF is larger (this depends on configuration and usage). Database backup files are generally BAK extension.
In most cases, recovered SQL Server database files test perfectly fine, but if the recovery was particularly difficult or there was significant damage to work around, we’ll often see a few issues arise. These can manifest in different ways. The database might fail to attach or show errors upon querying data. In some cases, we’ll simply find bad data within the raw files, at which point we’ll need to dive a little deeper to figure out what’s going wrong.
Depending on the case, we may need to go back to our physical data recovery procedures to try to get a better clone of the original media. If the SQL server database files were located on a RAID, we’ll have to adjust the RAID recovery to get the cleanest possible extraction from all of the drive members. This is where our in-house software tools come into play, as these tools allow us to fill in gaps of data in the SQL Server MDF and LDF files with the correct data whenever it can be found elsewhere (not in the original recovered file).
In the case of a RAID, where multiple drives have failed at different points, it may allow us to use small parts of the normally undesirable “stale” drive to this purpose. In other situations, since we are often able to find different versions of similar database files among the data, we can point our automated tool to several versions and it will piece together the best possible result with the most recent data by replacing bad sectors with the missing data from another file. We will do what it takes to get the best result, and sometimes the process can take days (we cut turnaround times by any means necessary, but the quality of the data recovery is always the top priority.)
Should I Use SQL Server Database Recovery Software or Repair Utilities?
For the IT professional dealing with a damaged SQL Server database, there are plenty of commercial tools advertised as capable alternatives to a professional SQL server database recovery service. I’d recommend against these types of utilities, however, based on the issues that often occur in a fairly typical recovery. Basic software isn’t enough to get a clean recovery, and all of these tools require access to data in order to work. That’s usually a security risk.
Beyond that, these tools will rarely return a database that attaches properly and presents usable data. Stored Procedures and Views can be dumped (these are often required by software systems using the database). Table rows are often slashed in a rough fashion, as the utility does what it needs to do to create a functioning database without taking a real look at the data. There’s simply no ability for logical decision making, which will lead to usability issues.
Of course, some SQL Server database repair tools will function as a last resort, but they’re best suited for use by knowledgeable professionals. A capable data recovery service will give you better, safer, and more secure results. If you are considering data recovery service with SQL Server data, please let us answer your questions – call 800.237.4200.
