Automating SQL Scripts via Command Line (sqlcmd)

A typical deployment task in any systems integration project usually involves either deploying a sql database or making some modifications to an existing one.

Previous to SQL Server 2005, we could use osql to automate this task. We still can, but SQL Server 2005 brings us a command line utility with a little bit more grunt that is more purposed to our task.

This is sqlcmd. Follow that link for the msdn article about it.

This can let us do anything we can do via T-SQL via the command line. All you need to do is put all your sql into a script file and then you can run the script from the command line by inputting something like this:


sqlcmd -S localhost -i d:\deploy\DoTasks.sql -U myusername -P mypassword


Note that the switches seem to be case sensitive. You don't need to specif icy a user name and password, if you don't sqlcmd will just use your current security context.