SifHelper

1.1.0.135

Public/Tasks/Invoke-DropSqlDatabaseTask.ps1

#Requires -Modules SitecoreInstallFramework, SitecoreFundamentals, SqlServer

Set-StrictMode -Version Latest

Write-Verbose "Loading $($MyInvocation.MyCommand.Path)"

Function Invoke-DropSqlDatabaseTask {
    [System.Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")]
    [CmdletBinding(SupportsShouldProcess = $true)]
    param
    (
        [Parameter(Mandatory = $true)]
        [string]$SqlServer,
        [Parameter(Mandatory = $true)]
        [string[]]$SqlDatabases,
        [Parameter(Mandatory = $true)]
        [string]$SqlAdminUser,
        [Parameter(Mandatory = $true)]
        [string]$SqlAdminPassword,
        [string]$TaskName = "DropSqlDatabase"
    )

    Set-PSDebug -Strict

    function Test-SqlDatabase {

        param
        (
            [Parameter(Mandatory = $true)]
            [string]$SqlServer,
            [Parameter(Mandatory = $true)]
            [string]$SqlDatabase,
            [Parameter(Mandatory = $true)]
            [string]$SqlAdminUser,
            [Parameter(Mandatory = $true)]
            [string]$SqlAdminPassword
        )

        $cmdCheckDb = @"
        IF EXISTS (SELECT 1
                FROM [master].[dbo].[sysdatabases]
                WHERE [name] = N'$($SqlDatabase)')
        BEGIN
            SELECT 1 as result
        END
        ELSE
        BEGIN
            SELECT 0 as result
        END
"@

        try {
            $cmdresult = Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Username $SqlAdminUser -Password $SqlAdminPassword -Query $cmdCheckDb -ErrorAction Stop
            $resultvalue = $false

            if ($cmdresult.result -eq 1) {
                $resultValue = $true
            }

            $result = New-Object PSCustomObject -Property @{
                "result" = $resultvalue
            }
        }
        catch {
            $result = New-Object PSCustomObject -Property @{
                "result" = $false
            }
        }

        return $result.result

    }

    function Remove-SqlDatabase {
        [CmdletBinding(SupportsShouldProcess = $true)]
        param
        (
            [Parameter(Mandatory = $true)]
            [string]$SqlServer,
            [Parameter(Mandatory = $true)]
            [string]$SqlDatabase,
            [Parameter(Mandatory = $true)]
            [string]$SqlAdminUser,
            [Parameter(Mandatory = $true)]
            [string]$SqlAdminPassword
        )

        if ($PSCmdlet.ShouldProcess($SqlDatabase)) {
            $dbExists = Test-SqlDatabase -SqlServer $SqlServer -SqlDatabase $SqlDatabase -SqlAdminUser $SqlAdminUser -SqlAdminPassword $SqlAdminPassword

            if ($dbExists) {

                Write-TaskInfo -Message "[$SqlDatabase] exists. Attempting removal." -Tag 'SQL' -TaskName $TaskName

                $cmdDropDb = "
            EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'[$($SqlDatabase)]'
            GO
 
            USE [master]
            GO
            ALTER DATABASE [$($SqlDatabase)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
            GO
 
            USE [master]
            GO
 
            DROP DATABASE [$($SqlDatabase)]
            GO"

                $msg = "Executing SQL Query: '$($cmdDropDb)'"

                Write-Debug $msg

                Invoke-Sqlcmd -ServerInstance $SqlServer -Database master -Username $SqlAdminUser -Password $SqlAdminPassword -Query $cmdDropDb -ErrorAction Stop

                $dbExists = Test-SqlDatabase -SqlServer $SqlServer -SqlDatabase $SqlDatabase -SqlAdminUser $SqlAdminUser -SqlAdminPassword $SqlAdminPassword

                if ($dbExists) {
                    Write-Warning -Message "[$SqlDatabase] still exists."
                    Write-TaskInfo -Message "[$SqlDatabase] still exists." -Tag 'SQL' -TaskName $TaskName
                }
                else {
                    Write-TaskInfo -Message "[$SqlDatabase] dropped successfully" -Tag 'SQL' -TaskName $TaskName
                }
            }
            else {
                Write-TaskInfo -Message "[$SqlDatabase] does not exist. No action required." -Tag 'SQL' -TaskName $TaskName
            }
        }
    }

    foreach ($SqlDatabase in $SqlDatabases) {
        Remove-SqlDatabase $SqlServer $SqlDatabase $SqlAdminUser $SqlAdminPassword
    }

}

Register-SitecoreInstallExtension -Command Invoke-DropSqlDatabaseTask -As DropSqlDatabase -Type Task -Force

Write-Verbose "Loaded $($MyInvocation.MyCommand.Path)"