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 8 and 4 and type the answer here:
  • Post
  • can you please tell me the attaching and detaching of mdf files by sqlcmd in sqlexpress using a batch files

  • Beth,

    I got this after doing the last "GO"

    Msg 948, Level 20, State 1, Server BUG-FUJITSU\SQLEXPRESS, Line 1

    The database 'OMS' cannot be opened because it is version 655. This server suppo

    rts version 612 and earlier. A downgrade path is not supported.

    Msg 1813, Level 16, State 2, Server BUG-FUJITSU\SQLEXPRESS, Line 1

    Could not open new database 'OMS'. CREATE DATABASE is aborted.

    Please help.

    Thank you.

  • Hi Paul,

    This message means you are trying to attach a SQL 2008 database to a SQL 2005 instance. The version of the OMS database you have must be the 2008 version. You can install SQL 2008 Express for free or you can download the 2005 version of the OMS database from this 2005 sample here:

    http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=vbvideosdata&DownloadId=69

    HTH,

    -B

  • Hello Beth, first of all let me congrat you on your videos... are making my learning a lot less painful...

    Now my question is...

    I made a SQL database connection on my application... Now I want to deploy it and make it a multi-user dababase... I want to have jsut one instance of the database where all people connect... How can I configure the connection string to match the target computer ? I managed to get SQL to be shared by TCP/IP ... how can i make it install and i dont know pop up a connection menu...that will configure all objects in the database..

    Thanks, Pedro

  • Hi Pedro,

    Once you have the database running on the remote machine you change the connection string in your application to point to the remote database. Make sure you store connections strings in your application Settings (app.config) file and then you can change it without having to recompile the application. If you're using the designers to model your database then they will create a connection sting in the app.config.

    For more information :

    http://msdn.microsoft.com/en-us/library/a65txexh.aspx

    HTH,

    -B

  • Beth , please i need to install sql express and attach the mdf file to it automatically without installing management studio  i know you can help me its very urgent . thank you a lottttttt in advance

  • Hi Georges,

    I'm not sure why you can't use the sqlcmd above. More info is here:

    http://msdn.microsoft.com/en-us/library/ms165673.aspx

    If you have a single-user app, you may also want to look into user instances, which will auto attach when your application runs:

    http://msdn.microsoft.com/en-us/library/ms254504.aspx

    HTH,

    -B

  • Dear beth everything is working fine but still one issue , when you install sql server management studio ,  before it launches it shows you the connect to server dialog box , from where you can see the server name example :george/sqlexpress , how can i get the name of the computer automatically , if i want to install sqlexpress and attach a database to it automatically and on a different computer . Please Helpppp thank you in advance for your support

  • Beth I have created a batch file with the following script :

    sqlcmd -S .\SQLEXPRESS -george -i sqlscript\createattach.sql

    pause

    that runs the createattach sql script that is :

    USE [master]

    GO

    CREATE DATABASE [Data2] ON

    ( FILENAME = N'C:\Program Files\week Publishers\Sader Civil Cassation\Cassation Data\Data\Data2.mdf' ),

    ( FILENAME = N'C:\Program Files\week Publishers\Sader Civil Cassation\Cassation Data\Data\Data2_log.ldf' )

    FOR ATTACH

    GO

    if exists (select name from master.sys.databases sd where name = N'Data2' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )

    GO

    USE [master]

    GO

    CREATE LOGIN [sadd] WITH PASSWORD=N'1p@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    EXEC master..sp_addsrvrolemember @loginame = N'sader', @rolename = N'sysadmin'

    GO

    USE [master]

    GO

    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

    GO

    what shall i do after ? help

  • Hi georgenaffah,

    As I mention in the article above:

    "You can use the .\ (dot-backslash) syntax to indicate the local server default instance"

    Regarding your script, I don't know what you're trying to do afterwards. You should check out the reference I mention above: http://msdn.microsoft.com/en-us/library/ms165673.aspx

  • Dear Beth

    all what i want to do is to install sql server express 2005 on a new machine then use the script to attach the database automatically , help me please locate where to write the .\ in order to create the server instance automatically . thank you in advance for all your support .

  • Beautiful Help and works right On.

    I should mention that

    EXEC sp_detach_db 'AdventureWorks', 'true';

    GO

    is the script for Detach

    as most of the people will wnt to know

    cause most of people wanted to know that how to get it back in VS.

    I used the attach for installing the roles and membership onto my existing database

    then used detach.

    prior to reading this blog

    I had to install SSME

    only for attach and detach process.

    So thanks a Lot again Beth

  • I am trying to select data from two diferent databases (or insert from one to another) using sql server 2005 express in vb6 code.

    for access database it works fine:

       Dim ds As dynaset

       Dim sql As String

       Dim db As Database

       Set db = OpenDatabase("c:\app\mydb1")

       sql = "select A.fld1,B.fld1"

       sql = sql & " FROM [;database=c:\myapp\mydb1.mdb].fld1 as A, "

       sql = sql & " [;database=c:\myapp\mydb1.mdb].fld1 as B "

       Set ds = db.CreateDynaset(sql)

    but for sql express I don't know how to specify connect string. I am using the following connection

    to sql server express files.

    Thank you,

    Nenad

  • sorry, correction:

       sql = "select A.fld1,B.fld1"

       sql = sql & " FROM [;database=c:\myapp\mydb1.mdb].fld1 as A, "

       sql = sql & " [;database=c:\myapp\mydb2.mdb].fld1 as B "

    Nenad

  • there is something wrong with my pc while connecting datatbase with visual studio 2008. what is the problem in other pc its going to connct but in other one it is not going to connect it

Page 2 of 3 (42 items) 123