PowerShell.PowerLibrary.SqlDatabaseMaintenance

1.0

PowerShell.PowerLibrary.SqlDatabaseMaintenance.psm1

#region Variables
$OutputSqlErrors = $($ErrorActionPreference -eq 'Continue' -or $ErrorActionPreference -eq 'Stop');
#endregion

#region Private Methods
FUNCTION Invoke-SqlCmdInternal
{
    [CmdletBinding()]
    param
    ( 
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true)]
        [string]
        $Query,

        
        [Parameter(Mandatory = $true, Position = 1, ValueFromPipeline = $true)]
        [string]
        $Database,

        [Parameter(Mandatory = $true, Position = 2, ValueFromPipeline = $true)]
        [System.Collections.Generic.Dictionary[string, object]]
        $InvocationBoundParameters
    );

    Invoke-Sqlcmd `
    -ErrorAction $ErrorActionPreference `
    -AbortOnError:$false `
    -OutputSqlErrors:$OutputSqlErrors `
    -QueryTimeout $InvocationBoundParameters.SqlQueryTimeout `
    -Query $Query `
    -ServerInstance $InvocationBoundParameters.ServerName `
    -Username $InvocationBoundParameters.UserName `
    -Password $InvocationBoundParameters.Password `
    -Database master;
}
#endregion

#region Set-DatabaseRecoveryMode
FUNCTION Set-DatabaseRecoveryMode
{
    <#
    .Synopsis
        Sets Database Recovery Mode.
    .DESCRIPTION
        Sets Database Recovery Mode. Simple, Full or BULK_LOGGED.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .PARAMETER RecoveryMode
        SIMPLE | FULL. Default: SIMPLE
    .EXAMPLE
        Set-DatabaseRecoveryMode -N Portal -M SIMPLE;
    .NOTES
        Query Executed: Alter Database [$DatabaseName] SET RECOVERY $RecoveryMode;"
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (    
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName,   
             
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('M')]
        [ValidateSet('SIMPLE','FULL', 'BULK_LOGGED')]
        [string]
        $RecoveryMode = 'SIMPLE'
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }

    BEGIN 
    {        
        Write-Host "Setting Database Recovery Mode Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Setting Database " -NoNewline -ForegroundColor Yellow;
                Write-Host $__ -NoNewline -ForegroundColor White;
                Write-Host " Recovery Mode to " -NoNewline -ForegroundColor Yellow;
                Write-Host $RecoveryMode -NoNewline -ForegroundColor White;
                Write-Host " ..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ DatabaseName = $__; RecoveryMode = $RecoveryMode; };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        } 
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Setting Database Recovery Mode Process Ended" -ForegroundColor DarkCyan;        
    }
}
#endregion

#region Set-DatabaseAuthorization
FUNCTION Set-DatabaseAuthorization
{
    <#
    .Synopsis
        Sets Database Authorization.
    .DESCRIPTION
        Sets Database Authorization.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .PARAMETER AuthorizationUserName
        Database Authorization User Name.
    .EXAMPLE
        Set-DatabaseAuthorization -N Portal -M SIMPLE;
    .NOTES
        Query Executed: ALTER Authorization ON database::[$DatabaseName] to [$AuthorizationUserName]
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $AuthorizationUserName
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }
    
    BEGIN
    {
        Write-Host "Setting Database Authorization Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Altering Authorization on " -NoNewline -ForegroundColor Yellow;
                Write-Host $DatabaseName -NoNewline -ForegroundColor White;
                Write-Host " to " -NoNewline -ForegroundColor Yellow;
                Write-Host $AuthorizationUserName -NoNewline -ForegroundColor White;
                Write-Host " ..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ DatabaseName = $__; UserName = $AuthorizationUserName; };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        } 
    }
    
    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Setting Database Authorization Process Ended" -ForegroundColor DarkCyan;
    }
}
#endregion

#region Drop-Database
Set-Alias -Name Start-DatabaseDrop -Value Remove-Database;
Set-Alias -Name Drop-Database -Value Remove-Database;
FUNCTION Remove-Database
{
    <#
    .Synopsis
        Drops one or more database(s).
    .DESCRIPTION
        Drops one or more database(s).
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .EXAMPLE
        Start-DatabaseDrop 'Portal';
    .NOTES
        Query Executed:
        EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Portal'
        GO
        USE [master]
        GO
        IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = 'Portal' OR name = 'Portal')))
        BEGIN
            ALTER DATABASE [Portal] SET single_user WITH ROLLBACK IMMEDIATE;
            DROP DATABASE [Portal];
        END
        GO
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }
    
    BEGIN
    {
        Write-Host "Dropping Database(s) Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Dropping $DatabaseName..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ DatabaseName = $__; };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        }
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Dropping Database Process Ended" -ForegroundColor DarkCyan;
    }
}
#endregion

#region Restore-Database
Set-Alias -Name Start-DatabaseRestore -Value Restore-Database;
FUNCTION Restore-Database
{
    <#
    .Synopsis
        Drops one or more database(s), and then Restore them.
    .DESCRIPTION
        Drops one or more database(s), and then Restore them.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .PARAMETER BackupPath
        Database Backup Path.
    .PARAMETER DataPath
        Database Data Path.
    .PARAMETER LogPath
        Database Log Path.
    .EXAMPLE
        $DatabaseName.Values | `
        Start-DatabaseRestore `
        -BackupPath "C:\Program Files\Microsoft SQL Server\Repository\Backup" `
        -DataPath "C:\Program Files\Microsoft SQL Server\Repository\Data" `
        -LogPath "C:\Program Files\Microsoft SQL Server\Repository\Log" `
        ;
    .NOTES
        Query Executed:
        USE [master]
        GO
        RESTORE DATABASE [@Model.DatabaseName]
        FROM DISK = N'@Model.BackupPath\@Model.DatabaseName.bak'
        WITH FILE = 1,
        MOVE N'@Model.DatabaseName' TO N'@Model.DataFileName',
        MOVE N'@Model.LogName' TO N'@Model.LogFileName',
        NOUNLOAD,
        REPLACE,
        STATS = 5
        GO
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $BackupPath,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $DataPath,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $LogPath
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }
    
    BEGIN
    {
        Write-Host "Restoring Database(s) Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Restoring $DatabaseName..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ 
                DatabaseName = $__; 
                BackupPath = $BackupPath;
                DataFileName = "$DataPath\$__.mdf";
                LogFileName = "$LogPath\$__.ldf";
                LogName = "$($__)_log";
                };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        }
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Restoring Database Process Ended" -ForegroundColor DarkCyan;
    }  
}
#endregion

