Attaching a Local SQL Server Database Using sqlcmd

Attaching a Local SQL Server Database Using sqlcmd

Rate This
  • Comments 42

I've been getting asked a lot lately about how to easily attach a local SQL Server/Express database file (.MDF file) to an instance of SQL Server (any edition). In most of my samples I distribute local SQL Server databases and use user instance connection strings so that if you are running Visual Studio with SQL Express then hitting F5 will auto-attach SQL Express to the database when it runs. This will automatically create the log file (.LDF) as well for you. This "just works" approach works well for people who are using the default install of Visual Studio which installs a SQL Express instance.

However, many developers (including myself) have SQL Server developer edition or higher already installed on our machines or connect to remote development machines with higher editions on them. We want to attach the database file to our instance of SQL Server (by the way you can also attach databases to SQL Express). You can do this easily with sqlcmd as outlined in the MSDN library. Here are the steps:

1. First copy the local database MDF file to the same location as your other databases. This is usually the folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (or if you are using SQL Express it's probably C:\Program Files\Microsoft SQL Server\MSSQL.SQLEXPRESS\MSSQL\Data )

2. Next open a command prompt and connect to your server using sqlcmd using the syntax -S ServerName\InstanceName. You can use the .\ (dot-backslash) syntax to indicate the local server default instance. In the case of SQL Express the instance is called sqlexpress. So to connect to a local SQL Express database:

C:\>sqlcmd -S .\SQLEXPRESS

Or if we were connecting to our local SQL Server default instance:

C:\>sqlcmd -S .\

Or a named instance (SQLInstance) on another machine (RemoeMachine) it would be:

C:\>sqlcmd -S RemoteMachine\SQLInstance

3. Next you need to switch to the master database (<enter> after each line below):

1> USE Master
2> GO

4. Then you need to enter the following command to indicate the new database name to create and the location of the MDF file. This will attach the database and automatically create a new log file for you (<enter> after each line below):

1> CREATE DATABASE databaseName ON
2> (FILENAME= N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MDFFileName.MDF')
3> FOR ATTACH;
4> GO

Where databaseName is the name of the database and MDFFileName is the physical name of the MDF file. Notice that we're omitting the log file location so it will create one automatically for you. Now you can change your connection strings in your applications to use this attached database in the application settings. For instance many of my samples use a local database called "OMS.MDF". Instead of the user instance connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OMS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

You can change the connection string in all those samples to:

Data Source=.\SQLEXPRESS;Initial Catalog=OMS;Integrated Security=True

It also becomes a lot easier to modify an attached database from the Visual Studio server explorer or from SQL Server Management Studio. Hope that clears it up for folks.

Enjoy!

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • I am trying to Connect sql server database mdf file without installing sql server on client machine. I want my clients to be able to use the project s/w even if they dont have SQL Server or SQL Express installed on their machines.

    I dont know if it can be done or not.

    Thanks in Advance for any Suggestion

  • Hi Tausif,

    You will need to install SQL Express on the machine that is hosting the database. Attach the mdf file to the database instance and then you can connect to it from other client machines on the network.

    -B

  • If you attach a SQL Express data file to an instance of SQL Server, does it then become a SQL Server database?  If so, why do you have to have "SQLEXPRESS" in the connection string, as in your example?

  • Hi Roger,

    The database instance name is SQLEXPRESS when you install SQL Server Express edition. Just because you attach a database to it doesn't change the instance name. Think of this name like your computer name.

    HTH,

    -B

  • Thank you for the answer, but maybe I need to re-word my question.  If I have a SQL Express data file and I attach it to an instance of SQL Server, does it then become a SQL Server database just like any other SQL Server database?

  • Dear Beth

    I have a microsoft word 2007 file that I want to extract its contents and send them to SQL Server 2008 Express Database can you help me with that ? I mean I m sure that you can  it is very easy and dummy for you , please help , give me instructions step by step or if there is a video available  thank you for your time and all the good videos you already made

  • Dear Beth

    I have a microsoft word 2007 file that I want to extract its contents and send them to SQL Server 2008 Express Database can you help me with that ? I mean I m sure that you can  it is very easy and dummy for you , please help , give me instructions step by step or if there is a video available  thank you for your time and all the good videos you already made

  • please make me correct .what's the wrong with following stuff.

    =============================================================================

    1> CREATE DATABASE OMS ON

    2> (FILENAME= N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\O

    MS.dbf')

    3> for attach;

    4> go

    Msg 5120, Level 16, State 101, Server MY-PC\SQLEXPRESS, Line 1

    Unable to open the physical file "C:\Program Files (x86)\Microsoft SQL Server\MS

    SQL.1\MSSQL\Data\OMS.dbf". Operating system error 2: "2(The system cannot find t

    he file specified.)".

    1>

  • There is a free tool "SQLS*Plus" (on http://www.memfix.com ) which is like SQL*Plus for SQL Server.

    Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, etc

  • good thanks

  • Msg 5120, Level 16, State 101, Line 1

    Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Annex.MDF". Operating system error 5: "5(Access is denied.)".

  • Thanks a million for this simple article. I am a noob,

    I disconnected my default DB and Management Studio would then not open..  

    I have spent days on forums being directed to so many threads telling me to restart services in different modes followed by another dozen or so instructions that were way over my head.... and all i needed to do was Re Attach it with SQLCMD....

Page 3 of 3 (42 items) 123