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.

Answer 1

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