SQL Server command line tool

What is osql? Osql is a Microsoft Windows 32 command prompt tool that can be used to run Transact-SQL statements and script files. Osql tool uses ODBC database applicationsProgramThe programming interface (API) communicates with the server. To put it bluntly, Microsoft's exe program can be used to connect to the local server or other servers.

C:> osql-S server name \ Instance name-U sa press enter to display the following prompt, enter the password.

Password 11111

Then you can enter SQL for database operations.

Use master;

Go

Select * From sysusers;

Go

Osql this command line tool to manage sqlserver

1. Create a New Login User

Verify with windows,

Sp_grantlogin

For example:

Exec sp_grantlogin Username

SQL Server Verification

Sp_addlogin

For example:

Exec sp_addlogin user name and password

Only users with the sysadminsecurityadmin role can run the sp_grantloginsp_addlogin stored procedure.

2. The above user name can only log on to the server and cannot operate on the database.

Only users with the sysadmindb_accessadmindb_owner role can run sp_grantdbaccess.

Sp_grantdbaccess

Exec sp_grantdbaccess window user name, user name

3. Change Password

Exec sp_password: old password, new password, username

Users with SysAdmin roles can change the passwords of other users. Generally, users can only change their own passwords.

4. Create a database

Create Database test (database name)

Users with the sysadmindbcreator role can perform this operation.

5. Back up and restore the database

Backup

Backup database mydb to disk = C: msdebackupmydb. Bak

Backup log mydb to disk = C: msdebackupmydb_log.bak

Users with the sysadmindb_ownerdb_backupoperator role can perform this operation.

Restore

Restore database mydb from disk = C: msdebackupmydb. Bak

Users with the sysadmindbcreatordbo role can perform this operation.

6. Attach and detach a database

Database separation: You can detach a database from the instance of the current SQL Server server.

Exec sp_detach_db mydb

Users with SysAdmin roles can perform this operation.

Attach the database:. MDF and. LDF files are required. The following statement can append these two files to the current system to generate a new database.

Exec sp_attach_db @ dbname = nmydb,

@ Filename1 = NC: msdebackupmydb. MDF,

@ Filename2 = NC: msdebackupmydb. LDF

The upper-case letter N is the prefix of the Unicode String constant. The prefix N is defined in the SQL-92 standard.

2. SQL Server 2005

SQL Server 2005 still supports the osql tool programs of the earlier version to connect to SQL Server 2005. However, you must use the osql version provided by SQL Server 2005 to execute new functions. If you use the osql provided by SQL Server 2000 to access SQL Server 2005, only functions compatible with the old version can be executed, and some new functions cannot be used.

Before logging on to and running the T-SQL syntax through sqlcmd, there are quite a few optional parameters available, you can enter the following syntax in the command prompt line:

Sqlcmd /?

1. Use the "sqlcmd" tool in SQL Server 2005

1. You can use sqlcmd to execute interactive actions, such:

 
C: sqlcmd> sqlcmd 1> select name from SYS. Databases 2> go
 

You can also try to enter the following command, Real Server LIST:

 
1>: serverlist servers: wuyz 1>
 

To use other commands, enter: Help /?