Running multiple scripts via SQLCMD

On some of our applications we have multiple database scripts that we need to run in a specific sequence as part of a release. Using SQLCMD you can write a quick and easy script to do the job for you.

There are other more involved alternatives such as using a Visual Studio Database Project, using Entity Framework Code First migrations or creating custom builds but sometimes a simple solution is all you need.

STEP 1 - enable SQLCMD
  • In SQL Server Management Studio (SSMS), go to the Query menu and select SQLCMD

STEP 2 - connect to a db server
  • this would be the same value as your SSMS connection server name
  • by simply changing this value, you can run these scripts on any other environment
:CONNECT mysqlserver.com

STEP 3 - set the variable path
  • move all the scripts you want to run in a single folder and set the full path to it
:SETVAR path "c:\myscriptsfolder"

STEP 4 - (optional) add standard T-SQL statements 
  • e.g. setting your script name in each script so it can then be written to a script log table
DECLARE @myScriptName nvarchar(128)

STEP 5 - add scripts
  • add script filenames in the desired sequence
:R $(path)\Tables.sql

Here's the full script that you can now customize and re-use e.g. per release.

:CONNECT mysqlserver.com
:SETVAR path "c:\myscriptsfolder"

DECLARE @myScriptName nvarchar(128)

:R $(path)\Tables.sql
:R $(path)\Keys.sql
:R $(path)\Data.sql
:R $(path)\dbView.sql
:R $(path)\dbStoredProcedure.sql

Now ask your IT/DBA team to send you a thank you note!

[original publish date: 09/06/16]

Comments

Popular posts from this blog

ASP.NET Identity Remember Me

IIS Express Client Certificates

ASP.NET MVC - How to enable/disable CaC/Client Certificate authentication per area or route.