Restoring an SQL Database using OSQL
Technical Note 1598
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 restore a previously backed up SQL database using the OSQL utility. This technical note describes how to use the OSQL command to restore an SQL database.

Note: For information about using OSQL to back up an SQL database, see Technical Note 1596.

Restoring a Express Data
Express Software Manager uses a combination of an SQL database and an application library to store data from client machines. A complete restore of Express data includes both a restore of the SQL database and a restore of all the application libraries associated with that database. This note discusses only the process of restoring an SQL database.

Note: If you have Express Software Manager 6.1 or 6.2 you can use the Restore utility to restore both your database and library. See the Restore utility help file (restore.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.

Restoring an SQL Database
To restore a previously backed up SQL database, enter the following command at the command prompt of the database server (the computer on which the database resides):

osql -U<username> -P<password> -o<output file name> -Q"RESTORE DATABASE <database name> FROM DISK = '<full path to backup file>' WITH MOVE '<logical name of data file>' TO '<local path to .mdf data file>', MOVE '<logical name of log file>' TO '<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”.

Restoring the Express database
To restore the “Express” database, enter this command at the command prompt of the database server (the backup file “express.bak” must exist in the C:\temp directory):

osql -Usa –Pesm02 -Q"RESTORE DATABASE Express FROM DISK = 'c:\temp\express.bak' WITH MOVE 'Express' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Express.mdf', MOVE 'Express_Log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Express_Log.ldf'"

Restoring the Express database on a remote database server
You can restore a database on a remote database server by including a -S option to specify the server name.

Note: The \temp directory on the remote database server must be shared out.

Enter this command at the command prompt of a computer that has OSQL installed on it and is able to access the database server (ESMdbSrv):

osql –SESMdbSrv -Usa –Pesm02 -Q"RESTORE DATABASE Express FROM DISK = '\\ESMdbSrv\temp\express.bak' WITH MOVE 'Express' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Express.mdf', MOVE 'Express_Log' TO ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\Express_Log.ldf'"

Restoring the Express database to a new name
You can use an OSQL command to create a new database from a backup of an existing database by specifying a new name.

Note: The backup file “express.bak” must exist in the c:\temp directory.

To create a new database (ExReport) from a backup of an existing database (Express), enter this command at the command prompt of your database server:

osql -Usa –Pesm02 -Q"RESTORE DATABASE ExReport FROM DISK = 'c:\temp\express.bak' WITH MOVE 'Express' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ExReport.mdf', MOVE 'Express_Log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ExReport_Log.ldf'"

Restoring the Express database to a new name, on a remote database server
You can also use OSQL to create a new database from a backup of an existing database, on a remote database server.

Note: The express.bak file must exist in the \temp directory, the \temp directory must be shared out, and the express.mdf and express_log.ldf files must exist in the \Program Files\Microsoft SQL Server\MSSQL\Data directory.

To create a new database (ExReport) on a remote database server (ESMdbSrv), enter this command at the command prompt of a computer that has OSQL installed on it and is able to access the database server:

osql –SESMdbSrv -Usa –Pesm02 -Q"RESTORE DATABASE ExReport FROM DISK = '\\ESMdbSrv\temp\express.bak' WITH MOVE 'Express' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ExReport.mdf', MOVE 'Express_Log' TO ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\ExReport_Log.ldf'"

Related Technical Notes

1596	Backing Up 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