pf-Sql

1.0.0.224

pf-Sql.ps1

function Get-Sql_Database_Script {
    # Load SMO Assembly
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

    # Connect to SQL Server Instance
    $serverName = "czchowsint1937.prg-dc.dhl.com,1525"
    $dbName = "UKMDataWarehouse"
    $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverName
    $database = $serverInstance.Databases[$dbName]

    # Define Scripting Options
    $scriptingOptions = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')
    $scriptingOptions.ScriptSchema = $True  # Include schema
    $scriptingOptions.ScriptData = $false     # Include data

    # Generate Scripts for All Tables
    foreach ($table in $database.Tables) {
        $script = $table.Script($scriptingOptions)
        $script | Out-File "C:\Path\To\Output\$($table.Name).sql"
    }
}

function Import-Module_sqlserver {
    if ( Get-Module -Name sqlserver ) {
        return
    }
    $sqlModule = Get-Module -ListAvailable | Where-Object name -eq sqlserver
    if ( -not $sqlModule ) { 
        Write-Warning 'sqlserver Module not available'
        return
    }
 
    # module sqlserver can change the current drive to sqlserver: ,
    # therefore this ensure the operation returns to the original location
    Push-Location
    try {
        $sqlModule | Import-Module -DisableNameChecking
    }
    finally {
        Pop-Location
    }
}

function New-SqlCommand($query, $inputfile, $QueryTimeout = [TimeSpan]::FromMinutes(10)) {
    if ( $inputfile ) {
        if ( -not ( Test-Path $inputfile ) ) {
            throw "File not found : '$inputfile' "
        }

        if ($query) {
            throw "Query will be overriden by : '$inputfile' "
        }

        Write-Host "SQL Executing : '$InputFile'"
        $query = Get-Content $inputfile -Raw
    }

    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.connection = $conn
    $cmd.commandtext = $query
    $cmd.CommandTimeout = $QueryTimeout.TotalSeconds
    return $cmd
}

function Invoke-SqlcmdAdoNetScalar ($dbServer, $db, $query) {
    
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$dbServer;Initial Catalog=$db;Integrated Security=SSPI;"
    try {
        $cmd = New-SqlCommand @PSBoundParameters
        $conn.open()
        $res = $cmd.ExecuteScalar()
        $res
    } finally {
        $conn.close()
    }
}
function Invoke-SqlcmdAdoNetScalar:::Example ($dbServer, $db, $query) {
    Invoke-SqlcmdAdoNetScalar -dbServer $SERVER.DB -db 'master' -query 'select count(*) from INFORMATION_SCHEMA.COLUMNS'
}

function Invoke-SqlcmdAdoNet ($dbServer, $db, $query, $inputfile) {
    
    $conn = New-Object System.Data.SqlClient.SqlConnection
    $conn.ConnectionString = "Data Source=$dbServer;Initial Catalog=$db;Integrated Security=SSPI;"
    try {
        $cmd = New-SqlCommand @PSBoundParameters
        $conn.open()
        $reader = $cmd.ExecuteReader()
        try {
            while ($reader.Read()) {
                $res = New-Object System.Object
                for ($i = 0; $i -lt $reader.FieldCount; $i++){
                    $res | Add-Member -type NoteProperty -name $reader.GetName($i) -value $reader.GetValue($i)
                }
                $res
            }
        }
        finally {
            $reader.Close()
        }
    } finally {
        $conn.close()
    }
}
function Invoke-SqlcmdAdoNet:::Example ($dbServer, $db, $query) {
    Invoke-SqlcmdAdoNet -dbServer $SERVER.DB -db 'master' -query 'select * from INFORMATION_SCHEMA.COLUMNS'
}

function Invoke-Sqlcmd_New ($Query, $Database, $ServerInstance, $InputFile, [switch]$scalar ) {
    if ($scalar) {
        Invoke-SqlcmdAdoNetScalar --dbServer $SERVER.DB -db $Database -query $Query 
    }
    else { 
        Invoke-SqlcmdAdoNet -dbServer $SERVER.DB -db $Database -query $Query -inputfile $InputFile
    }
}

