MSSQL, LocalDB, and upgrading to SQL 2014

This is a fairly rudimentary post about inner workings of SQL, but since I found it so damn hard to find earlier, I thought I'd share my learnings.

I started a new Web Forms project with user control in Visual Studio 2013, and had SQL 2008 installed on my local machine. The db procedure tries first to create an .SQLEXPRESS database, and if SQL Express is not installed (though I'm not totally clear on this point as I later had it installed but this next statement didn't happen), it will create a LocalDB type database instead. Here's the important bit; in SQL 2008, the version of LocalDB installed is 11.0. Here comes the trouble...

I had most of the project ready to go and wanted to upload it to our test server at work here; it's our own machine, perfectly capable, but it needed SQL Server installed. Not wanting to lean on others, I went to Microsoft and downloaded the latest version of SQL Express 2014 with tools and installed it on the server through Remote Desktop. I uploaded my project and tried to use the registration but ran into "error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details."

Let me tell you now, Application event log didn't help me, so I figured it was the difference in SQL versions (spoiler alert: yes).

I tried reading through the SQL Server documentation, and an SQL In Depth book for some glimmer of what might have been happening; at the time I didn't understand much of the inner workings, and thought that SQL couldn't find my .mdf database file.

(I will note here too, that originally the .mdf file in my App_Data folder didn't copy to the server properly, because it was locked to Visual Studio. After closing Vis, I recopied the whole site to ensure everything was in its place, but the same error persisted.)

 I asked Pete for some help and outlined everything that I had tried up to this point (an important trait of a software dev!). He tested out the issue, looked at the Web.config Entityframework connection string and various other application pool settings; everything seemed to be in order. So he opened up the server's SQL database manager to try to recreate the database there. I had previously found a StackOverflow (Dear Joel Spolski, Thank you. Sincerely, everyone) thread that said the localdb database name had changed in 2014 to (LocalDB)/MSSQLLocalDB, the mssqllocaldb abomination part being the brain child of some bored Microsoft employee. Felt like we were getting close!

Pete used the GUI to "Attach..." the .mdf file to the server's database. Everything looked promising, until it didn't. We received an error that said something along the lines of "... unable to upgrade the database because the file is locked." Got a locked file I feel bad for you son... Yeah it wasn't locked. It wasn't even opened. It didn't even throw an error when copy/paste-ing it around the server. The permissions didn't cause an issue either!

After fighting with the "locked" part of that message, we looked to the "upgrade" part instead, and found something to go on. SQL Server 2014's localdb is now version 12.0, and was the source of the issue!

After searching for others who had this issue, I found a StackOverflow thread that illuminated many workings of SQL Server. LocalDB is a simple, blank instance of a database. At run-time, your App_Data .mdf file is loaded into this database for interaction. Localdb lives in your SQL installation folder, whereas the data that populates it lives in your site's App_Data folder: News to me!

So I followed the (at this time) one answer provided, and used command prompt to create a new database:

sqllocaldb create "v12.0" 12.0

This created a new database named v12.0 (following the v11.0 convention), and it is version 12.0

After much fighting, this continues to be an issue. Will update when the solution comes around. Until then, I will reiterate Luigi's middle finger.