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
STEP 2 - connect to a db server
STEP 3 - set the variable path
STEP 4 - (optional) add standard T-SQL statements
STEP 5 - add scripts
Here's the full script that you can now customize and re-use e.g. per release.
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.
- 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.comSTEP 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]
[original publish date: 09/06/16]
Comments
Post a Comment