Overcoming issues attaching SQL Server database files

I’m doing some reporting work against a SQL Server database, and received some files (a .mdf and .ldf file) for evaluation. Starting from scratch with no version of SQL Server installed, the first thing to confirm was: exactly what SQL Server database was used to create these files? There’s no straight-forward way to determine version. It seems the best way is to inspect the .mdf file with a hex editor, go to the location 0x12064, read the 2 bytes there, and convert to decimal.

Hex values

The value is readout as 0295 in hex, so plugging that into the calculator gives the following output:

Hex to decimal conversion using the Windows calculator

So according to this version-listing table, 661 corresponds to SQL Server 2008 R2. However there is some inconsistency about that. Some sites show 661 as 2008, and 665 as 2008 R2.

With that info, I opted to locate and install SQL Server 2008 R2 Express on my Windows 8.1 machine. It’s necessary to install the SP2 version to avoid compatibility problems. Once installed, using Microsoft SQL Server Management Studio 2014 (SSMS), I found it was not easy to attach the database. Firstly I had the issue of:

Unable to open the physical file “C:\temp\db.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”. (Microsoft SQL Server, Error: 5120)

which is due to lack of permissions. Oddly, even when I set the mdf file permissions to include MS SQL Server, it still complained with the above message. So I opted for the safest option, i.e. move the folder containing the mdf & ldf files to the DATA folder, and that solved the issue.

The next problem arose regarding the validity of the ldf file. The following error was seen upon attaching the mdf via the GUI (Databases > Attach…):

The log scan number (9884:437:2) passed to log scan in database ‘xxx’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. Could not open new database ‘xxx’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)

This was odd, since the database files were copied safety (after the original server was stopped). Restoring from a backup was not possible. The solution was to delete the bad ldf file, and perform a manual attach request by opening the query window and using the following script.

CREATE DATABASE Manager ON
( FILENAME = N'C:\temp\db.mdf')
FOR ATTACH_REBUILD_LOG;
GO

This created a new log file db_log.LDF that I copied over to the DATA folder.

It would be interesting to try reversing the above, and installing the latest SQL Server 2014 Express makes the process any smoother to attach and use older databases. It would be ideal if SSMS offered to automate the solutions (i.e. offer to move the files or set permissions, or to create a new log) to solve these kind of problems.

BTW here’s a nice piece of SQL that will show the connected SQL Server version (once it’s attached :-):

Select @@version

Leave a Reply

Your email address will not be published. Required fields are marked *