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 4 and 3 and type the answer here:
  • Post
  • Beth,

    Please why not use sql server?

  • More goodness from Beth Massi: I've been getting asked a lot lately about how to easily attach a local

  • Hi Tony,

    What do you mean exactly? MDF files are SQL Server database files. If you are referring to using SQLCmd instead of SSMS then it's because SQLCmd is easier when you don't have the log file (LDF) available.

    Cheers,

    -B

  • Beth I mean sql express, in the real working (telecommunications) is not installed in my company.  Only sql server is installed on our dev servers.  

    I think you should target more real world examples.

  • Hi Tony,

    Umm... that's what this post is all about. SQL Express and SQL Server work the same way when you want to attach database files. SQL Express allows you to have user connections and work with local database files. This post shows you how to take all the samples where I distribute the databases, and instead attach them to your instances -- whether that is sqlexpress or server (I mention "developer edition or higher" in the second paragraph). Sorry I wasn't clear, I updated the wording in the post.

    And SQL Express is very real world. It's a great FREE database for small-medium businesses. And upgrading is just a matter of attaching the database to SQL Server like I showed above.

    HTH,

    -B

  • I only find it necessary to move my express db files on Windows Millenium Edition 2008 (aka Vista), otherwise I only use express in website development and attach them in place.

    The security and deployment defects of Vista are my #1 complaint about this half baked wad of recursive dough eating dough.

  • Hi Monkey,

    I'm running as admin on Vista and user instances connect just fine. If you're doing web development then there are some issues when the ASPNET user is auto-attaching to the user instance. If ASPNET does not have write permissions on the database files, the connection fails. (Also see this post for a specific Vista issue with an easy fix: http://blogs.msdn.com/joestagner/archive/2008/03/12/sql-express-failed-generate-a-user-instance.aspx)

    However I think attached databases are easier to work with when developing. For attached databases I wrote about how to enable remote connections on Vista here:

    http://blogs.msdn.com/bethmassi/archive/2008/09/17/enabling-remote-sql-express-2008-network-connections-on-vista.aspx

    I do find Vista to be a little more pesky when developing on it because of it's security model but in general it's been fine with respect to using Visual Studio 2008 and SQL Express.

    Cheers,

    -B

  • My gripe about Windows Millenium Edition 2008: The Sequel has to do with the deployment of application databases and the broken linkage mechanism. This isn't a problem for Cassini website development, but if you try to deploy a windows solution that uses 32 & 64 bit versions of SQL Express, Vista will fail to locate & link your database properly.

    It is better the be the balmer than the balmy, apparently the most when they are handing out pay; hence the world economy blue screen.

  • Thanks for the feedback Monkey. Though I'm not sure what problem you're running into so it's hard for me to suggest a solution. It sounds like IIS security related to the ASPNET user in which there is a weird fix that requires you to delete the SQLEXPRESS temporary directory.

    Do you have a link handy that explains the issue?

    TIA,

    -B

  • Do you know how to change the database name setting in the mdf? Case: copying & renaming a database file to be attached with a new name. This was not previously a problem, either there was no signature or the attach event updated the reference, but now it throws an error.

  • Hi Beth - love the videos.

    I'm trying to attach the OMS database and I get the error:

    "File activation failure. The physical file name "C:\Users\bethma\Documents\Visual Studio 2005\Projects\FormsOverData\OrderManager2\OrderManager\OMS_log.ldf" may be incorrect.

    The log cannot be rebuilt when the primary file is read-only.

    Msg 1813, Level 16m State 2, Server...

    Could not open new database 'BethMassi1OMS'. Create Database is aborted."

    I checked the MDF file and it is not read only.

    Can you help me with this?

  • hallo ....

    how add automatic attach database, to sqlexpress in setupdeployment in vb.net

  • Hi Walter,

    You'll need to make sure the name of the dataabse is OMS.mdf for those samples when you attach it using sqlcmd.

    HTH,

    -B

  • Thanks Beth.

    Just what I needed.

    Bill

  • how can i attach a file.mdf file from sqlexpress back to visual studio2008. (i attached file.mdf to sql express from visual studio to add the schemas needed for user rolls, but now i can't get it back to visual studio)

Page 1 of 3 (42 items) 123