0

I'm trying to set up a test environment that simulates (very roughly) having three servers. I'm doing this by creating 3 databases on my local instance of SQL which I'll call [A], [B], and [C]. Why I can't simply set up three test instances, is a story for another time. I've got a bunch of code files I want to execute using SQLCMD, which is no problem. I've got a parent SQLCMD file that looks like

:Setvar db gt
:Setvar filepath "C:\Documents\test\"
:r "C:\Documents\test\master.sql"

then another file (master.sql) with a few statements like create database $(db) and so on.

The problem now however is in the actual code files, the database referenced is [Alerts] on the actual server, but in my text harness the database has to be [A], [B] or [C] (since I'm using the database names to simulate the server).

Imagine I also have a file MyTable.sql which looks roughly like this:

create table [alerts].[dbo].[MyTable]
(
    id int identity(1,1)
)

In pseudocode, I'd like to do something like this:

(:r $(filepath)MyTable.sql).Replace('[Alerts]', '$(db)')

Does anyone know if this is possible?

1

If I understand your question correctly, let me try providing an alternate solution.

You could write a quick powershell script to automate the whole process - -reading source .sql files In a loop - -replacing 'Alerts' word with A (B and C). -run those files calling sqlcmd within the powershell script

  • "quick PowerShell script" – Erik Darling Feb 1 '17 at 6:11
  • @DBAalwaysOn I would certainly consider that, in fact I kind of did, but my PoSh knowledge is almost non-existent. Can you help with what that might look like? – Xedni Feb 1 '17 at 15:12
0

As an example something that i have used for an automatic azure upload using PowerShell:

foreach ($file in $configFiles)
{
    write-Host "Processing $($file.PSPath)" -ForegroundColor CYAN
    (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("ReplaceMe"), "Replaced" } | Set-Content $file.PSPath
    Invoke-SQLCMD -InputFile $file.PSPath -ServerInstance $ServerAddress -database $databaseName 
}

In your case you would just have to run this in another loop for each databaseName ( i would choose something other than A B C ) so something like this:

$dbNames = "databaseA", "databaseB", "databaseC"
$configFiles = Get-ChildItem "path to folder" -filter *.sql

foreach ($item in $dbNames) {
    foreach ($file in $configFiles)
    {
        write-Host "Processing $($file.PSPath)" -ForegroundColor CYAN
        (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("ReplaceMe"), "$databaseA" } | Set-Content $file.PSPath ## Replace to database name
        Invoke-SQLCMD -InputFile $file.PSPath -ServerInstance $ServerAddress
        (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("$databaseA"), "ReplaceMe" } | Set-Content $file.PSPath ## Reset to original
    }
}

Hope this helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.