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


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 ().



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

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'


To verify if the database has been attached write:

select name, filename from sysdatabases where name like '%cxn%'

If you need to detach the database, write:

sp_detach_db @dbname='cxnMassImport'