Backup-Database

DBSystems: Performs a backup of a SQL Server database

Script

#Requires -Version 5.0
#Requires -Modules SQLServer

[CmdLetBinding()]
Param(
    [Parameter(Mandatory = $true)]   
    [string]$ServerInstance,    
    [Parameter(Mandatory = $true)]   
    [string]$DBName,
    [pscredential]$ServerCredential,    
    [ValidateSet('Database', 'Files', 'Log')]
    [string]$BackupAction = "Database",
    [string]$BackupFile,
    [string]$BackupContainer,
    [string]$BackupSetName,
    [string]$BackupSetDescription,     
    [ValidateSet('512', '1024', '2048', '4096', '8192', '16384','32768','65536')]
    [string]$BlockSize = "512",
    [switch]$CheckSum,
    [switch]$ContinueAfterError,
    [switch]$CopyOnly,
    [ValidateSet('Default','On','Off')]
    [string]$CompressionOption = "Default",
    [string]$DatabaseFile,
    [string]$DatabaseFileGroup,
    [switch]$Incremental,
    [ValidateSet('TruncateOnly','Truncate','NoTruncate')]
    [string]$LogTruncationType = "Truncate",
    [switch]$NoRecovery,
    [int]$ConnectionTimeout = 30
)

function Get-SqlServerInstanceInternal {
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory = $true)]   
        [string]$ServerInstance,    
        [pscredential]$ServerCredential,
        [int]$ConnectionTimeout = 30
    )
    try {
        [hashtable]$cmdArgs = @{
            'ErrorAction' = 'Stop'
            'Confirm' = $false
            'ServerInstance' = $ServerInstance
            'ConnectionTimeout' = $ConnectionTimeout
        }
        if ($null -ne $ServerCredential) {
            $cmdArgs.Add('Credential', $ServerCredential)
        }
        return Get-SqlInstance @cmdArgs
    } catch {
        throw
    }
}

Import-Module SQLServer

try {
    $instance = Get-SqlServerInstanceInternal -ServerInstance $ServerInstance -ServerCredential $ServerCredential -ConnectionTimeout $ConnectionTimeout

    [hashtable]$cmdArgs = @{
        'ErrorAction' = 'Stop'
        'InputObject' = $instance
        'Database'    = $DBName
        'Confirm'     = $false
        'BackupAction' = $BackupAction
        'BlockSize'   = [int]$BlockSize
        'CheckSum'    = $CheckSum
        'CompressionOption' = $CompressionOption
        'ContinueAfterError' = $ContinueAfterError
        'CopyOnly'    = $CopyOnly
        'Incremental' = $Incremental
        'LogTruncationType' = $LogTruncationType
        'NoRecovery'  = $NoRecovery
        'PassThru'    = $true
    }
    
    if (-not [string]::IsNullOrWhiteSpace($BackupFile)) {
        $cmdArgs.Add('BackupFile', $BackupFile)
    }
    elseif (-not [string]::IsNullOrWhiteSpace($BackupContainer)) {
        $cmdArgs.Add('BackupContainer', $BackupContainer)
    }
    
    if (-not [string]::IsNullOrWhiteSpace($BackupSetName)) {
        $cmdArgs.Add('BackupSetName', $BackupSetName)
    }
    if (-not [string]::IsNullOrWhiteSpace($BackupSetDescription)) {
        $cmdArgs.Add('BackupSetDescription', $BackupSetDescription)
    }
    
    if ($BackupAction -eq 'Files') {
        if (-not [string]::IsNullOrWhiteSpace($DatabaseFile)) {
            $cmdArgs.Add('DatabaseFile', $DatabaseFile.Split(',').Trim())
        }
        elseif (-not [string]::IsNullOrWhiteSpace($DatabaseFileGroup)) {
            $cmdArgs.Add('DatabaseFileGroup', $DatabaseFileGroup.Split(',').Trim())
        }
    }
   
    $result = Backup-SqlDatabase @cmdArgs | Select-Object *    
    Write-Output $result
} catch {
    throw
}

Customize

ServerInstance string

Specifies the name of the target computer including the instance name, e.g. MyServer\Instance

DBName string

Specifies the name of the database to back up

ServerCredential pscredential

Specifies a PSCredential object for the connection to the SQL Server. ServerCredential is ONLY used for SQL Logins. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login.

BackupAction string

Specifies the type of backup operation to perform: Database (Full), Files, or Log

BackupFile string

Specifies the location and file name for the backup (e.g. C:\Backups\MyDatabase.bak)

BackupContainer string

Specifies the folder or location where the cmdlet stores backups

BackupSetName string

Specifies the name of the backup set

BackupSetDescription string

Specifies the description of the backup set

BlockSize string

Specifies the physical block size for the backup in bytes

CheckSum switch

Off

Indicates that a checksum value is calculated during the backup operation

ContinueAfterError switch

Off

Indicates that the operation continues when a checksum error occurs

CopyOnly switch

Off

Indicates that the backup is a copy-only backup

CompressionOption string

Specifies the compression options for the backup operation (Default, On, or Off)

DatabaseFile string

Specifies one or more database files to back up, comma separated. Only used when BackupAction is 'Files'.

DatabaseFileGroup string

Specifies the database file groups to back up, comma separated. Only used when BackupAction is 'Files'.

Incremental switch

Off

Indicates that a differential backup should be performed

LogTruncationType string

Specifies the truncation behavior for log backups (TruncateOnly, Truncate, or NoTruncate)

NoRecovery switch

Off

Indicates that the tail end of the log is not backed up

ConnectionTimeout int

Specifies the time period to retry the command on the target server