function Invoke-SqlScript ($Query, $Database, $ServerInstance, $InputFile, [switch]$scalar ) {
    Import-Module_sqlserver
    if ($Database -is [Microsoft.SqlServer.Management.Smo.Database] ) {
        # Temporary Fix to SQLProvider WMI issue
        $PsBoundParameters.Database = $Database.Name
    }
    if ($SERVER.DB) {
        $PsBoundParameters.ServerInstance = $SERVER.DB
    }
    if ($InputFile) {
        Write-Host "Executing : '$InputFile'"
    }
    $timeout = [TimeSpan]'00:10:00'
    $result = sqlserver\Invoke-Sqlcmd @PsBoundParameters -QueryTimeout $timeout.TotalSeconds -Verbose -ErrorAction 'stop'

    if ($InputFile) {
        $msg = ConvertTo-Json -Depth 1 -InputObject $PsBoundParameters
        "$(Get-Date)`n$msg" >> ( $InputFile + ".log" )
    }

    if ( $scalar -and $result ) {
        return $result[0]
    }
    return $result
}

function Enable-SqlDevOptions {
    $sql = "
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        "
    Invoke-Sqlcmd_New -Query $sql
}

function Get-SqlInstance_Default {
    if ($SERVER.DB) {
        $servInstance = if ( $SERVER.DB.Contains('\') ) { $SERVER.DB } else { $SERVER.DB + '\DEFAULT' }
        return "SQLSERVER:\sql\" + $servInstance
    }
    throw 'Required SQL Server name and instance in $SERVER.DB'
}

function Get-SqlPathParts($path) {
    $path = Get-Path $path
    $path = $path | Update-Prefix "SQLSERVER:\"
    $path = $path.Split('\')
    function GetPathPart($i) {
        if ($path.Count -gt $i) {
            $path[$i]
        }
    }
    [PSCustomObject]@{
        Service   = GetPathPart 0
        Server    = GetPathPart 1
        Instance  = GetPathPart 2
        Area      = GetPathPart 3
        Container = GetPathPart 4
        Type      = GetPathPart 5
        Object    = GetPathPart 6
    }
}
function Get-SqlPathParts:::Test {
    Get-SqlPathParts "SQLSERVER:\sql\Cts-db-tst.MYTMBC.C360.PRIVATE\server\databases\appdb\tables"
    Get-SqlPathParts "SQLSERVER:\service\server"
}

function Get-SqlDatabaseList ([string[]]$dbNames = $UserDatabaseNamePatterns) {
    $dbList = ( Get-SqlInstance_Default ) + "\Databases" | Get-ChildItem
    $result = @()
    foreach ($dbName in $dbNames) {
        $result += $dbList | Where-Object name -like $dbName
    }
    return $result
}

function Get-SqlProcess ($dbName) {

    $sql = 'SELECT d.name as dbname, d.dbid ,spid ,login_time ,nt_domain as domain ,nt_username as username,loginame as loginName
            FROM master.dbo.sysprocesses p
            INNER JOIN master.dbo.sysdatabases d ON p.dbid = d.dbid '
    if ($dbName) { 
        $sql += "WHERE d.name = '$dbName' "
    }

    Invoke-Sqlcmd_New -query $sql
}
function Get-SqlProcess:::Example {
    $sqlProcessList  = Get-SqlProcess -db 'PortalDiagnostics'
    Write-Verbose $sqlProcessList
}

function Stop-SqlProcess {
    param ( 
        [Parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true,Mandatory=$true)]
        [int]$spID
    )
    process {
        if ($spID) {
            Invoke-Sqlcmd_New -query "kill $spID"
        }
    }
}
function Stop-SqlProcess:::Example {
    $sqlProcessList  = Get-SqlProcess -db 'PortalDiagnostics'
    $sqlProcessList.spid | Stop-SqlProcess
}

function Get-SqlBackUpFolder {
    $result = $SERVER.DBBackupFolder ?? "c:\backups"
    $result = Join-Path $result $SERVER.DB
    return $result
}

function CheckPoint-SqlDatabase ($label, [string[]]$dbNames = $UserDatabaseNamePatterns) {
    if (-not $label) {
        $label = Get-TimeStamp
    }
    
    $dbList = Get-SqlDatabaseList $dbNames

    $backUpBasePath = Get-SqlBackUpFolder
    $backUpBasePath = "$backUpBasePath\$label"
    New-Folder_EnsureExists $backUpBasePath

    foreach ($db in $DbList) {
        $backupPath = "$backUpBasePath\$($db.Name).bak"
        if (Test-Path $backupPath) {
            Remove-Item $backupPath -Force -Verbose
        }
        Backup-SqlDatabase -DatabaseObject $db -BackupFile $backupPath -CompressionOption On -BackupAction Database
    }
}
function CheckPoint-SqlDatabase:::Example {
    CheckPoint-SqlDatabase -label INIT_TBC_SP
}

function Restore-SqlCheckpoint ($label) {
    $backUpBasePath = Get-SqlBackUpFolder
    if (-not $label) {
        $lastBackupFolder = Get-ChildItem $backUpBasePath -directory | 
            Sort-Object LastWriteTime -Descending | Select-Object -First 1
        if (-not $lastBackupFolder) {
            Write-Warning 'No backups availables'
            return
        }
        $label = $lastBackupFolder.Name
    }
    $backUpBasePath = "$backUpBasePath\$label"
    if (-not (Test-Path $backUpBasePath) ) {
        Write-Warning "Backup not found '$backUpBasePath'"
    }
    $sqlInstancePath = Get-SqlInstance_Default

    $pathParts = Get-SqlPathParts $sqlInstancePath
    $sqlInstanceName = $pathParts.Server + "\" + $pathParts.Instance

    $dbNameList = Get-ChildItem $backUpBasePath -filter *.bak | 
        ForEach-Object { $_.Name | Update-Suffix '.bak' }

    foreach ( $dbName in $dbNameList ) {
        $backUpPath = "$backUpBasePath\$dbName.bak"
        $dbPath = "$sqlInstancePath\Databases\$dbName"

        Invoke-SqlSingleUser $dbPath -script {
            Restore-SqlDatabase -ServerInstance $sqlInstanceName -Database $dbName -BackupFile $backUpPath -ReplaceDatabase -Verbose -RestoreAction Database -RestrictedUser
        }
    }
}

function Invoke-SqlSingleUser($dbPath, [ScriptBlock]$script) {
    $dbPath = get-path $dbPath
    $dbname = Split-Path $dbPath -Leaf
    $sqlProcessList  = Get-SqlProcess -dbName $dbName
    $sqlProcessList.spid | Stop-SqlProcess

    $dbExists = Test-Path $dbPath
    if ($dbExists) {
        Invoke-Sqlcmd_New "ALTER DATABASE [$dbName] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE"
    }
    try {
        Invoke-Command -ScriptBlock $script
    }
    finally {
        if (Test-Path $dbPath) {
            Invoke-Sqlcmd_New "ALTER DATABASE [$dbName] SET MULTI_USER"
        }
    }
}

function Import-Module_SqlServer {
    Invoke-InLocation -path $env:TEMP -script {
        Import-Module sqlserver -Global
    }
}

function Compress-CorpAppDatabases {
    Import-Module_SqlServer
    $dbList = Get-SqlDatabaseList
    $dbList | Compress-Database
}
function Compress-CorpAppDatabases:::Example {
    Import-Module .\PSModules\Common -DisableNameChecking
    Import-Module VMProvision -DisableNameChecking
    Import-AppDeploySettings
    $PSDefaultParameterValues = @{ "New-SqlCommand:QueryTimeout" = [Timespan]::FromMinutes(100) }
    Compress-CorpAppDatabases
}

function Compress-Database {
    Param(
        [Parameter(ValueFromPipeline=$true)]
        $dbname
    )
    process {
        if (-not $dbname) { return }

        $dbnameRaw = $dbname | Update-Prefix '[' | Update-Suffix ']'
        $dbname = $dbnameRaw | Update-String_Enclose -prefix '[' -suffix ']' -conditional

        Write-Host "Compress-Database $dbname"
        
        $recoveryModel = Invoke-Sqlcmd_New -Query "SELECT name, recovCORP_model_desc FROM sys.databases WHERE name = '$dbnameRaw' ;" 
        $dblogfiles =  Invoke-Sqlcmd_New -Query "select name from $dbname.sys.database_files --where type_desc = 'LOG'"
        
        if ($dblogfiles) {
            $shrinkQuery = @("USE $dbname") + ($dblogfiles.name | 
                ForEach-Object { "DBCC SHRINKFILE(N'$_', 1 );" } ) -join "`n"
        
            if ($recoveryModel.recovCORP_model_desc -ne 'SIMPLE') {
                $shrinkQuery = "
                    ALTER DATABASE $dbname SET RECOVERY SIMPLE WITH NO_WAIT;
                    $shrinkQuery
                    ALTER DATABASE $dbname SET RECOVERY $($recoveryModel.recovCORP_model_desc) WITH NO_WAIT; "
            }

            $ignoredOutput = Invoke-Sqlcmd_New -Query $shrinkQuery;
            Write-Verbose $ignoredOutput
        }

    }
}

function Set-DatabaseRecoveryModel ($database, $recoveryModel = 'SIMPLE') {
    Write-Host "Set-DatabaseRecoveryModel $database to $recoveryModel"
    $sql = "
USE master ;
ALTER DATABASE $database SET RECOVERY $recoveryModel WITH NO_WAIT;
USE $database
DBCC SHRINKDATABASE ($database);
"
    Invoke-Sqlcmd_New $sql
}

function Set-DatabaseRecoveryModelBatch ($label, [string[]]$dbNames = $UserDatabaseNamePatterns) {
    $dbList = Get-SqlDatabaseList $dbNames
    foreach ($db in $DbList) {
        Set-DatabaseRecoveryModel -Database $db
    }
}
function Set-DatabaseRecoveryModelBatch:::Example {
    Set-DatabaseRecoveryModelBatch
}

function Get-SqlCmd_Variables {
    Param (
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
        $file
    ) 
    process {
        $lines = get-content $file
        $result = $lines | Select-RegEx "^\s*(?<comment>--)?\s*:Setvar\s+(?<variable>\w+)\s+(?<value>.+)"
        $result
    }
}
function Get-SqlCmd_Variables:::Example {
    $file = "pf-sql\puml.sql"
    $result = Get-SqlCmd_Variables -file $file
    $result[0].0 | assert -eq "??????????"
}

function Set-SqlCmd_VariableOverride {
    Param (
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
        $file,
        [hashtable]$Variables,
        $destination 
    ) 
    process {
        $currentVariables = Get-SqlCmd_Variables -file $file
        $missing = @()

        $replacements = $Variables.GetEnumerator() | ForEach-Object {  
            $varName = $_.Key
            $newLine = ":setVAR $varName $($_.Value)"
            $sqlcmdMatchList = $currentVariables | Where-Object { $_.variable -eq $varName -and -not $_.comment }
            if ($sqlcmdMatchList) {
                foreach ($sqlcmdCurrentVariable in $sqlcmdMatchList) {
                    $oldline = $sqlcmdCurrentVariable.0
                    [PSCustomObject]@{
                        ToReplace   = $oldline
                        Replacement = "--$oldline`n$newLine"
                    }
                }
            }
            else {
                $missing += $newLine
            }
        }
        $replacementsMap = $replacements | ConvertTo-HashTable -keyProp ToReplace -valueProp Replacement
        Update-FileContent_Replace_Multiple -All -replacements $replacementsMap `
            -Path $file -destination $destination
        
        if ($missing) {
            $missing +  (Get-Content $destination) | Set-Content $destination
        } 
    }
}

function Set-SqlCmd_VariableOverride:::Example {
    Set-SqlCmd_VariableOverride -file  "pf-sql\puml.sql" `
        -destination  "pf-sql\puml.generated.sql" `
        -variables @{ResultType = '"XXX"'; MisVar = "YYY"; MaxLevels=3}
}

function Invoke-Puml_Generation() {
    $sqlPwd = '1qaz.2WSX.'
    $sqluser = 'SA'
    $databaseName = 'AdventureWorks2017'
    $ConnectionString = "Server=localhost,1433;Database=$databaseName;User Id=$sqluser; Password=$sqlPwd";

    $fileSqlGenerated = "pf-sql\puml.generated.sql"
    
    Set-SqlCmd_VariableOverride -file  "pf-sql\puml.sql" `
        -destination $fileSqlGenerated `
        -variables @{
            ResultType = '"BiDirectionalRelationWithColumnsAndLabels"'
            MaxLevels = 3
        }

    $sqlResult = Invoke-Sqlcmd -ConnectionString $ConnectionString `
                    -InputFile $fileSqlGenerated `
                    -MaxCharLength ([int]::Maxvalue) -Variable $StringArray -OutputAs DataSet

    $tableSchemaJson = $sqlResult.Tables[0].Rows | ForEach-Object { $_[0] } | Join-String
    $tableSchema = $tableSchemaJson | ConvertFrom-Json
    $tableSchemaPuml = $tableSchema | ForEach-Object {
        "package $($_.TABLE_SCHEMA) { "
            $_.tables | ForEach-Object {
                "`tclass $($_.TABLE_NAME) { "
                $_.Columns | ForEach-Object {
                    "`t`t$($_.DATA_TYPE) $($_.COLUMN_NAME)"
                   }
                "`t}"
            }
        "}"
     } | Join-String -separator "`n"

    $outputlines = $sqlResult.Tables[1].Rows | ForEach-Object { $_.TableOne + $_.LabelOne + ' --|{ ' + $_.LabelMany + $_.TableMany }
    $tableRelationsPuml = $outputlines | Join-String -separator "`n"

    $templateContext = @{ 
        Schema = $tableSchemaPuml 
        Relations = $tableRelationsPuml 
    }

    Update-File_Replace_PSTemplate -file pf-Sql\Tests\diagram-puml.md.PSTemplate -TemplateContext $templateContext
}

function Start-SQL_Docker {
    $dw = '"C:\Program Files\Docker\Docker\frontend\Docker Desktop.exe" --name=settings'
    Test-Path $dw
    get-service -name com.docker.service
    get-service -name *docker*
    
    # Ensure you have a docker login first and it has been setup using the Docker Desktop App
    # Might need to sing out maually
    # $sqlDockerImage = 'microsoft/mssql-server-windows-developer'
    $sqlDockerImage = 'mcr.microsoft.com/mssql/server:2019-latest'
    $sqlContainerName = 'SQL19'
    $sqlPwd = '1qaz.2WSX.'
    $sqluser = 'SA' 
    
    docker run `
        --name $sqlContainerName `
        -p 1433:1433 `
        -e "ACCEPT_EULA=Y" `
        -e "SA_PASSWORD=$sqlPwd" `
        -v C:\Docker\SQL:/sql `
        -d $sqlDockerImage

    $sqlContainerIp = docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $sqlContainerName
    $sqlContainerId = docker inspect --format="{{.Id}}" $sqlContainerName
    
# docker kill $sqlContainerName
# docker rm $sqlContainerName

    if(-not (find-module sqlserver)) {
        install-module sqlserver -Scope AllUsers
    }
    Import-Module sqlserver -Global

    $databaseName = 'master'

    $ConnectionString = "Server=localhost,1433;Database=$databaseName;User Id=$sqluser; Password=$sqlPwd";
    $sql = 'SELECT @@SERVERNAME AS ServerName;';   
    Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql;

    # docker exec $sqlContainerName sudo yum install -y mssql-server-fts
    # docker exec $sqlContainerName sudo hostname
}

#Import-Module_sqlserver
$UserDatabaseNamePatterns = @('QA*', 'PROD*', 'DEV*','ReportServer*')