Attaching a Local SQL Server Database Using sqlcmd
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!
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
