SQLCMD was first introduced with SQL Server 2005 and was designed as a replacement to old SQL utilities like OSQL and ISQL. SQLCMD was written from scratch, and a lot of effect was put into performance and features. Before typing sql in Management Studio query editor, do not forget to enable SQLCMD mode under Query menu. It will pass the script to the SQLCMD application instead of submitting it to the database engine directly.
Unlike old tools,with use ODBC to connect to SQL Server, SQLCMD uses more efficient OLE DB connection and allows you to make multiple connections to different servers within the same script. SQLCMD also provides the ability to pass variables from either command line arguments or within the script itself.
Samples:
To backup DB you can use the following script:
:SETVAR myConnection FENCO-DW1 :SETVAR myDatabase AdventureWorks BACKUP DATABASE $(myDatabase) TO DISK='C:\Backups\$(myDatabase).bak' GO |
the result:
Processed 21312 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.
Processed 1 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.
BACKUP DATABASE successfully processed 21313 pages in 3.590 seconds (46.381 MB/sec).