PowerShellScript

.ps1 script

SqlDatabase supports powershell scripts for commands execute, create and upgrade.

Example:

$ SqlDatabase execute ^
      "-database=Data Source=server;Initial Catalog=database;Integrated Security=True" ^
      -from=c:\script.ps1

PS> Execute-SqlDatabase `
      -database "Data Source=server;Initial Catalog=database;Integrated Security=True" `
      -from c:\script.ps1 `
      -InformationAction Continue

script.ps1:

[CmdletBinding(SupportsShouldProcess=$true)] # indicates that the script implementation supports -WhatIf scenario
param (
    $Command, # instance of SqlCommand, $null in case -WhatIf
    $Variables # access to variables
)

if (-not $Variables.TableName) {
    throw "Variable TableName is not defined."
}

if ($WhatIfPreference) {
    # handle -WhatIf scenario
    return
}

Write-Information "start execution"

$Command.CommandText = ("print 'current database name is {0}'" -f $Variables.DatabaseName)
$Command.ExecuteNonQuery()

$Command.CommandText = ("drop table {0}" -f $Variables.TableName)
$Command.ExecuteNonQuery()

Write-Information "finish execution"

use

• cmdlet parameter binding
• parameter $Command to affect database
• parameter $Variables to access variables
• Write-* to write something into output/log
• SupportsShouldProcess=$true and $WhatIfPreference if script supports -WhatIf scenario

Which version of PowerShell is used to run .ps1

SqlDatabase powershell module

The version with which you run the module.

.net framework 4.7.2

Installed Powershell Desktop version.

.net SDK tool for .net 6.0+

Pre-installed Powershell Core is required, will be used by SqlDatabase as external component. Due to the Powershell Core design:

• SqlDatabase .net 8.0 can host Powershell Core versions below 7.5
• SqlDatabase .net 7.0 can host Powershell Core versions below 7.4
• SqlDatabase .net 6.0 can host Powershell Core versions below 7.3

see PowerShell release history.

PowerShell location can be passed via command line:

$ SqlDatabase execute ^
      -usePowerShell=C:\Program Files\PowerShell\7

$ dotnet SqlDatabase.dll create ^
      -usePowerShell=/opt/microsoft/powershell/7

PowerShell location by default:

• if SqlDatabase is running by PowerShell (parent process is PowerShell) and version is compatible, use this version
• check well-known installation folders: C:\Program Files\PowerShell on windows and /opt/microsoft/powershell on linux, use latest compatible version

Scripts isolation

For each .ps1 script, executing by SqlDatabase

• create new PowerShell session with default cmdlets, providers, built-in functions, aliases etc.
• run script as script block
• destroy the session