I have a home-grown database build script that allows me to build multiple instances of my database.
The build scripts allow me to target a number of different environments (test, dev, etc) and perform upgrade or replace operations.
I use the wonderful FART utility to perform text substitution into templated script files, using placeholders delimited with “@@”. FART is a command-line utility developed by Lionello Lunesu. The name is an acronym for “Find And Replace Text”.
For example, consider the following template script, build.tpl:
create database @@DB_NAME@@ containment = partial on primary ( name = '@@DB_NAME@@' , filename = '@@DATA_PATH@@@@DB_NAME@@.mdf' , size = 5120KB, filegrowth = 1024KB ) log on ( name = '@@DB_NAME@@_log' , filename = '@@LOG_PATH@@@@DB_NAME@@_log.ldf' , size = 1024KB, filegrowth = 10% ) collate Latin1_General_CS_AS; go |
This template can be written out as a targeted script using a sequence of command-line instructions:
copy build.tpl build.sql set SB_DB_NAME=MY_NEW_DB set SB_DATA_PATH=c:\SQL\Data\ set SB_LOG_PATH=C:\SQL\Log\ fart *.sql @@DB_NAME@@ %SB_DB_NAME% fart *.sql @@DATA_PATH@@ %SB_DATA_PATH% fart *.sql @@LOG_PATH@@ %SB_LOG_PATH% |
Running this yields the following output:
build.sql Replaced 5 occurence(s) in 1 file(s). build.sql Replaced 1 occurence(s) in 1 file(s). build.sql Replaced 1 occurence(s) in 1 file(s). |
And the contents of build.sql is now:
create database MY_NEW_DB containment = partial on primary ( name = 'MY_NEW_DB' , filename = 'C:\SQL\Data\MY_NEW_DB.mdf' , size = 5120KB, filegrowth = 1024KB ) log on ( name = 'MY_NEW_DB_log' , filename = 'C:\SQL\Log\MY_NEW_DB_log.ldf' , size = 1024KB, filegrowth = 10% ) collate Latin1_General_CS_AS; go |
This is now ready to execute as part of the larger automated build process (this is a simplified example, obviously).
This is all fine and dandy until we try to target a brand new instance of SQL Server 2017 running on Linux. (Distribution of choice: Mint.) The Paths need to be changed to the Unix-style forward slashes:
set SB_DB_NAME=MY_NEW_DB set SB_DATA_PATH= /var/opt/mssql/data/ set SB_LOG_PATH= /var/opt/mssql/data/ |
You’d think this would Just Work, but unfortunately we get an error:
build.sql Replaced 5 occurence(s) in 1 file(s). > fart *.sql @@DATA_PATH@@ %SB_DATA_PATH% Error: invalid option -/ Error: invalid option -o Error: invalid option -t Error: invalid option -/ Error: invalid option -m Error: invalid option -l Error: invalid option -/ Error: invalid option -d Error: invalid option -t Error: invalid option -/ |
We have to escape the slashes, and also tell FART to use c-style extended characaters, with the -C switch:
set SB_DB_NAME=MY_NEW_DB set SB_DATA_PATH=\ /var \ /opt \ /mssql \ /data \/ set SB_LOG_PATH=\ /var \ /opt \ /mssql \ /data \/ fart *.sql @@DB_NAME@@ %SB_DB_NAME% fart -C *.sql @@DATA_PATH@@ %SB_DATA_PATH% fart -C *.sql @@LOG_PATH@@ %SB_LOG_PATH% |
This looks a bit odd, and the output to console includes a warning message:
Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ build.sql Replaced 1 occurence(s) in 1 file(s). Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ Warning: unrecognized character escape sequence: \/ build.sql Replaced 1 occurence(s) in 1 file(s). |
However, the substitution did take place, and the result is correct:
create database MY_NEW_DB containment = partial on primary ( name = 'MY_NEW_DB' , filename = '/var/opt/mssql/data/MY_NEW_DB.mdf' , size = 5120KB, filegrowth = 1024KB ) .. etc |
My guess is that this console warning is probably a bug in FART.
UPDATE: And someone else has reported it: #12 Warning: unrecognized character escape sequence: \/.
Recent Comments