Backing Up an SQL Database using OSQL
Technical Note 1596
Last Reviewed 16-Sep-2007

Applies To
Express Software Manager version 6.2 and above

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

Note: For information about using OSQL to restore a backed up database, see Technical Note 1598.

Backing up Express Data
Backups of your Express database should be done on a regular basis. (If you are running version 6.2 or earlier, a complete backup of Express data includes a backup of your application library as well as your database.) This note discusses only the process of backing up an SQL database.

Note: If you have Express Software Manager 6.1 or 6.2 you can use the Backup utility to back up both your database and library. See the Backup utility help file (backup.chm) that was installed on your Administrative Console workstation.

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.

Backing up an SQL Database with OSQL Commands
To back up an SQL database using OSQL commands, enter the following case-sensitive command at the command prompt of the database server:

osql -U<user> -P<password> -Q"BACKUP DATABASE <database-name> to disk = '<path>\<database-name>.bak' WITH INIT"

For example, if you use the default “ExpressDB” database and have a password of “sapwd” for the “sa” account, your command would look like this (Note: The C:\temp directory must already exist, while the database backup file “express.bak” must not exist):

osql –Usa –Psapwd -Q"BACKUP DATABASE ExpressDB TO DISK = 'c:\temp\express.bak' WITH INIT"

The database backup, a file called express.bak, will be created in the C:\temp folder.

Backing up an SQL Database on a Remote Database Server
By adding an additional parameter that specifies the database server, and by specifying a UNC path to your output location, you can use OSQL commands to back up a remote database.

To back up a remote SQL database, enter the following case-sensitive command at the command prompt of the database server:

osql –S<database-server> -U<user> -P<password> -Q"BACKUP DATABASE <database-name> to disk = '<UNC path>\<database-name>.bak' WITH INIT"

For example, if your database server is called “ESMdbSrv”, you are using the default “ExpressDB” database with a password of “sapwd” on the “sa” account and you have shared out a folder as “temp” on your database server, your command would look like this (Note: The \\ESMdbSrv\temp directory must exist and be shared out, and the “express.bak” file cannot exist):

osql –SESMdbSrv –Usa –Psapwd -Q"BACKUP DATABASE ExpressDB to disk = '\\ESMdbSrv\temp\express.bak' WITH INIT"

The database backup, a file called express.bak, will be created in the specified folder on the database server machine.

Note: The backup directory can also be a local (non-UNC) path, but this path is in the context of the database server machine, not the local machine on which the SQL command is issued.

Related Technical Notes

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