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.

Answer 1

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

Answer 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