Using sqlcmd utility in SQL Server

sqlcmd is a utility that helps you fire SQL commands and operations via command prompt.

It can be used in case your machine has SSSM (SQL server management studio) or any other visual tool to manage databases are not installed.

The command to login to the SQL server instance installed is as follows:

For logging in using Windows credential

(Equivalent to SQL Server Trusted Connection or ‘Integrated Security = true’):

You should replace the <.\sqlexpress> with the instance name of your SQL server installation

sqlcmd -S .\sqlexpress –E

Go

For Logging in Using SQL Server Login:

You should replace the <sa> with the user name and <system> with the password

sqlcmd -S .\sqlexpress –U sa –P system

Go

To create a database

you can issue Create Database <database name>command like this:

sqlcmd create database MyDatabase1

Go

To restore a database from a backup (.bak) file

you can issue following command:

Replace “MyDatabase1” with the name of the database you want to restore and the file path (within single inverted comma) with the full path of the .bak file.

the ‘with replace’ option replaces all old tables and data with the tables and data of the backup.

sqlcmd restore database MyDatabase1 from disk = 'c:\backup\MyDatabase1Backup.bak' with replace

Go

Similarly other commands can be issued.

References

These are the few good references that helped me learn this:

SQL Command Utility  : http://msdn.microsoft.com/en-us/library/ms162773.aspx

Restoring a database : http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx

Introduction and Explanation to sqlcmd :

http://blog.sqlauthority.com/2007/09/06/sql-server-2005-introduction-and-explanation-to-sqlcmd/

Interesting Observation – Using sqlcmd From SSMS Query Editor:

http://blog.sqlauthority.com/2009/01/06/sql-server-interesting-observation-using-sqlcmd-from-ssms-query-editor/