How to run an SQL script against a MDF file?

Question

I've created a database model with model-first method using Entity Framework 4.0. I then created an sql script using the Generate Database from Model... I've also created an SQL Server Database file in my App_Data folder. How do I now run the SQL file against this MDF file?

I'm using Visual Studio 2010.

Terrenceterrene

Can I get some feedback on that down vote? – Terrenceterrene

Upvote. It is useful and clear. I got a hint to work out a solution from the answer by @jedatu below. – Hypnogenesis

Answer 1

I found a solution, but it's a bit hacky.

I have SQL Server Express (2008 R2). So when generating the database from the model I connect to it and let it build a database there. Then I go to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA. The 10_50 part is because of the version and can be different for you. So in this folder there's a .mdf file named just like the database - .mdf. There's also a _log.ldf file. I copied these to into the App_Data folder of my project.

This works but is very time consuming to generate a new .mdf database for every change in the model. So I do this just before shipping.

If you find a better answer please do share.

Terrenceterrene

That won't be a useful solution, as most developer's are used to creating mdf files in their project folders, not in program files. – Hypnogenesis

Answer 2

I ran into this same problem and here is what worked for me.

When I selected "Generate Database from Model..." I created a new MDF file. This process worked fine and Visual Studio generated the needed SQL Script. However, I did not know how to connect to the same MDF file to run the script.

It turned out to be quite easy.

1. Right-click on the script and choose Connection > Connect

2. The server name and authentication should already be set for the local SQLEXPRESS instance.

3. Click the Options button

4. Click the Additional Connection Parameters tab

5. Paste in the the path to your database file using the following as a guide:

   AttachDBFilename=C:\Path\To\Database\LocalData.mdf;database=LocalData;

6. Click the Connect button

If you still have trouble connecting it may be because there is already an open connection. Check Server Explorer and if connection is open, Right-click and choose Close Connection.

This process will also create a persistent connection as long as the SQL Script remains connected. You will want to close the script or choose Right-click > Connection > Disconnect.

More information can be found at this question: EF4 Generate Database

Bosanquet

What does database=LocalData; have to be replaced with? – Analytic

I can't answer that question definitively. I don't think it really matters what the database name is, but for me, I would always make it match the MDF file name without the extension of course. – Bosanquet

@jedatu, I tried the above, but I get this error: Unable to open the physical file "...\App_Data\Database.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". Cannot attach the file '...\App_Data\Database.mdf' as database 'Database'. (Microsoft SQL Server, Error: 5120) For help, click: go.microsoft.com/… – Analytic

It is perfect except that in step#5 you need to paste the connection string of your mdf file. – Hypnogenesis

alright, this one worked: #22057900 – Clubby

Answer 3

I encountered the same problem, Model First approach in mvc4 using EF5. What I did was create project, add a new model, add entities and associations on the edmx diagram. Right click on diagram and select 'Generate database from model'. This generates the sql file.

Now add a mdf file, say, Database1.mdf (Add > New item > Data > SQL Server Database) in the App_Data folder in solution explorer.

Right click > Open on this mdf file. Now in properties pane you have the connection string. Just copy that as it is.

Revert back to your generated SQL script, right click > Exeecute. A pop-up asks for connection parameters. Make sure you have correct value in Server name (use your PC name, or 127.0.0.1).

Now in options, Click the Additional Connection Parameters tab and paste the connection string copied from properties of mdf file.

This worked perfectly from me and is quite logical. There is no need to directly play around with the SQL Server installation directory.

Hypnogenesis

Answer 4

Great Answers above but I had to do a few more steps in VS2013 but I did resolve with the help here as follows.

1. I created a ADO.NET Model from existing .mdf
2. Right Clicked -> Generate Database from model
3. Right click the .mdf in Solution Explorer and Open
4. In the properties pane copy Connection String to clipboard
5. In the SQLQuery window created by ADO Model click change connection
6. In the new Connect to Server window -> Additional Connection Parameters -> paste connection string from clipboard
7. In Server Explorer right click the Database -> New Query
8. Copy and Paste the Query from ADO Generated script to new Query Window
9. Change USE [yourdatabase.mdf] to USE [longPath] where longPath is the very long path listed in the drop down at top. The easiest way is to start typing and CTRL-SPACE it.
10. Run the Query
11. Save the longPath because you can just paste it into the USE[] on any new Query generated by ADO.

I hope this helps someone - Happy Coding!

Eiffel

It's been a few years since my original question and answer, but I've since found that LINQPad can handle .mdf files. Take a look at this: #4824505 – Terrenceterrene