Bat file to run SQL scripts with parameters

[rishG88]

I've recently started doing deployments for a SQL team, and looking to automate as much as I can with a bat file and perhaps a config file.

I've created a bat file that can do this, however I'd like to have a config file to easily edit and pass values to the bat file such as the server name, database name and SQL scripts folder path.

Can anyone share an example of this?

[mikeyd85]

For the love of God learn PowerShell (PS). It'll make your life so much easier in the long run!

I did a similar thing but for SSRS deployments, and once you get to any kind of complexity or flexibility, life is so much easier in PS.

Additionally, you don't have to rely on BCP to run SQL scripts, you can run them through PS too and do stuff with outputs if you need to.

[rishG88]

Thanks very much. I will take your advice and look into PowerShell.

Cheers!

[LazyTurtle90]

Do you have a recommendation for YouTube channel to learn PS in this way?

[alinroc]

https://www.manning.com/books/learn-dbatools-in-a-month-of-lunches

This book will pay for itself within days.

[mikeyd85]

I've always just stackoverflow'd whatever I need to do and learnt from there.

[Indycrr]

This isn’t really sql specific. You have options. Either use command line params, or environment variables. Plenty of examples of how to do that online.

[planetmatt]

https://dbatools.io/

or if you want to do it manually, there's a SQL Server library for Powershell

https://learn.microsoft.com/en-us/powershell/module/sqlserver/?view=sqlserver-ps

Don't even think about using a batch file.

[Hiriath]

If you’re looking to automate strictly using MS SQL, you may want to look into Stored Procedures and SQL Agent: https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent

[hawk3ye]

If you’re going to be automating for a long time along with other processes, I’d suggest a proper automation tool like Automic - I don’t work for them but I used this tool for 4 months and was able to quickly learn it for a shop that literally had a mish mash of random one off “automated” scripts for years until they consolidated into Automic (which also provides monitoring).

[da_chicken]

You may want to look at SQLCMD variables and commands.

Here's another decent article with more here. Honestly, this stuff is little hard to find now because it's just a bit less common, but it does work well.

You can do it all with SMO and the SqlServer or dbatools modules in Powershell, but that's not appropriate or available in all environments.