Execute SQL Server Script Files with the sqlcmd Utility

By:   |   Comments (5)   |   Related: More > Database Administration


Usually when we need to run a SQL script we open it in SQL Server Management Studio and execute it, but there are cases when the file is too big.  For example, when the script we need to run is a scripted database containing a large schema and data. Also, you may need to run a script on a SQL Server instance running on Linux where you cannot connect using SSMS due to firewall rules. In this tip I will show you how you can accomplish these tasks with sqlcmd.


It is a fact that we as SQL Server DBAs are more prone to use graphical tools for our day to day work, mostly because SQL Server has historically run on the Windows platforms. Things are changing and with the release of SQL Server 2017 it wont be uncommon to see SQL Server instances running on Linux. That will force us to adapt to new ways of doing our work, like running script files.

Sqlcmd to the Rescue

As I told you in my previous tip Introduction to SQL Server's sqlcmd Utility, this command line tool allows you to execute T-SQL statements, stored procedures, and script files from the console. Amongst the sqlcmd arguments there are three that will serve us when we need to execute a script from the command line.

Argument     Description
-i <filename> This argument followed by the script name serves as input for sqlcmd. After executing the commands in the input file sqlcmd exits.
-o <filename> With this argument you can make the input queries write the output to a file.
-u Specifies that the output file is stored in Unicode format.
-e Echo input. Basically, when you specify this argument, sqlcmd writes the commands of the input file to the console or the output file before showing the results.

SQL Server sqlcmd Examples

Now I will show you a few examples on how to run script files with sqlcmd for different scenarios. I am using a script file with the AdventureWorksDW database which you can download from Microsoft for free at this link: AdventureWorks Databases and Scripts for SQL Server 2016.

Passing an Input file to sqlcmd

If you need to execute a script file with sqlcmd on a server using Windows Authentication (a Trusted Connection), you can do so with the following command:

sqlcmd -S -E -i AdventureWorksDW2012.sql

The S argument is the server name, and the E argument is for a Trusted Connection.

On the other hand, if we need to accomplish the same task, but use a SQL Server login, there is a slight variation to the previous command:

sqlcmd -S U sa -P 1234 -i AdventureWorksDW2012.sql

In this command the U argument is used to specify the SQL login account and the P is for the account password. As you may notice, this is not the best way to authenticate to SQL Server if you need to use sqlcmd in a batch script, since it shows the password.

Saving the Output to a Text File

Usually when we are running a big script, we cannot use the screen output to determine if one of the batches on the input script has failed because the output is displayed really fast on the screen. To overcome this situation we must redirect the output to a text file for further analysis.

The following command will connect to SQL Server using Windows Authentication, execute the file after the i argument and save the execution results in the file after the o argument.

sqlcmd -S -E -i AdventureWorksDW2012.sql -o QueryResults.txt

Saving the Output to a Text File Including the Input Batches

This feature is very useful when you are running small scripts on a database and need to send the results to another person, like the development team. To do so, we have to add the e argument:

sqlcmd -S -E -i AdventureWorksDW2012.sql -o QueryResults.txt -e

Remember that the e argument is not required to be used with the o option. You can also use it without the o argument if you need the results to be displayed on the screen. That may be useful if you are invoking sqlcmd from another program that reads the standard output.

Suppose you have a script file named sample.sql with the following contents:


If you want to execute this file and display the output to the console, you can use the following command:

sqlcmd -S U sa -P 1234 -i sample.sql -e

The output of the command will be like the screen capture below. I enclosed the commands with a red box to make this clear.

SQLCMD to Screen - Description: Screen capture showing the screen execution of sqlcmd including the input batches on the output.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, November 25, 2020 - 12:24:06 AM - Ahmad Back To Top (87840)
Excellent Work. Followed this guide to setup SQL Server on Ubuntu 18.04. Can you please also share guide how to copy multiple lines of scripts in sql command line?

Saturday, September 26, 2020 - 7:14:10 AM - Viktor Back To Top (86538)
Hi Daniel

Could you please explain how to use %%i variable in batch code as If %%i ==1 (do something)
SQLCMD -S xxx -d xx -U xxx -P xxx -v %%i = "GatewayTrouble" -i azureselect.sql

Best Regards

Tuesday, April 10, 2018 - 9:46:05 PM - Daniel Farina Back To Top (75668)


Hi Andrew!

Thanks for asking. Look if the following sentence works for you:

sqlcmd -S SERVER -E -Q master.dbo.sp_help

The previous command will execute the stored procedure sp_help on the master database and exit.

Please let me know if this solved your problem.

Best Regards!

Tuesday, April 10, 2018 - 10:27:31 AM - Andrew Back To Top (75661)

 Hi Daniel 

Could you please explain the setup if i want to only call a Stored Procedure in mssql using sqlcmd. I do not need to see any output but merely to execute the proc without having to login to mssql.






Thursday, July 6, 2017 - 8:07:28 AM - Channdeep Singh Back To Top (58955)

Very nice and much needed work. Many many thanks. We all have needed many of these commands time-to-time in our journey; but they were not collated in one place.....and everytime I had to gather and do the "hit and trial" :) Thanks again.

get free sql tips
agree to terms