Copying .mdf file from app_data folder to default localhost folder

Question

My friend gave me a database file: record.mdf. I copied that .mdf file to my app_data folder and I can access it.

However, the connection string contains absolute path:

AttachDbFilename="C:\Users\Dell\Documents\Visual Studio 2010\Projects\WebApplication2\WebApplication2\App_Data\record.mdf"

But I want it to connect using:

Data Source=localhost\SQLEXPRESS;

How do I copy .mdf file to SQL Server's local folder, so that the connection string does not use an absolute path to the database?

I am using Visual Studio 2010. I do not have SQL Server Management Studio.

score 6 · Accepted Answer · answered Jun 09 '12 at 08:43

Step 1: you need to find out your SQL Server's data directory. This will be something like

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

by default (for SQL Server 2008 R2 Express) - it might be slightly different in your case, depending on how you installed your SQL Server Express (and which version you have).

Step 2: copy that record.mdf file to that directory

Step 3: attach it to your SQL Server Express instance - using sqlcmd if you don't have Mgmt Studio at hand:

c:\> sqlcmd -S .\SQLExpress

Then at the sqlcmd prompt, type in:

USE [master]
GO
CREATE DATABASE record ON 
   (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\record.mdf' )
FOR ATTACH_REBUILD_LOG;
GO

This will attach the .mdf file as your new "logical" database record to your SQL Server Express instance, and it will rebuild the missing transaction log file (.ldf) in the process.

From now on, you can use

server=.\SQLEXPRESS;Database=record;Integrated Security=SSPI;

as your connection string to connect to your database

score 2 · Answer 2 · answered Jun 09 '12 at 08:33

2

Rather than copying it to the SQL server local folder, you can access it from the App_Data directory using |DataDirectory|\record.mdf

Documentation: http://msdn.microsoft.com/en-us/library/ms247257(v=vs.80).aspx

answered Jun 09 '12 at 08:33

Richard

29,854
11
77
120

That's a rather kludgy and messy thing - it might work - at times. But it's definitely not the way to go - you should attach your .mdf to your server instance - much easier to work with – marc_s Jun 09 '12 at 08:38
Why is this kludgy? It's the standard way used in the Microsoft samples, and is automatically used in Entity Framework connections. If you only need to access the DB from within a single ASP.NET application, it's the way to go because you don't need to touch the SQL server at all (therefore suitable for deployment on a host you don't have Admin or SA access to). If you need to access the same DB from management studio (OP said he didn't) or multiple projects then attaching has advantages. – Richard Jun 09 '12 at 08:59
First of all - the whole User INstance story is already been deprecated and basically vanquished from SQL Server 2012. Seems like it was a really bad idea after all - and I agree. Also: with this scenario, you get into a lot of messy situations, where Visual Studio copies around the .mdf file and we had tons of questions here on Stackoverflow where devs are inserting data into their .mdf (in their local output folder), but after their app has run, the data is gone (since the .mdf has been copied & used and then disposed of). Messy without end .... – marc_s Jun 09 '12 at 09:02
And btw: just because some team inside Microsoft is doing it doesn't mean it's a good idea to be copied ..... just look at the ASP.NET membership database with clustered indices on GUID columns - horribly bad..... – marc_s Jun 09 '12 at 09:03
I can't comment on the data files being copied around, but users instances was definitely not vanquished from 2012 - it was turned into [LocalDB](http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx) – Richard Jun 09 '12 at 09:19
@marc_s: Is there a way to attach .mdf to server instance without using Management Studio? – coolscitist Jun 09 '12 at 09:24
@marc_s "we had tons of questions here on Stackoverflow where devs are inserting data into their .mdf (in their local output folder), but after their app has run, the data is gone (since the .mdf has been copied & used and then disposed of). Messy without end". I did not understand the problem with using .mdf file within app_data. Did you mean that new data is deleted? – coolscitist Jun 10 '12 at 02:26
@Robik: no - when the app runs, VS copies the `.mdf` to a separate directory, and all operations are made on that `.mdf` (not the one in the `app_data` folder). When the app finishes, that copied `.mdf` is deleted, and the original `.mdf` in the `app_data` folder of course doesn't have any of the changes made - since they weren't made to that `.mdf` file. THat's causing a lot of confusion and questions. – marc_s Jun 10 '12 at 07:23
@marc_s: I "registered" to an asp.net site. The registration info is stored in aspnetdb.mdf. Even when I closed visual studio, stopped sql server etc... I can still login to the site. Doesn't it mean that the data was stored in the original database "aspnetdb.mdf"? All in all, I still don't understand the condition when the problem will arise. – coolscitist Jun 10 '12 at 13:36

Copying .mdf file from app_data folder to default localhost folder

2 Answers2

Linked