Applies To
Express Software Manager version 6.0 and higher

Summary
If you do not have a full version of Microsoft SQL Server with SQL Enterprise Manager, you can use the OSQL utility to update and delete data from your SQL database. This technical note lists a series of useful OSQL commands and provides links to more detailed information about using the commands.

Useful OSQL Commands for Maintaining an Express SQL Database
The following list includes some of the most commonly needed OSQL

This list describes some of the most commonly needed OSQL commands for manipulating data in your SQL database. For more information on any of these commands, click the related link.

Backing Up a Database
You should back up all of your Express data on a regular basis, and Express Software Manager 6.1 and 6.2 include a utility to back up your application libraries and databases. However, you may need to back up a database manually. To do so, use the following case sensitive OSQL command:

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

Restoring a Database
To restore an SQL database, use the following case sensitive OSQL command:

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>'

Deleting a Library from a Database
To delete all references to an application library from an SQL database, use the following case sensitive OSQL commands:

osql -U<username> -P<password> -d<database name> -o<output file name> -Q"SELECT libid, name, libpath FROM libs"

osql -d<database name> -U<user name> -P<password> -Q"DELETE FROM libs WHERE libid = <your libid>"

Reattaching a Database
To reattach an SQL database, use the following case sensitive OSQL command:

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>'"

Changing the State of a Read-only Database
If your database is in a read-only state, Express Software Manager will not be able to update data in the database. To change the state of an SQL database from read-only, use the following case-sensitive OSQL command:

osql -U<username> -P<password> -Q"sp_dboption '<database name>', 'READ_ONLY', 'FALSE'"

Adding a New Account to a Password-protected Database
You may need to add an account with database ownership (dbo) capabilities to MSDE or SQL Server. To add an account with dbo capabilities, use the following case sensitive OSQL commands:

osql –E -Q"Exec sp_addlogin @loginame = '<new account name>', @passwd = '<new password>'"

osql –E -Q"Exec sp_addalias '<new account name>', 'dbo'"

osql –E -Q"Exec sp_addsrvrolemember '<new account name>', 'sysadmin'"

Related Technical Notes

1596	Backing Up an SQL Database using OSQL
1597	Reattaching an SQL Database using OSQL Commands
1598	Restoring an SQL Database using OSQL
1599	Deleting a Library From an SQL Database using OSQL
1604	Changing the State of a Read-only SQL Database using OSQL
1605	Adding a New User with Database Owner Privileges using OSQL
9989	Express Software Manager 6.x Technical Notes