Reattaching an SQL Database using OSQL Commands
Technical Note 1597
Last Reviewed 16-Sep-2007

Applies To
Express Software Manager version 6.2

Summary
If you do not have a full version of Microsoft SQL Server with SQL Enterprise Manager, you can reattach your Express SQL database using the OSQL utility. This technical note describes how to use OSQL to reattach an SQL database.

About the OSQL Utility and OSQL Commands
The OSQL utility comes with every copy of SQL Server, including MSDE. This utility allows you to run scripts, system procedures, and Transact-SQL statements directly from the operating system command prompt.

OSQL commands can include a large number of parameters or switches. To see a list of OSQL command options, enter the following command at the command prompt of the database server (the machine on which SQL Server or MSDE is running):

osql -?

Many of the switches are case sensitive while some parts of the commands are not. Unless you have specific experience with OSQL, we recommend that you enter the commands exactly as they are presented in this note.

The OSQL utility can be run in interactive mode, or in batch mode. The instructions in this technical note specify a single command line that starts the utility, executes a query, and exits the utility afterward.

For more information about OSQL, see the Microsoft Web site.

Reattaching an SQL Database with OSQL Commands
To reattach an SQL database using OSQL, enter the following case-sensitive command at the command prompt of the database server (the computer on which the database resides):

osql -U<username> -P<password> -Q"Exec sp_attach_db @dbname = N'<database name>', @filename1 = N'<local path to .mdf data file>', @filename2 = N'<local path to .ldf log file>'"

Examples
Note: Unless otherwise stated, the below examples assume that you are restoring the Express database, are using the “sa” account, and that this account has a password of “esm02”. The examples also assume that the data and log files exist in their specified locations, and that the database you are reattaching does not exist.

Reattaching the Express database
To reattach the “Express” database, enter this command at the command prompt of the database server:

osql -Usa –Pesm02 -Q"Exec sp_attach_db @dbname = N'Express', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\express.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\express_log.ldf'"

Reattaching the Express database on a remote database server
You can reattach a database on a remote database server by including a “–S<database server>” option to specify the server name.

To reattach an SQL database on a remote database server, enter this case-sensitive command at the command prompt of a computer that has OSQL installed and is able to access the database server (ESMdbSrv):

osql –SESMdbSrv -Usa –Pesm02 -Q"Exec sp_attach_db @dbname = N'Express', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\express.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\express_log.ldf'"

Related Technical Notes

1606	Maintaining Your SQL Database With OSQL
2000	Express Software Manager Technical Notes
9989	Express Software Manager 6.x Technical Notes