Attaching Database - ASP.NET

cotton31 · 10 Jul 2010 at 16:56

Hi,

I am developing my first web app in ASP.NET using Visual Studio 2010.

I need to attach an existing database I have which is sitting in an instance of SQL Server 2008. How would I do this? I have managed to create a DB using VS2010's built in instance of SQL Server, but I need to attach the database in my full install.

Thanks,

GravyMonster · 10 Jul 2010 at 20:48

Right click the App_Data folder in your project, Add Existing Item and then navigate to the MDF file of your database - normally in Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

cotton31 · 13 Jul 2010 at 21:35

Cheers Spunkey,

I've found my MDF file, I get an error saying that the file is in use by another program. The only program I have open is Visual Studio.

Are there any SQL services/programs which may be stopping me from doing this?

Running Windows 7 x64 with VS2010 (32bit) and SQL Server 2008 (64bit), does the version mismatch make any difference?

GravyMonster · 13 Jul 2010 at 21:45

The database will be being used by one of the SQL Server services - I forget which one exactly.

Basically if you go into SQL management, right click your server and hit Stop (edit - assuming this is your personal testing server here - DON'T do this if it's a public server!). Then try and add the MDF to the VS project like I mentioned before. Hopefully the lock should have been released and it'll work.

Don't forget to restart your SQL server though

Mickey · 13 Jul 2010 at 22:14

You'd be much better off just connecting to the database server you already have running.

cotton31 · 13 Jul 2010 at 22:24

@Spunkey - does the DB need to be offline then for my app to access it? No it's my own testing server, just learning at the minute

@Mickey - How would I do that? That sounds as though it might be what I need to do, as I think what I'm doing above is essentially copying the DB into my app? Correct me if I'm wrong, as I say I'm learning...

Mickey · 13 Jul 2010 at 23:35

Well it depends how you're doing it.

As you're just starting out, I'll assume you're using the drag & drop/wizardy features - get the server explorer up (defaults snapped to the left IIRC), right click on data connections and add connection. Put in the server address/username/password then select the database from the dropdown. Once you've done this the connection should appear in any wizard you use.

GravyMonster · 14 Jul 2010 at 08:48

cotton31 said:
@Spunkey - does the DB need to be offline then for my app to access it? No it's my own testing server, just learning at the minute

No, it needs to be offline to you to add the MDF to the project. All your doing is effectively taking a copy out of your database and placing it in your project. From that point on the two would be completely separate.

HOWEVER as Mickey says, it's a lot easier to just keep the data in SQL and connect to it in VS. I assumed you had some need to store the DB in your project - eg for taking it with you around different machines.