Run SQL script on MDF file in App_Data

Question

I have the following setup:

Development: My own computer using an MDF file with a connectionstring which refers to my app_data folder.
Production: An online server with its own SQL database

When I want to create a new table / fields in my system, I create it in deployment and then run those scripts on production without data.

Now, I am in the opposite situation: I've generated scripts from my production server, and wants to run those towards my local MDF file.

How do I do that? All the answers i've seen so far doesn't help me.

I've tried the following:

I've tried to run the whole SQL script in the "Run query" in Visual Studio, but I get a "The Set SQL construct or statement is not supported." error.

I guess I have to open the MDF file somehow in Microsoft SQL Management Studio - but I can't really find a way to get it all to work.

EDIT:

After half a year, I've been using the method suggested as the answer again and again. It is by far the best solution. No more MDF files in the project!

I would strongly suggest anyone reading this to start having your databases in your local database.

That "attach `.mdf` file from disk" approach is really not a good idea. Why aren't you just using your SQL Server instance on your development machine - same as in production? Create the database **on the server instance**, use the logical database name instead of fiddling around with `.mdf` files in the file system - and it makes it **just that much easier** to run scripts against it, back it up etc. — marc_s, Jul 14 '12 at 20:46
I guess that's probably the best solution. I will definetely look into that. Is the other way possible? — Lars Holdgaard, Jul 14 '12 at 20:47
Don't know - I gave up on that "attach .mdf" approach too quickly to ever get to the point where I had to execute a `.sql` script against that file.... — marc_s, Jul 14 '12 at 20:48
I ended up implementing your solution instead.. Thanks! Please post an Answer, so anyone searching for this can get that advise easily :) — Lars Holdgaard, Jul 15 '12 at 20:28

score 2 · Accepted Answer · answered Jul 15 '12 at 20:35

In my opinion, the whole AttachDbFileName= approach is flawed. Since that .mdf file is not attached to your SQL Server instance that's already installed, there's many things you cannot do:

you cannot use BACKUP to back up your database
you cannot easily execute .sql scripts against your database
and quite a few more things

The only really viable solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. YourDatabase)
connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
```
Data Source=.\\SQLEXPRESS;Database=YourDatabase;Integrated Security=True
```
and everything else is exactly the same as before...

You have to take the **User Instance = true** parameter out of your connection string. — Jaroslav Svestka, Mar 03 '16 at 00:37

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Connecting to an MDF in Visual Studio:

https://stackoverflow.com/a/173221/188926

You can then open in SQL Server Object Explorer and run queries.

edited May 23 '17 at 11:50

Community

1
1

answered Jun 05 '14 at 09:34

Dunc

18,404
6
86
103

Run SQL script on MDF file in App_Data

2 Answers2