\ncreate database @@DB_NAME@@\n containment = partial\n on primary \n ( name = '@@DB_NAME@@',\n filename = '@@DATA_PATH@@@@DB_NAME@@.mdf',\n size = 5120KB,\n filegrowth = 1024KB\n )\n log on\n ( name = '@@DB_NAME@@_log',\n filename = '@@LOG_PATH@@@@DB_NAME@@_log.ldf',\n size = 1024KB,\n filegrowth = 10%\n )\n collate Latin1_General_CS_AS;\ngo \n<\/pre><\/div>\n\n\nThis template can be written out as a targeted script using a sequence of command-line instructions:<\/p>\n\n\n
\ncopy build.tpl build.sql\n \nset SB_DB_NAME=MY_NEW_DB\nset SB_DATA_PATH=c:\\SQL\\Data\\\nset SB_LOG_PATH=C:\\SQL\\Log\\\n \nfart *.sql @@DB_NAME@@ %SB_DB_NAME%\nfart *.sql @@DATA_PATH@@ %SB_DATA_PATH%\nfart *.sql @@LOG_PATH@@ %SB_LOG_PATH%\n<\/pre><\/div>\n\n\nRunning this yields the following output:<\/p>\n\n\n
\nbuild.sql\nReplaced 5 occurence(s) in 1 file(s).\n \nbuild.sql\nReplaced 1 occurence(s) in 1 file(s).\n \nbuild.sql\nReplaced 1 occurence(s) in 1 file(s).\n<\/pre><\/div>\n\n\nAnd the contents of build.sql is now:<\/p>\n\n\n
\ncreate database MY_NEW_DB\n containment = partial\n on primary \n ( name = 'MY_NEW_DB',\n filename = 'C:\\SQL\\Data\\MY_NEW_DB.mdf',\n size = 5120KB,\n filegrowth = 1024KB\n )\n log on\n ( name = 'MY_NEW_DB_log',\n filename = 'C:\\SQL\\Log\\MY_NEW_DB_log.ldf',\n size = 1024KB,\n filegrowth = 10%\n )\n collate Latin1_General_CS_AS;\ngo \n<\/pre><\/div>\n\n\nThis is now ready to execute as part of the larger automated build process (this is a simplified example, obviously).<\/p>\n\n\n\n
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:<\/p>\n\n\n
\nset SB_DB_NAME=MY_NEW_DB\nset SB_DATA_PATH=\/var\/opt\/mssql\/data\/\nset SB_LOG_PATH=\/var\/opt\/mssql\/data\/\n<\/pre><\/div>\n\n\nYou’d think this would Just Work, but unfortunately we get an error:<\/p>\n\n\n
\nbuild.sql\nReplaced 5 occurence(s) in 1 file(s).\n \n> fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%\nError: invalid option -\/\nError: invalid option -o\nError: invalid option -t\nError: invalid option -\/\nError: invalid option -m\nError: invalid option -l\nError: invalid option -\/\nError: invalid option -d\nError: invalid option -t\nError: invalid option -\/\n<\/pre><\/div>\n\n\nWe have to escape the slashes, and also tell FART to use c-style extended characaters, with the -C switch:<\/p>\n\n\n
\nset SB_DB_NAME=MY_NEW_DB\nset SB_DATA_PATH=\\\/var\\\/opt\\\/mssql\\\/data\\\/\nset SB_LOG_PATH=\\\/var\\\/opt\\\/mssql\\\/data\\\/\n \nfart *.sql @@DB_NAME@@ %SB_DB_NAME%\nfart -C *.sql @@DATA_PATH@@ %SB_DATA_PATH%\nfart -C *.sql @@LOG_PATH@@ %SB_LOG_PATH%\n<\/pre><\/div>\n\n\nThis looks a bit odd, and the output to console includes a warning message:<\/p>\n\n\n
\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nbuild.sql\nReplaced 1 occurence(s) in 1 file(s).\n \nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nWarning: unrecognized character escape sequence: \\\/\nbuild.sql\nReplaced 1 occurence(s) in 1 file(s).\n<\/pre><\/div>\n\n\nHowever, the substitution did take place, and the result is correct:<\/p>\n\n\n
\ncreate database MY_NEW_DB\n containment = partial\n on primary \n ( name = 'MY_NEW_DB',\n filename = '\/var\/opt\/mssql\/data\/MY_NEW_DB.mdf',\n size = 5120KB,\n filegrowth = 1024KB\n )\n.. etc\n<\/pre><\/div>\n\n\nMy guess is that this console warning is probably a bug in FART.<\/p>\n\n\n\n
UPDATE: And someone else has reported it: #12 Warning: unrecognized character escape sequence: \\\/<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-database-management","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}