SQLServerStandardExpressDbBackup.ps1

# All Rights Reserved, 2018
# v2.0.20181210
#
# Description:
#   Powershell Script to generate database backups for MS SQL Server (Any Edition) Installation.
#
# Credits:
#   December 10, 2018: Edgar Gerardo Chinchilla Mazate, https://www.facebook.com/edgargerardo.chinchillamazate
#   Changes:
#       - Added a parameter to specify the max-age backup files to
#         be preserved in the target path. Default is 180.
#       - Added a parameter to specify if is needed to prompt for
#         user credentials (to store it for use the script silently later).
#       - Tweked code for porformance gains
#   January 12, 2017: Edgar Gerardo Chinchilla Mazate, https://www.facebook.com/edgargerardo.chinchillamazate
#   Changes:
#       - Improved file comments
#       - Added a parameter to specify a database to backup. Null will
#         result in the backup of all the registered databases
#       - Added (if especified) a "Backup Destination Path" check, invalid
#         path will result in an exception error
#       - Added a hardcoded parameter to specify the days to keep the
#         previous backups in the destination path
#   July 3, 2012: Thomas LaRock, http://thomaslarock.com
#   Changes:
#       - Initial Version
# Parameters:
#   $daysToPreserveBackups:
#                       The max-age days quantity to preserve backups in the
#                       destination path. Null will result in asuming 180 days.
#   $sqlInstanceName:
#                       The SQL Server instance name to connect to. Null will
#                       result in an error exception.
#   $backupDestination:
#                       The desired backup destination path. Null will
#                       result in default backup path being used.
#   $databasesToBackup:
#                       The (comma separated) name of the databases to backup.
#                       Null will resul in the backup of all the registered dbs.
#   $promptForCreds:
#                       Specifies if the script will prompt the user to enter
#                       SQL credentials (to create or update them to execute
#                       the script silently later). Default is true.
#   $credsDestination:
#                       Specifies where the encrypted credentials will be stored.
#                       Null will result in the use of "ProgramData" folder.
# Excecution example:
#   .\SQLServerStandardExpressDbBackup.ps1
#       <optional> -daysToPreserveBackups 180
#       -sqlInstanceName "SRV00xyz\SQLEXPRESS"
#       <optional> -backupDestination "C:\BKxyz\"
#       <optional> -databasesToBackup "DBx,DBy,DBz"
#       <optional> -promptForCreds 0
#       <optional> -credsDestination "C:\Config\xyz\"

param (
    # WARNING: This value especifies the time Interval to keep backups.
    [Int32]$daysToPreserveBackups=180,
    [String]$sqlInstanceName=$(Throw "Please specify a SQL Server Instance name."),
    [String]$backupDestination=$null,
    [String]$databasesToBackup=$null,
    [Bool]$promptForCreds=$true,
    [String]$credsDestination="$env:ProgramData"+"\PS Backup Task"
);

# Function to handle any errors that occurs in the script
Function Error_Handler {
    Write-Host (" Error Category: " + $error[0].CategoryInfo.Category);
    Write-Host (" Error Object:   " + $error[0].TargetObject);
    Write-Host (" Error Message:  " + $error[0].Exception.Message);
    Write-Host (" Error FQEId:    " + $error[0].FullyQualifiedErrorId);
};

# Register the script error handler
Trap {
    # Handle the error
    Error_Handler;
    # End the script.
    Break;
};

# Define the credentials file name from a "Sanitized" $sqlInstanceName
# Sanitize the string using Regular Expression - Unicode - Unicode Categories
$credsFileName = $($sqlInstanceName -replace '[^\p{L}\p{Nd}]', '');
$credsFileName += ".xml";