#region Compress-DatabaseLog
Set-Alias -Name Start-DatabaseLogShrink -Value Compress-DatabaseLog;
Set-Alias -Name Shrink-DatabaseLog -Value Compress-DatabaseLog;
FUNCTION Compress-DatabaseLog
{
    <#
    .Synopsis
        Drops one or more database(s), and then Restore them.
    .DESCRIPTION
        Drops one or more database(s), and then Restore them.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .EXAMPLE
        Start-DatabaseLogShrink 'Portal';
    .NOTES
        Query Executed:
        Alter Database [@Model.DatabaseName] SET RECOVERY SIMPLE;
        GO
        USE [@Model.DatabaseName]
        GO
        DBCC SHRINKFILE (N'@Model.LogName' , 0, TRUNCATEONLY)
        GO
        Alter Database [@Model.DatabaseName] SET RECOVERY FULL;
        GO
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }

    BEGIN
    {
        Write-Host "Database Log Shrink Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Shrinking Database Log $DatabaseName..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ 
                DatabaseName = $__; 
                LogName = "$($__)_log";
                };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        }
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Database Log Shrink Process Ended" -ForegroundColor DarkCyan;        
    }   
}
#endregion

#region Backup-Database
Set-Alias -Name Start-DatabaseBackup -Value Backup-Database;
FUNCTION Backup-Database
{
    <#
    .Synopsis
        Drops one or more database(s), and then Restore them.
    .DESCRIPTION
        Drops one or more database(s), and then Restore them.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .PARAMETER BackupPath
        Database Backup Path.
    .EXAMPLE
        Start-DatabaseBackup -DatabaseName 'Portal' -BackupPath;
    .NOTES
        Query Executed:
        BACKUP DATABASE [@Model.DatabaseName] TO DISK = N'@Model.BackupPath\@Model.DatabaseName.bak' WITH NOFORMAT, INIT, NAME = N'@Model.DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
        GO
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $BackupPath,
                
        [Parameter(Mandatory = $false, ValueFromPipeline = $true)]
        [switch]
        $UniqueBackupName
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }
    
    BEGIN
    {
        Write-Host "Database Backup Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Backing up Database: $DatabaseName..." -ForegroundColor Yellow;
                $BackupName = $__;
                IF($UniqueBackupName.IsPresent)
                {
                    $BackupName = "$($__)-$([datetime]::Now.ToString('yyyy-MM-dd-hh-mm-ss'))";
                }
                [PSObject]$Model = @{ 
                DatabaseName = $__; 
                BackupName = $BackupName;
                BackupPath = $BackupPath;
                };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        }
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Database Backup Process Ended" -ForegroundColor DarkCyan;
    }   
}
#endregion

