Changing the State of a Read-only SQL Database using OSQL
Technical Note 1604
Last Reviewed 19-Dec-2002
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 change an SQL database from a read-only state using the OSQL utility. This technical note describes how to use the OSQL command to switch the state of an SQL database from read-only.
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):
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.
Changing the Read-only State of an SQL Database State Using OSQL Commands
To change the state of an SQL database that has been set to read-only, 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"sp_dboption '<database name>', 'READ_ONLY', 'FALSE'"
Examples
Note: Unless otherwise stated, the below examples assume that you are working with the Express database, are using the “sa” account, and that this account has a password of “esm02”.
Changing the state of the Express database from read-only
To change the state of the “Express” database, enter this command at the command prompt of the database server:
osql -Usa –Pesm02 -Q"sp_dboption 'express', 'READ_ONLY', 'FALSE'"
Changing the state of the Express database from read-only on a remote server
You can change the state of a database on a remote database server by including a -S option to specify the server name.
To change the state of 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"sp_dboption 'express', 'READ_ONLY', 'FALSE'"
Related Technical Notes
| 1606 |
Maintaining Your SQL Database With OSQL |
| 9989 |
Express Software Manager 6.x Technical Notes |
|