# Check if the paths has "\" at the end of string, if not, add it
If (-Not $backupDestination.EndsWith("\")) {
    $backupDestination += "\";
};
If (-Not $credsDestination.EndsWith("\")) {
    $credsDestination += "\";
};

# Check if the backup path is especified and exists, if isn't valid, throw a new error
If ($backupDestination) {
    $pathValidation = Test-Path($backupDestination);
    If (-Not $pathValidation) {
        Throw [System.IO.FileNotFoundException] "The specified $backupDestination backup path is not found.";
        Exit 1;
    };
    $pathValidation = $null;
};

# Get/Prompt/Store User credentials
# Verify (and create if not exists) creds destination directory
$pathValidation = Test-Path($credsDestination);
If (-Not $pathValidation) {
    MD -Force $credsDestination -ErrorAction SilentlyContinue | Out-Null;
};
$pathValidation = $null;

# Clear screen (supposing that some path was created in the previous steps)
Clear-Host;
# Check if the user wants to promp for creds (to create them)
If ($promptForCreds) {
    Get-Credential | Export-Clixml -Path ($credsDestination + $credsFileName);
};
# Load the Secure Stored creds
$sqlCreds = Import-Clixml -Path ($credsDestination + $credsFileName) -ErrorVariable cmdError;
If ($cmdError -ne '') {
    Throw "SQL Server credentials not found.";
    Exit 1;
};

# Load SMO assembly to check the SQL Server Instance version
$sqlSmoAssembly = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.Smo') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
# If we're running SQL 2008 DLLs, load the SMOExtended and SQLWMIManagement libraries
If ($sqlSmoAssembly.GetName.Version -ne '9') {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
};
# Create a SQL Server Management Object for the specified SQL Server Instance name
# and authenticate using "SQL Server Auth"
$sqlServerManagementSmo = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlInstanceName
# Disable StatementTimeOut to prevent large-backups error
$sqlServerManagementSmo.ConnectionContext.StatementTimeout = 0;
# Switch to SQL Server Auth instead of the de default Windows Auth
$sqlServerManagementSmo.ConnectionContext.LoginSecure = $false;
# Set Login Creds to establish the connection
$sqlServerManagementSmo.ConnectionContext.Login=$sqlCreds.GetNetworkCredential().UserName;
$sqlServerManagementSmo.ConnectionContext.set_SecurePassword($sqlCreds.Password);

# If missing, set default backup directory
If (-Not $backupDestination) {
    $backupDestination = $sqlInstanceName.Settings.BackupDirectory;
    # Check if the paths has "\" at the end of string, if not, add it
    If (-Not $backupDestination.EndsWith("\")) { $backupDestination += "\"; };
    $pathValidation = Test-Path($backupDestination);
    If (-Not $pathValidation) {
        Throw [System.IO.FileNotFoundException] "The specified $backupDestination backup path is not found.";
        Exit 1;
    };
    $pathValidation = $null;
};

# Start the Backup Task
Write-Output ("Backup Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));

# If specified, try to backup the desired database
If ($databasesToBackup -ne "") {
    ForEach ($dbToBackup in $databasesToBackup.Split(",")) {
        Write-Output ("    Backing up " + $dbToBackup + " database...");
        # Backup the specified database
        $timeStamp = Get-Date -format yyyyMMddHHmmss;
        $targetBackup = $backupDestination + $timeStamp + "_full_" + $dbToBackup + ".bak";
        $backupManager = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");
        $backupManager.Action = "Database";
        $backupManager.Database = $dbToBackup;
        $backupManager.BackupSetName =  $dbToBackup + " Backup";
        $backupManager.BackupSetDescription = "Full backup of " + $dbToBackup + " " + $timeStamp;
        $backupManager.MediaDescription = "Disk";
        $backupManager.Devices.AddDevice($targetBackup, "File");
        $backupManager.Incremental = 0;
        # Starting full backup process
        $backupManager.SqlBackup($sqlServerManagementSmo);
        If (Test-Path($targetBackup)) { Write-Host ("        The backup was finished OK."); }
    }
} Else {
    # Full-backup every registered database
    ForEach ($db in $sqlServerManagementSmo.Databases) {
        If ($db.Name -ne "tempdb") {
            Write-Output ("    Backing up " + $db.Name + " database...");
            $timeStamp = Get-Date -format yyyyMMddHHmmss;
            $targetBackup = $backupDestination + $timeStamp + "_full_" + $db.Name + ".bak";
            $backupManager = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");
            $backupManager.Action = "Database";
            $backupManager.Database = $db.Name;
            $backupManager.BackupSetName = $db.Name + " Backup";
            $backupManager.BackupSetDescription = "Full backup of " + $db.Name + " " + $timeStamp;
            $backupManager.MediaDescription = "Disk";
            $backupManager.Devices.AddDevice($targetBackup, "File");
            $backupManager.Incremental = 0;
            # Starting full backup process.
            $backupManager.SqlBackup($sqlServerManagementSmo);
            If (Test-Path($targetBackup)) { Write-Host ("        The backup was finished OK."); }
        };
    };
};

# Remove backups older than the especified backup days
Get-ChildItem "$backupDestination\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToPreserveBackups) } |% { Remove-Item $_ -force }
"Removed all backups older than $daysToPreserveBackups days"

# End the Backup Task
Write-Output ("Backup Finished at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));

# Fuentes
#   https://stackoverflow.com/questions/45008016/check-if-a-string-is-not-null-or-empty
#   https://stackoverflow.com/questions/16906170/create-directory-if-it-does-not-exist
#   https://sqlaj.wordpress.com/2010/10/07/using-powershell-sql-database-backup-and-compression/
#   https://www.mssqltips.com/sqlservertip/3458/automate-sql-server-express-backups-and-purge-old-backups/
#   https://ola.hallengren.com/
#   https://www.reddit.com/r/PowerShell/comments/1m1y3e/hep_with_script_for_backing_up_sql_database/
#   https://blogs.technet.microsoft.com/heyscriptingguy/2014/07/17/using-the-split-method-in-powershell/
#   https://elegantcode.com/2009/12/22/powershell-load-assembly-without-locking-file/
#   http://hkeylocalmachine.com/?p=413
#   https://gallery.technet.microsoft.com/determining-the-version-dbc1c53a
#   https://www.powershellmagazine.com/2014/07/21/using-powershell-to-discover-information-about-your-microsoft-sql-servers/
#   https://gallery.technet.microsoft.com/scriptcenter/5f763a6e-23bf-46be-a837-13e5005acb72
#   https://powershell.org/forums/topic/get-sql-edition-and-version-from-remote-servers/
#   https://www.itprotoday.com/disaster-recovery/druvas-cloudranger-integration-helps-manage-protect-workloads-cloud
#   https://stackoverflow.com/questions/7587077/how-do-i-check-for-the-sql-server-version-using-powershell
#   https://www.thomasmaurer.ch/2010/07/powershell-check-variable-for-null/
#   https://stackoverflow.com/questions/5592531/how-to-pass-an-argument-to-a-powershell-script
#   https://stackoverflow.com/questions/28352141/convert-a-secure-string-to-plain-text
#   https://blog.sqlserveronline.com/2017/11/10/powershell-credentials-connect-to-sql-server-with-powershell/
#   https://powershell.org/2013/09/powershell-performance-the-operator-and-when-to-avoid-it/
#   https://4sysops.com/archives/strings-in-powershell-replace-compare-concatenate-split-substring/
#   https://community.spiceworks.com/how_to/17736-run-powershell-scripts-from-task-scheduler
#   https://ss64.com/ps/syntax-esc.html
#   https://dmitrysotnikov.wordpress.com/2011/02/03/how-to-schedule-a-powershell-script/
#   https://lazywinadmin.com/2015/08/powershell-remove-special-characters.html