#region Repair-SSB
Set-Alias -Name Fix-SSB -Value Repair-SSB;
FUNCTION Repair-SSB
{
    <#
    .Synopsis
        Drops one or more database(s), and then Restore them.
    .DESCRIPTION
        Drops one or more database(s), and then Restore them.
    .PARAMETER SqlQueryTimeout
        Server Name. Default: 0
    .PARAMETER ServerName
        Server Name. Default: (local)
    .PARAMETER UserName
        SQL User Name. Default: sa
    .PARAMETER Password
        SQL User Name Password.
    .PARAMETER DatabaseName
        Database Name.
    .PARAMETER AuthorizationUserName
        Database Authorization User Name.
    .EXAMPLE
        Start-DatabaseLogShrink 'Portal';
    .NOTES
        Query Executed:
        USE [Master]
        GO
        GRANT EXTERNAL ACCESS ASSEMBLY TO [@Model.UserName]
        GO
        ALTER Authorization on Database::[@Model.DatabaseName] to [@Model.UserName];
        GO
        ALTER DATABASE [@Model.DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        GO
        ALTER DATABASE [@Model.DatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
        GO
        ALTER DATABASE [@Model.DatabaseName] SET TRUSTWORTHY ON;
        GO
        ALTER DATABASE [@Model.DatabaseName] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
        GO
        ALTER DATABASE [@Model.DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
        GO
        sp_configure 'clr enabled';
        GO
        RECONFIGURE
        GO
        USE [@Model.DatabaseName]
        Go
        ALTER ASSEMBLY [??????.ClrProcedures] WITH PERMISSION_SET = UNSAFE;
        GO
        RECONFIGURE
        GO
    #>

    [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')]
    param
    (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias('N')]
        [string[]]
        $DatabaseName,
        
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]
        $AuthorizationUserName
    );

    DynamicParam { return (Get-DynamicParameters "$PSScriptRoot\CommonParameters.ps1"); }

    BEGIN
    {
        Write-Host "Fix SSB Process Started" -ForegroundColor Cyan;
        $QueryBuilder = [System.Text.StringBuilder]::new();
        $TemplateFile = "$PSScriptRoot\Templates\$($MyInvocation.MyCommand.Name).sql.template" ;
        $Confirmed = $false;
    }

    PROCESS
    {
        FOREACH($__ in $DatabaseName)
        {
            IF($PSCmdlet.ShouldProcess($__) -or $WhatIfPreference.IsPresent)
            {
                $Confirmed = $true;
                Write-Host "Fixing SSB on Database $DatabaseName..." -ForegroundColor Yellow;
                [PSObject]$Model = @{ 
                DatabaseName = $__; 
                UserName = $AuthorizationUserName;
                };
                $PartialQuery = Get-FormattedTemplate -Model $(ConvertTo-Json $Model) -Path $TemplateFile -Silent:$true;
                $QueryBuilder.AppendLine($PartialQuery) | Out-Null;
            }
        }
    }

    END
    {
        $Query = $QueryBuilder.ToString();
        IF($WhatIfPreference.IsPresent) { RETURN $Query; }       
        IF($Confirmed)
        {
            Invoke-SqlCmdInternal -Query $Query -Database 'master' -InvocationBoundParameters $PsBoundParameters;
        }
        Write-Host "Fix SSB Process Ended" -ForegroundColor DarkCyan;        
    }   
}
#endregion