PowerShell: Backup, Archive, and Remove a Microsoft SQL Database

$sqlServer='sqlsherver007'
$databaseName="someDatabaseName"
$saCredential=get-credential
$backupDestination="\\Archive03\MSSQL_Backups"

function triggerSqlBackup($sqlServer,$databaseName){
    # Ensure the the Jump Box has SQL PowerShell tools
    $moduleName='SqlServer'    
    if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        if(!('NuGet' -in (get-packageprovider).Name)){    
            try{
                Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                }
            catch{               
                write-warning "$error"
                }            
            }
        Install-Module -Name $moduleName -Force -Confirm:$false
        Import-Module $moduleName
        }

    # Trigger backup of target database into the default backup directory
    $databaseObject=Get-SqlDatabase -ServerInstance $sqlServer|?{ $_.Name -eq $databaseName }
    Backup-SqlDatabase -DatabaseObject $databaseObject -CompressionOption On -CopyOnly -Verbose     
}

function invokeGetDefaultSqlPaths($sqlServer,$saCredential){
    $defaultValues=invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
        # $sqlConnection.Databases.Name
        $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
        $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
        $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
        return @{'data'=$defaultDataDirectory;'log'=$defaultLogDirectory;'backup'=$defaultBackupDirectory}
        }
    if($defaultValues){return $defaultValues}
    else{return $false}
}

function convertLocalToUnc($localPath,$computername){    
    $uncPath=.{$x=$localPath -replace "^([a-zA-Z])\:","\\$computername\`$1`$";
                if($x -match '\\$'){return $x.Substring(0,$x.length-1)}else{return $x}
                }
    $validLocal=if($localPath){test-path $localPath -ErrorAction SilentlyContinue}else{$false}
    $validUnc=if($uncPath){test-path $uncPath -ErrorAction SilentlyContinue}else{$false}
    if($validUnc){write-host "$uncPath is reachable, using credentials of $(whoami)"}
    else{write-warning "$computername is unreachable, using credentials of $(whoami)"}
    return $uncPath
}

function removeDatabase($sqlserver,$databasename,$saCredential){
    $ErrorActionPreference='Stop'
    
    function confirmation($content,$testValue="I confirm",$maxAttempts=3){
        $confirmed=$false;
        $attempts=0;        
        $content|write-host
        write-host "Please review this content for accuracy.`r`n"
        while ($attempts -le $maxAttempts){
            if($attempts++ -ge $maxAttempts){
                write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                break;
                }
            $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm";
            if ($userInput.ToLower() -ne $testValue.ToLower()){
                cls;
                $content|write-host
                write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again..`r`n"
                }else{
                    $confirmed=$true;
                    write-host "Confirmed!`r`n";
                    break;
                    }
            }
        return $confirmed;
    }
    
    # Ensure the the Jump Box has SQL PowerShell tools
    $moduleName='SqlServer'    
    if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        if(!('NuGet' -in (get-packageprovider).Name)){    
            try{
                Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                }
            catch{               
                write-warning "$error"
                }            
            }
        Install-Module -Name $moduleName -Force -Confirm:$false
        Import-Module $moduleName
        }

    $dropDatabase="DECLARE @DatabaseName nvarchar(50)
        SET @DatabaseName = N'$databasename'
        DECLARE @SQL varchar(max)
        SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
        FROM MASTER..SysProcesses
        WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId        
        --SELECT @SQL 
        EXEC(@SQL)
        DROP DATABASE $databasename
    "
    $removeDatabase="invoke-sqlcmd -ServerInstance '$sqlserver' -Query `"$dropDatabase`""
    #$removeDatabase="invoke-sqlcmd -ServerInstance '$sqlserver' -Query 'Drop database $databasename'"
    $confirm=confirmation $removeDatabase
    if($confirm){
        Try{
            Invoke-Expression $removeDatabase -ea Stop
            $databaseNames=Invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{
                [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
                $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
                return $sqlConnection.Databases.Name
                } -ea Stop
            if($databasename -notin $databaseNames){
                write-host "Database $databaseName NO longer exists on $sqlserver"
                return $true
                }
            else{
                write-warning "Database $databaseName STILL exists on $sqlserver"
                return $false
                }            
            }
        Catch{
              write-host "$($error[0])"
              Write-Warning "Failed to delete database $databasename"
              return $false
            }
    }
    #invoke-sqlcmd : Cannot drop database because it is currently in use.
    # Msg 3702, Level 16, State 4, Procedure , Line 1.
    #At line:1 char:1
    #+ invoke-sqlcmd -ServerInstance -Query 'Drop database Pre ...
    #+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    #    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    #    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
}

function archiveDatabaseBackupFile ($sqlServer,$databaseName,$saCredential,$backupDestination) {
    $defaultPaths=invokeGetDefaultSqlPaths $sqlServer $saCredential
    $defaultBackupLocation=$defaultPaths.backup
    $defaultBackupUncPath=convertLocalToUnc $defaultBackupLocation $sqlServer
    $backupFileName="$databaseName.bak"
    $copyCommand="robocopy '$defaultBackupUncPath' '$backupDestination' '$backupFileName'"
    Invoke-Expression $copyCommand
    $creationTime=(Get-Item "$defaultBackupUncPath\$backupFileName").CreationTime
    $timeStamp=[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($creationTime,'Pacific Standard Time').tostring("MM-dd-yyyy-HHmm")+'_PST'
    Rename-Item -path "$backupDestination\$backupFileName" -newname "$backupDestination\$databaseName`_$timeStamp.bak"
    Remove-Item "$defaultBackupUncPath\$backupFileName"  #Cleanup backup file on the server
}

function backupArchiveAndRemoveDatabase ($sqlServer,$databaseName,$saCredential,$backupDestination) {
    triggerSqlBackup $sqlServer $databaseName
    archiveDatabaseBackupFile $sqlServer $databaseName $saCredential $backupDestination
    removeDatabase $sqlServer $databaseName $saCredential
}

backupArchiveAndRemoveDatabase $sqlServer $databaseName $saCredential $backupDestination