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