Blog: Using PowerShell Invoke-Sqlcmd with Variable parameter

I recently needed to use PowerShell to call a T-SQL script using Invoke-Sqlcmd with the -Variable parameter. In my case, I wanted to specify the locations for backup files and where to restore the data and log files. Rather than add the conditions into the SQL script, I wanted to make the SQL script as generic as possible, and use SQLCMD variables for the different scenarios. In this blog, I’ll show how to use the array sub-expression operator and “splatting” with hash tables to build the parameters needed for the Invoke-Sqlcmd cmdlet.

Isn’t using Powershell Invoke-Sqlcmd with the -Variable parameter straightforward?

Well, not really. There are some forum posts out there that cover the topic with some issues, and the help topic from Microsoft was confusing, at least for me. For instance, in Example 3 for the documentation for Invoke_Sqlcmd, it shows using a string array and then a -Query SELECT statement like this:

$StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

The problem with this example is the use of the ` backtick, backquote, or grave accent character in the SQL statement. This leads to the impression that you should do the same in your SQL script.

SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2

Needless to say, the T-SQL interpreter doesn’t know what to do with the backtick character and throws an error! It turns out that backtick is an escape character for PowerShell so that the use of $(MYVAR1) doesn’t throw an error. The pesky backtick character is not just for line continuations.

Maintaining different variations of .SQL scripts is a pain

In my scenario, I like to use HammerDB with the TPC-C benchmark for practicing monitoring and troubleshooting scenarios on my local machine and cloud platforms like AWS and Azure. On my local machine or cloud snapshot drive, I have a folder in the root directory that contains several different sizes and modifications for comparing results to a baseline. For example:

C:\Backup\  contains files with name patterns like
TPCC_HammerDB3.3_10.bak
TPCC_HammerDB3.3_1000.bak
TPCC_HammerDB3.3_2000.bak
TPCC_NewIndexTest1_10.bak
TPCC_NewIndexTest1_1000.bak
TPCC_NewIndexTest1_2000.bak

Before, I would create multiple SQL files for each scenario and then run the appropriate SQL file for the test scenario. This quickly got out of control. Here is an example of one of my original scripts.

RESTORE DATABASE [tpcc]
FROM DISK = N'G:\BACKUP\TPCC_HammerDB3.3_10.bak'
WITH FILE = 1,
MOVE N'tpcc' TO N'E:\DATA\TPCC_HammerDB3.3_10.mdf',
MOVE N'tpcc_log'
TO N'L:\LOG\TPCC_HammerDB3.3_10.ldf',
NOUNLOAD, STATS = 5, MAXTRANSFERSIZE = 4194304;
GO

Creating a SQL file that drops the current database if it exists and then does the restore operation with SQLCMD variables is straight forward.

:on error exit

-- Drop the database if it already exists
USE master
GO
IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (name = '$(DBName)')))
BEGIN
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'$(DBName)';
ALTER DATABASE [$(DBName)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DBName)];
END
GO

RESTORE DATABASE [$(DBName)]
FROM
DISK = N'$(BackupPath)'
WITH FILE = 1
, MOVE N'$(DBName)' TO N'$(DataDrv)'
, MOVE N'$(DBName)_log' TO N'$(LogDrv)'
, NOUNLOAD
, STATS = 5
, MAXTRANSFERSIZE = 4194304;
GO

However, do to the various pieces of guidance, I had a frustrating time trying to get this to work. Hence this blog post.

Using a hash table for the Invoke-Sqlcmd variable parameter

Real PowerShell developers will tell you, one of the best ways to pass parameters to a cmdlet based on different conditions is to use Splatting. Huh? How did splatting become a technical term?

The PowerShell documentation has a great definition of the Splatting feature.

Splatting is a method of passing a collection of parameter values to a command as a unit. PowerShell associates each value in the collection with a command parameter. Splatted parameter values are stored in named splatting variables, which look like standard variables, but begin with an At symbol (@) instead of a dollar sign ($). The At symbol tells PowerShell that you are passing a collection of values, instead of a single value.

Perfect! I already have all the Powershell variables needed for building the SQLCMD variables. So, here is my version of using Invoke-Sqlcmd with -variable parameters using PowerShell arrays, hash tables, and splatting.

# Setup the parameters for a specific test run.
param
(
[string]$Cloud = "local", # Use aws, azure, gcp, or local
[string]$Benchmark = "tpcc", # Also DB name
[string]$Scenario = "HammerDB3.3",
[string]$BackupDrv = "G",
[string]$DataDrv = "E",
[string]$LogDrv = "L",
[string]$Warehouses = "10",
[string]$sql_instance_name = "BILL\SQLEXPRESS"
)

The next step is to create an array of the SQLCMD “variable names = values” strings to define the database name, backup file path, data, and log file names.

In this example, I’m using the array sub-expression operator @( ... ).

$SqlcmdVariables= @(
"DBName=$Benchmark",
"BackupPath=$($BackupDrv):\Backup\$($Benchmark)_$($Scenario)_$($Warehouses).bak",
"DataDrv=$($DataDrv):\DATA\$($Benchmark)_$($Scenario)_$($Warehouses).mdf",
"LogDrv=$($LogDrv):\LOG\$($Benchmark)_$($Scenario)_$($Warehouses).ldf"
)

Assuming no typos, here is what the result looks after running this code snippet and then typing $SqlcmdVariables to view the array of strings.

PS C:\GitHub\dbt-power-bi-for-sql-dbas> $SqlcmdVariables
DBName=tpcc
BackupPath=G:\Backup\tpcc_HammerDB3.3_10.bak
DataDrv=E:\DATA\tpcc_HammerDB3.3_10.mdf
LogDrv=L:\LOG\tpcc_HammerDB3.3_10.ldf

Now, I’ll create a hash table for the Invoke-Sqlcmd variable parameter and all the other parameters. I’m using a modified example of Example 3 from the Invoke-Sqlcmd web page with the Query parameter. In this example, I’m using the create hash table syntax @{ <name> = <value>; [<name> = <value> ] ...}

$SqlcmdParameters = @{
ServerInstance = $sql_instance_name
QueryTimeout = 0
Query = "SELECT '`$(DBName)' AS DBName, '`$(BackupPath)' AS BackupPath, '`$(DataDrv)' AS DataDrv, '`$(LogDrv)' AS LogDrv"
Verbose = $true
Variable = $SqlcmdVariables
}

Notice that I used the $SqlcmdVariables array as the value for the Variable key for the hash table. By typing in the PowerShell command $SqlParameters, you’ll see a different looking output than $SqlcmdVariables.

PS C:\GitHub\dbt-power-bi-for-sql-dbas> $SqlcmdParameters

Name           Value
----           -----
Query          SELECT '$(DBName)' AS DBName, '$(BackupPath)' AS BackupPath, '$(DataDrv)' AS DataDrv, '$(LogDrv)' AS LogDrv
ServerInstance BILL\SQLEXPRESS
Variable       {DBName=tpcc, BackupPath=G:\Backup\tpcc_HammerDB3.3_10.bak, DataDrv=E:\DATA\tpcc_HammerDB3.3_10.mdf, LogDrv=L:\LOG\tpcc_HammerDB3.3_10.ldf}
Verbose        True
QueryTimeout   0

Because $SqlcmdParameters is a hash table, the result comes back in name and value pairs.

Why real Powershell developers like splatting

I can now use the hash table by taking advantage of the splat feature using @SqlcmdParameters in PowerShell to test variable substitution for the test query.

Invoke-Sqlcmd @SqlcmdParameters

The output should look like this.

DBName BackupPath                        DataDrv                         LogDrv
------ ----------                        -------                         ------
tpcc   G:\Backup\tpcc_HammerDB3.3_10.bak E:\DATA\tpcc_HammerDB3.3_10.mdf L:\LOG\tpcc_HammerDB3.3_10.ldf

That was easy, but so what?

Now that the parameters and their values needed to execute the Invoke-Sqlcmd cmdlet are in a hash table, you can use the Add and Remove methods to change them.

Since I really want to run my restore database script, I need to remove the -Query parameter and add the -InputFile parameter like this.

# Delete the Query key value with Remove method
$SqlcmdParameters.Remove("Query")

# Add the Inputfile key value pair with the Add Method
$SqlcmdParameters.Add('InputFile', '.\Restore-Database-Sqlcmd.sql')

# Sanity check
$SqlcmdParameters

Here is what you should expect to see.

PS C:\GitHub\dbt-power-bi-for-sql-dbas> $SqlcmdParameters

Name           Value
----           -----
ServerInstance BILL\SQLEXPRESS
Variable       {DBName=tpcc, BackupPath=G:\Backup\tpcc_HammerDB3.3_10.bak, DataDrv=E:\DATA\tpcc_HammerDB3.3_10.mdf, LogDrv=L:\LOG\tpcc_HammerDB3.3_10.ldf}
InputFile      .\Restore-Database-Sqlcmd.sql
Verbose        True
QueryTimeout   0

Nice. You can perform Add and Replace on hash tables, but no Update? There is no mention of this in the hash table documentation. Let’s say you want to change the Verbose parameter to false. Sure, you could remove it and then add it back. But, you don’t need to do that. Here is the trick.

# Updating a parameter value
$SqlcmdParameters.Verbose = $false
Write-Host $SqlcmdParameters.Verbose

You can reference any value for a key by just using the name of the key!

Putting it all together

With the -InputFile parameter set, you can use the same Invoke-Sqlcmd command line.

Invoke-Sqlcmd @SqlcmdParameters

If all goes well, you should see a successful restore database output.

Let’s use our newfound hash table and splatting knowledge to the test. In this scenario, I’ve added back the -Query parameter and added the -Database parameter to display the Logical and Physical files.

# Change the Parameters in the hash table
$SqlcmdParameters.Remove("InputFile")
$SqlcmdParameters.Add("Database", "master")
$SqlcmdParameters.Add("Query", "SELECT name as [Logical Name], physical_name AS [File Location] FROM sys.master_files WHERE name LIKE ('$($Benchmark)%')")

Invoke-Sqlcmd @SqlcmdParameters

Notice how I used the $Benchmark variable in the query string to build the LIKE clause. If all went well, you should see the following results.

PS PS C:\GitHub\dbt-power-bi-for-sql-dbas> Invoke-Sqlcmd @SqlcmdParameters

Logical Name File Location
------------ -------------
tpcc         E:\DATA\tpcc_HammerDB3.3_10.mdf
tpcc_log     L:\LOG\tpcc_HammerDB3.3_10.ldf

I hope that you find this post useful.