i was on a client-of-a-client-of-a-client-site yesterday, parachuted in to address some issues with a .NET thick client application i hadn't seen before. the thing was a VB WinForms client running on 2000 and XP, making (predominantly) stored procedure calls to a SQL Server 2000 back end. Two versions of the client had been deployed:

• a older version, running on five client machines in .NET 1.1 using the Crystal "9" (i believe) bundled with VS2003
• a newer version, running on a single client machine in .NET 2.0 using Crystal 10 (bundled with VS 2005) and including some new functionality.

My problem (one of my problems, for there were many) was how to establish a dev environment on my XPSP2 notebook given that I had only SQL Server Express 2005. After a quick survey of the web, it seemed as though restoring SQL Server 2000 backups to SQL Server Express 2005 should be possible, but that people out there had run into a dauntingly wide range of disturbingly un-informative error messages while attempting to do so.

So, the first big find that I'm really happy with is Microsoft's SQL Server Management Studio Express (currently available in CTP release). It provides (in my opinion) a nicer UI for dev/admin-y work than Enterprise Manager or Query Analyser, and is free, as far as I can tell. For a CTP, it was quite robust. I didn't run into any crashes or odd behaviours other than in the area of restoring backups, which brings me back to our topic.

Having used the client's SQL Server's licensed Enterprise Manager, on their network, to perform a full backup of the database in question, and transferred it to my laptop using a USB key, i tried using SSMSE to restore it directly:

 

but i got this error:

 

Or, for those about to google: System.Data.SqlClient: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\DBName_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

anyway, the fix i found was to use mark russinovich of sysinternals' junction tool to set up an NTFS reparse point (kind of like a hard link in Unix) which causes requests to the MSSQL\Data folder the SqlClient was looking in on behalf of SMO above to be directed to the same location as requests to the folder the actual data turned out to be stored in (which i determined using filemon) by running this command-line:

 

C:\>junction "C:\program files\Microsoft SQL Server\MSSQL"
   "C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL"
Junction v1.03 - Win2K junction creator and reparse point viewer
Copyright (C) 2000-2002 Mark Russinovich
Systems Internals - http://www.sysinternals.com
Created: C:\program files\Microsoft SQL Server\MSSQL
Targetted at: C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL

after which, i was able to restore the database successfully and get some actual work done. hooray for kludgey hacks to deal with CTPs, semi-supported scenarios and high-pressure client environments!