SQL how to attach a database / DB using osql.exe

This is a quite simple task ..

Assuming you have an MDF file called cxnMassImport.mdf and you want to attach it using osql.exe.

Open a CMD

Type:

osql.exe -E -S .\SQLExpress

If you don’t have osql.exe on the system, you can just copy it from an SQL Server to your system. Osql.exe can (usually) be found under
%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE (for a SQL 2005 Installation)
%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE (for a SQL 2008 Installation)
All of the osql.exe’s dependencies (DLLs etc) exist on a more or less default Windows setup.

The used osql.exe syntax for this example is:
-E => Trusted Connection – means your currently logged on user credentials are used to do the connection to the SQL Server

-S => Servername and Instance. Use a dot for the local system. If your SQL Server is some kind of MSDE or SQL Express Version, you must provide the Instance Name. For SQL Express it is usually SQLExpress. To find out the Instance Name, go to Services.msc and check the SQL Server Service. See the value between the ().

Services_SQLServer(Instance)

Services_SQLServer(Instance)

To connect to the SQL Server Instance using SQL integrated security, use the –U for the username (sa) and -P for the password.
i.e.

osql.exe -U sa -P P@ssW0Rd -S .\SQLExpress

Now to attach the MDF file, on the osql.exe prompt write:

sp_attach_db @dbname='cxnMassImport', @filename1='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cxnMassImport.mdf'
GO
osql_sp_attach_db

osql_sp_attach_db

To verify if the database has been attached write:

SELECT name, filename FROM sysdatabases WHERE name LIKE '%cxn%'
Osql_SELECT_Name_Filename_FROM_SysDatabases
Osql_SELECT_Name_Filename_FROM_SysDatabases

If you need to detach the database, write:

sp_detach_db @dbname='cxnMassImport'

3 thoughts on “SQL how to attach a database / DB using osql.exe

  1. Pingback: ..::\ www.christiano.ch //::.. » SQL: List Database Connections for specific user

  2. Suppose you have made a software application and you intend to publish it as setup package to the customers.

    Now the question is how you can attach DB from the inside of application.

    Can you please do a favor and answer?

  3. It is a very good guide for us to use commands to attach a database to a SQL instance.Thank you so much.

Leave a Reply

Your email address will not be published. Required fields are marked *