Attaching a Local SQL Server Database Using sqlcmd

บทความ
12/18/2008

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!

Comments

Anonymous
December 18, 2008
Beth,Please why not use sql server?
Anonymous
December 18, 2008
More goodness from Beth Massi: I've been getting asked a lot lately about how to easily attach a local
Anonymous
December 18, 2008
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
Anonymous
December 18, 2008
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.
Anonymous
December 19, 2008
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
Anonymous
December 19, 2008
The comment has been removed
Anonymous
December 19, 2008
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.aspxI 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
Anonymous
December 19, 2008
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.
Anonymous
December 19, 2008
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
Anonymous
December 19, 2008
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.
Anonymous
February 26, 2009
The comment has been removed
Anonymous
February 27, 2009
hallo ....how add automatic attach database, to sqlexpress in setupdeployment in vb.net
Anonymous
February 27, 2009
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
Anonymous
March 11, 2009
Thanks Beth.Just what I needed.Bill
Anonymous
April 07, 2009
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)
Anonymous
April 30, 2009
can you please tell me the attaching and detaching of mdf files by sqlcmd in sqlexpress using a batch files
Anonymous
May 28, 2009
The comment has been removed
Anonymous
June 02, 2009
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=69HTH,-B
Anonymous
October 29, 2009
The comment has been removed
Anonymous
November 04, 2009
The comment has been removed
Anonymous
December 08, 2009
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
Anonymous
December 09, 2009
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.aspxIf 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.aspxHTH,-B
Anonymous
December 10, 2009
The comment has been removed
Anonymous
December 10, 2009
Beth I have created a batch file with the following script :sqlcmd -S .SQLEXPRESS -george -i sqlscriptcreateattach.sqlpausethat runs the createattach sql script that is :USE [master]GOCREATE DATABASE [Data2] ON( FILENAME = N'C:Program Filesweek PublishersSader Civil CassationCassation DataDataData2.mdf' ),( FILENAME = N'C:Program Filesweek PublishersSader Civil CassationCassation DataDataData2_log.ldf' )FOR ATTACHGOif exists (select name from master.sys.databases sd where name = N'Data2' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )GOUSE [master]GOCREATE LOGIN [sadd] WITH PASSWORD=N'1p@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOEXEC master..sp_addsrvrolemember @loginame = N'sader', @rolename = N'sysadmin'GOUSE [master]GOEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 2GOwhat shall i do after ? help
Anonymous
December 11, 2009
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
Anonymous
December 11, 2009
Dear Bethall 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 .
Anonymous
January 20, 2010
Beautiful Help and works right On.I should mention thatEXEC sp_detach_db 'AdventureWorks', 'true';GOis the script for Detachas most of the people will wnt to knowcause 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 databasethen used detach.prior to reading this blogI had to install SSMEonly for attach and detach process.So thanks a Lot again Beth
Anonymous
February 25, 2010
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:appmydb1") sql = "select A.fld1,B.fld1" sql = sql & " FROM [;database=c:myappmydb1.mdb].fld1 as A, " sql = sql & " [;database=c:myappmydb1.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 connectionto sql server express files.Thank you,Nenad
Anonymous
February 25, 2010
sorry, correction: sql = "select A.fld1,B.fld1" sql = sql & " FROM [;database=c:myappmydb1.mdb].fld1 as A, " sql = sql & " [;database=c:myappmydb2.mdb].fld1 as B "Nenad
Anonymous
February 27, 2010
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
Anonymous
June 23, 2010
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
Anonymous
June 24, 2010
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
Anonymous
July 02, 2010
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?
Anonymous
July 02, 2010
The comment has been removed
Anonymous
July 02, 2010
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?
Anonymous
October 20, 2010
Dear BethI 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
Anonymous
October 20, 2010
Dear BethI 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
Anonymous
August 28, 2011
The comment has been removed
Anonymous
September 03, 2011
There is a free tool "SQLSPlus" (on http://www.memfix.com ) which is like SQLPlus for SQL Server.Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, etc
Anonymous
January 18, 2012
good thanks
Anonymous
September 07, 2012
The comment has been removed
Anonymous
May 24, 2013
The comment has been removed
Anonymous
January 28, 2015
Your simple examples really help a lot.. thanks
Anonymous
June 02, 2015
Hello! I'm trying to develop a vb.net database application with visual studio 2010 and install it to other computers. It is a offline application with sql server database. So, I just want to know the full process from deployment to installation on other computer.
Anonymous
December 10, 2015
Sorry to revive old posts, but man you are my savior! (/'-')/ sql server 2014; have been looking left and right to be able to get back database data and tables from a unseemingly corrupt database mdf file, the management studio just complained about it not being able to do anything and giving me loops on error codes. i really thank you for this guide, i tried tons of softwares out there, reinstalled multiple instances of diferent versions, but this was the one solution that worked. Thank you very much.
Anonymous
April 27, 2016
The comment has been removed
Anonymous
July 13, 2016
Thanks Beth!Not sure if this belongs here but I'll try...I have built this script below but it does not attach. It creates the database but it does not attach. Do you know how I can attach?USE [master]GO/****** Object: Database [MYTABLE] Script Date: 5/26/2016 9:40:02 AM ******/declare @Path as nvarchar(max)SELECT @Path = CONVERT(nvarchar(max),SERVERPROPERTY('InstanceDefaultDataPath'))Print (@Path)declare @sql as nvarchar(max)set @sql = N'CREATE DATABASE [MYTABLE] ON (NAME = N''MYTABLE'', FILENAME = N''' + @Path + 'MYTABLE.mdf'', SIZE = 18432KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = N''MYTABLE_log'', FILENAME = N''' + @Path + 'MYTABLE_1.ldf'', SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ALTER DATABASE [MYTABLE] SET COMPATIBILITY_LEVEL = 100'--PRINT (@sql)EXEC(@sql)GO-install Schema and data...
- Anonymous
  July 14, 2016
  @Matt - you forgot the FOR ATTACH clause on the CREATE DATABASE statement. :-)
  - Anonymous
    July 16, 2016
    Thanks,/Would the syntax go like this?//Do I need to add USE [MYDATABASE]?//I changed MYTABLE to MYDATABASE/USE [master]GO/****** Object: Database [MYDATBASE] Script Date: 5/26/2016 9:40:02 AM ******/declare @Path as nvarchar(max)SELECT @Path = CONVERT(nvarchar(max),SERVERPROPERTY('InstanceDefaultDataPath'))Print (@Path)declare @sql as nvarchar(max)set @sql = N'CREATE DATABASE [MYDATBASE] ON (NAME = N''MYDATBASE'', FILENAME = N''' + @Path + 'MYDATBASE.mdf'', SIZE = 18432KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = N''MYDATBASE_log'', FILENAME = N''' + @Path + 'MYDATBASE_1.ldf'', SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ALTER DATABASE [MYDATBASE] SET COMPATIBILITY_LEVEL = 100' FOR ATTACH--PRINT (@sql)EXEC(@sql)GOUSE [MYDATBASE]IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [MYDATBASE].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [MYDATBASE] SET ANSI_NULL_DEFAULT OFF=ADD SCHEMA AND DATA BELOW HERE…

แชร์ผ่าน

Attaching a Local SQL Server Database Using sqlcmd

Comments

แหล่งทรัพยากรเพิ่มเติม