Summary
If you do not have a version of Microsoft SQL Server that includes an interactive management application such as SQL Enterprise Manager or SQL Studio, you can still perform maintenance tasks such as backup and restore, on your Express database using OSQL, the command line SQL tool. This technical note describes how to perform such tasks using OSQL.

Using OSQL with the Express Database
OSQL is a command line tool that allows you to issue commands to Microsoft SQL Server. To run OSQL, simply bring up a DOS box and type OSQL followed by any required switches. You can view the complete OSQL command syntax with: This technical note describes only those switches required to perform the operations included in the note. Note: OSQL switches are case-sensitive. Switches:

• -S <sql-server-name> - the name of the SQL Server, including instance, if applicable. You may use "." (without quotes) if you are running OSQL on the same machine as SQL Server.
• -d <database-name> - the name of the database on which the operation is to be performed. For example -d ExpressDB.
• -U <user-name> - the SQL Server user account under which to run the specified command.
• -P <password> - the password associated with the specified user account.
• -E - use NT authentication to interact with SQL Server
• -Q "<SQL-command>" - the command to issue to SQL Server.
• -i <file-of-SQL-commands> - a file containing one or more commands to issue to SQL Server.

OSQL can also operate interactively. If you specify only the server, database, user name, and password, you will be presented with a prompt allowing you to enter and execute commands one at a time. The command exit exists interactive mode.

The examples below using the following sample values for the switch parameters described above:

• Database: ExpressDB
• SQL Server: .\ESM - that is, the ESM instance of SQL Server on the current machine
• User: sa - the SQL Server administrative account
• Password: mysapwd - the password associated with the sa account

Important notes

1. The operations described in this technical note require SQL Server administrative privileges. This means you must either use the SQL Server sa account, another administrative-capable account, or be logged into Windows under an account that has SQL Server administrative rights.
2. SQL commands are issued in the context of the machine running SQL Server. If you use OSQL on one machine to issue commands to SQL Server on a different machine, any file/path information specified must be in the context of the SQL Server machine, not your local machine.

Backups of your Express Database should be done on a regular basis. To create a backup using OSQL, use the following command: For example, using the sample parameters above and creating the backup file in the \sqlbackups folder on your C: drive, the command would be: Important note
You can write the backup to a local folder or network share, however in either case, the account under which SQL Server is running must have write access to the folder in which the backup file is written.

If you need to restore your Express Database from a backup, you can use the following OSQL command: Notice that when restoring a database, you specify where the backup file exists as well as where SQL Server will actually create the database files (.mdf, .ldf) when restoring. For example, using the sample parameters above, restoring a database backup found in the \sqlbackups folder on your C: drive, and creating the new database files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (the data folder for a typical instance of SQL Server 2005): Important notes

1. You can specify that the backup file come from a network share, however, the account under which SQL Server is running, must have access to the share.
2. The account under which SQL Server is running must also have write access to wherever the database files (.mdf, .ldf) are created.
3. If you are moving a database from one machine to another (or from one SQL Server instance to another), you will need to recreate the Express account used by Express Software Manager to access the data. (Even if the target SQL Server already has an Express account, you may need to "bind" it to the account information restored from the backup.) Included with the database and database tools components of your Express Software Manager installation is a batch file, InitializeExpressUser.bat which facilitates the creation (if necessary) and binding of the Express account. From a DOS prompt, you can run InitializeExpressUser.bat without parameters to view its syntax.