Tuesday, March 9, 2010

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


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


To create a database

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

sqlcmd create database MyDatabase1


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


Similarly other commands can be issued.


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 :


Interesting Observation – Using sqlcmd From SSMS Query Editor: