MSEndpointMgr

How to back up a WSUS SQL database with PowerShell

Over the years I’ve worked with a few different organizations that all do SQL backups in different ways however I recently ran into a situation where there wasn’t a really great way to perform a SQL back up of the SUSDB in the environment because one it was running on SQL express and their standard backup solution wasn’t viable for SQL. Now since we had just done the rather lengthy process of performing WSUS clean-up/maintenance we really don’t want to have to do it again and start from scratch it would be much better to simply restore the database in its cleaner state.

For quite a long time now there has been a PowerShell module installed with almost every version of SQL. In fact there are two of them the standard SQLPS module and the SQL-Server module. However the SQL-Server module is only installed with much newer versions of SQL and as a general rule needs to be installed from the online PowerShell Gallery using the install-module command.

Turns out the backup command itself is relatively simple to run you can backup a SQL database with just one line.

Backup-SqlDatabase -ServerInstance YOURSERVERNAME -Database DATABASENAME -BackupFile PATHTOPACKUPFIke.BAK

What tripped me up the first time I did this was that I thought ‘ServerInstance’ meant the server instance name but in fact it just means the server host name where the database is located.

However I wanted to account for more things than just writing a backup file to a location and to include a few double checks and some logging so I wrote something that does a very simple database backup routine.

You can download the script from the SCConfigMgr Git-Hub repository. Below is the script in its entirety if you simply want to copy paste and read the script through. However if you want a detailed explanation of the script scroll to the bottom of the script block to get a more in depth breakdown.

<#
.SYNOPSIS
    Simple full backup Script for WSUS database 

.DESCRIPTION
    $ServerName                         Chose the Server Name that hosts the SQL instance
    $BackupDirectory                    Choose the path to the location to backup the SQL database
    [int32]$BackupRetentiondays         Specifiy the number of days you would like to retain the database for
    $DatabaseName                       Specifify the database you would like to perform a full backup for.
    $LogDirectory                       Specifiy the directory you would like to send basic logging to
    $LogName                            Partially name the daily log file.

.EXAMPLE


.NOTES
    FileName:    SQL-BackupScript.ps1
    Author:      Jordan Benzing
    Contact:     @JordanTheItGuy
    Created:     2018-09-06
    Updated:     2018-09-06

    Version history:
    1.0.0 - (2018-09-06) Script created
#>
<#
This can be used to enable support for parameterization with the script instead of using stored variables for a Scheduled task.
[CmdletBinding(SupportsShouldProcess=$true)]
param(
    

[parameter(Mandatory = $true)]

[string]$ServerName,

[parameter(Mandatory = $true)]

[string]$BackupDirectory,

[parameter(Mandatory = $true)]

[int32]$BackupRetentiondays,

[parameter(Mandatory = $true)]

[string]$DatabaseName, [Parameter(Mandatory = $True)] [string]$LogDirectory, [Parameter(Mandatory = $True)] [string]$LogName ) #> #See Description for explanation of the Below variables $ServerName = $BackupDirectory = [int32]$BackupRetentiondays = $DatabaseName = $LogDirectory = $LogName = function Write-Log { [CmdletBinding()] param( [Parameter(Mandatory = $False)] [string]$LogDirectory, [Parameter(Mandatory = $False)] [string]$LogName, [Parameter(Mandatory = $False)] [string]$Message, [Parameter(Mandatory = $false)] [switch]$LogCleanup ) try { if(Test-Path $LogDirectory) #Validates the log direcory exists { if(($LogDirectory.Substring($LogDirectory.Length – 1)) -eq ‘\’) #Checks the backslash is present at the end of the string if not then jumps to second loop and adds it back. { $LogFile = $LogDirectory + $LogName + “-” + (Get-Date -UFormat %m_%d_%y ).ToString() + “.TXT” ((Get-Date).ToString() + ” – ” + $Message) >> $LogFile; } else { $LogFile = $LogDirectory + $LogName + “-” + (Get-Date -UFormat %m_%d_%y ).ToString() + “.TXT” ((Get-Date).ToString() + ” – ” + $Message) >> $LogFile; } } if($LogCleanup) { Get-ChildItem -Path $LogDirectory -Filter $LogName* | Where-Object{$_.LastWriteTime -lt ((Get-Date).AddDays(-3))} | Remove-Item } } catch { $ErrorMessage = $_.Exception.Message Write-Error -Exception LoggingError -Message $ErrorMessage } } function Start-backup { [CmdletBinding(SupportsShouldProcess=$true)] param(

[parameter(Mandatory = $true)]

[string]$ServerName,

[parameter(Mandatory = $true)]

[string]$BackupDirectory,

[parameter(Mandatory = $true)]

[int32]$BackupRetentiondays,

[parameter(Mandatory = $true)]

[string]$DatabaseName ) Begin { if((Get-Module -ListAvailable -Verbose:$false| Where-Object{$_.Name -eq “SQLPS”})) #Checks to see if the SQL Module is available if the module is not available it cannot execute backups. { $VerboseMessage = “The minimum required SQL Module is present you may continue” Write-Verbose -Message “The minimum required SQL Module is present you may continue” Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage } } Process { try { #Import the SQLPS Module Import-Module SQLPS -Verbose:$false $VerboseMessage = “Imported the Management Pack” Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage Write-Verbose -Message $VerboseMessage $CurrentDate = Get-Date -UFormat %m_%d_%y $VerboseMessage = “Got the current date to use as the BAKName” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage $VerboseMessage = “Validating the retention days is negative if greater than 0 if not then making it negative” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage #Above block completes initial variable setting and information gathering before attemping to do anything. if($BackupRetentiondays -gt 0) #Validates that backup retention days is not a positive number if it is makes it negative to only remove OLD backup files. { $VerboseMessage = “Backup date is positive making it negative” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage $BackupRetentiondays = $BackupRetentiondays * -1 #Validaes and logs that the date was properly set to a negative value. } if(Test-Path -Path $BackupDirectory) #Validates that the BackupDirectory Path is reachable and if it is reachable then continues on. { $VerboseMessage = “Managed to test the location the backups should be stored” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage if(($BackupDirectory.Substring($BackupDirectory.Length – 1)) -eq ‘\’) #Validates the path ends with a \ in the prompt. Due to past experience with strings that don’t end in \ from a user stared doing this check { $VerboseMessage = “Validated that the last character in the string is a \” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage $BackupFileName = $DatabaseName + “-” + $CurrentDate + “.bak” $BackupFile = $BackupDirectory + $BackupFileName $VerboseMessage = “Created the Backup location string” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage #Concatonate and builds the backup file location string. } } else { if(Test-Path -Path $BackupDirectory) #Validates that the path is reachable and continues on ##DEVNOTE## – This can be moved to top level and validated at start of run time to avoid multiple validation steps { $BackupFileName = $DatabaseName + “-” + $CurrentDate + “.bak” $BackupFile = $BackupDirectory + $BackupFileName #Sets the backup fiel strings that will be used. } } $VerboseMessage = “Starting backup process” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage #Next line exeuctes the SQL Backup Statement from PowerShell Backup-SqlDatabase -ServerInstance $ServerName -Database $DatabaseName -BackupFile $BackupFile #Upon Completion Continues on $VerboseMessage = “Completed the backup process – Now Calculating all files that can be removed” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage $AllBAK = Get-ChildItem -Path $BackupDirectory -Filter *.BAK | Where-Object{$_.LastWriteTime -lt (Get-Date).AddDays($BackupRetentiondays) -and $_.Name -eq $BackupFileName} #Gatehrs list of files that meet the criteria for removal (Old backup clean up) if($AllBAK) { $VerboseMessage = “There are BAK files to be removed” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage ForEach($BAKFile in $AllBAK) #For each file that is in the list do the folowing { if($BAKFile.LastWriteTime -lt (Get-Date).AddDays($BackupRetentiondays)) #Re-confirm that nothing slipped through initial filter and if it is really this old then remove #DEVNOTE#- This is not needed opimization can be removed since this age criteria was moved above to the filter. { Remove-Item -Path $BAKFile.FullName -Force #Forces the removal of the Backup Files using the full name to ensure its the pathed to item. $Name = $BAKFile.Name $VerboseMessage = “Removed $Name” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage } } } else { #If nothing met the backup purge criteria exits to here and continues on. $VerboseMessage = “No backup files need to be removed” Write-Verbose -Message “No backup files need to be removed” Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage } #Logs completion of the process $VerboseMessage = “Backup Process has been completed” Write-Verbose -Message $VerboseMessage Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage } catch { Write-Error -Message “Something went wrong run the script manually with Verbose specified to validate” } } } #Call functions using specified variables at script top to execute. Start-backup -ServerName $ServerName -BackupDirectory $BackupDirectory -BackupRetentiondays $BackupRetentiondays -DatabaseName $DatabaseName #Clean up log files older than 3 days this is hard coded #DEVNOTE# – Circle back and make this optional to the end user Write-Log -LogDirectory $LogDirectory -logName $LogName -LogCleanup -Message “Cleaning up Old Log Files Older than 3 days.” Write-Log -LogDirectory $LogDirectory -logName $LogName -Message “Script Completed”

The code above is relatively well covered in notes explaining what it does. The script has some limited logging and also will police its own logs and backups this helps prevent you from just creating 2GB backup files until all your storage has been used. It’s also worth noting that the script can be run using parameters or by storing and configuring a set of variables in the script you simply need to change the comment block at the start of the script. If you use the statically assigned variables its far simpler when creating a scheduled task than passing through a long complex argument. I also wrote a script that creates a scheduled task to run the job daily at a specific time this is not available on Git-hub as its a simple two line creation.

$action = New-ScheduledTaskAction -Execute 'Powershell.exe' `
  -Argument '-ExecutionPolicy Bypass -NoProfile -WindowStyle Hidden -command "& {"C:\scripts\Execute-SQLBackup.ps1"}"'
$trigger =  New-ScheduledTaskTrigger -Daily -At 9am
Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "SUSDB Backup" -Description "Daily Backup of SUSDB"

The script for the backup process is two simple functions. The first one is a generic log writer function that creates a text file and appends information to the text file regarding the backup tasks. It also deletes any log files from the log directory that are older than three days and that match the logname standard.

The second function is what runs the actual backup process for the SQL database. The first order of business in the script above is to establish that the script is running from a server where the SQLPS module is available which is exactly what occurs in this begin block.

Begin 
{
    if((Get-Module -ListAvailable -Verbose:$false| Where-Object{$_.Name -eq "SQLPS"}))
    #Checks to see if the SQL Module is available if the module is not available it cannot execute backups.
    {
        $VerboseMessage = "The minimum required SQL Module is present you may continue"
        Write-Verbose -Message "The minimum required SQL Module is present you may continue"
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
    }
}

If the minimum requirement is met then it stamps the log stating so and if its not then it will stamp that to the log as well. Since we know we have the SQLPS module its safe to continue and we can then attempt to import the SQLPS module and set some information that we will use when generating our BAK file and logs.

try
     {
        #Import the SQLPS Module
        Import-Module SQLPS -Verbose:$false
        $VerboseMessage = "Imported the Management Pack"
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage 
        Write-Verbose -Message $VerboseMessage
        $CurrentDate = Get-Date -UFormat %m_%d_%y
        $VerboseMessage = "Got the current date to use as the BAKName"
        Write-Verbose -Message $VerboseMessage
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage 
        $VerboseMessage = "Validating the retention days is negative if greater than 0 if not then making it negative"
        Write-Verbose -Message $VerboseMessage
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
        #Above block completes initial variable setting and information gathering before attemping to do anything.

All of this is of course logged and if you are simply running the script in console it has verbose options to show the steps as it walks through.

We then check the backup retention days variable to see if its set to a value that is greater than 0 and if it is we make the value negative because we need to delete things that are in the past not files that are in the future.

if($BackupRetentiondays -gt 0)
    #Validates that backup retention days is not a positive number if it is makes it negative to only remove OLD backup files.
    {
        $VerboseMessage = "Backup date is positive making it negative"
        Write-Verbose -Message $VerboseMessage
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage 
        $BackupRetentiondays = $BackupRetentiondays * -1
        #Validaes and logs that the date was properly set to a negative value.
    }

Once this is complete we validate that we can reach the backup directory location we then check the tail end of the backup directory string and ensure that it has a \ at the end of it so that we can properly concatenate the string and also due to an issue I had a long time ago I just tend to make sure directory’s end with a ‘\’. This code section could easily be cleaned up/optimized and have some duplication of code removed however it currently works as is and depending on usage or request from the community I may someday circle back to improve it.

if(Test-Path -Path $BackupDirectory)
    #Validates that the BackupDirectory Path is reachable and if it is reachable then continues on.
    {
        $VerboseMessage = "Managed to test the location the backups should be stored"
        Write-Verbose -Message $VerboseMessage
        Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
        if(($BackupDirectory.Substring($BackupDirectory.Length - 1)) -eq '\')
        #Validates the path ends with a \ in the prompt. Due to past experience with strings that don't end in \ from a user stared doing this check
        {
            $VerboseMessage = "Validated that the last character in the string is a \"
            Write-Verbose -Message $VerboseMessage
            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
            $BackupFileName = $DatabaseName + "-" + $CurrentDate + ".bak"
            $BackupFile = $BackupDirectory + $BackupFileName
            $VerboseMessage = "Created the Backup location string"
            Write-Verbose -Message $VerboseMessage
            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
            #Concatonate and builds the backup file location string.
        }
    }
else
    {
        if(Test-Path -Path $BackupDirectory)
        #Validates that the path is reachable and continues on
        ##DEVNOTE## - This can be moved to top level and validated at start of run time to avoid multiple validation steps
        {
            $BackupFileName = $DatabaseName + "-" + $CurrentDate + ".bak"
            $BackupFile = $BackupDirectory + $BackupFileName
            #Sets the backup fiel strings that will be used. 
        }
    }

Once all of our checks have been done and we’ve build the backup location string. We then know that we are safe to execute the backup step and log its activity. Once complete we then check to see if there are any BAK files that meet the criteria set out for deletion based on the retention days parameter and then move to delete any that meet that criteria set.

$VerboseMessage = "Starting backup process"
            Write-Verbose -Message $VerboseMessage
            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
            #Next line exeuctes the SQL Backup Statement from PowerShell
            Backup-SqlDatabase -ServerInstance $ServerName -Database $DatabaseName -BackupFile $BackupFile
            #Upon Completion Continues on
            $VerboseMessage = "Completed the backup process - Now Calculating all files that can be removed"
            Write-Verbose -Message $VerboseMessage
            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
            $AllBAK = Get-ChildItem -Path $BackupDirectory -Filter *.BAK | Where-Object{$_.LastWriteTime -lt (Get-Date).AddDays($BackupRetentiondays) -and $_.Name -eq $BackupFileName}
            #Gatehrs list of files that meet the criteria for removal (Old backup clean up)
            if($AllBAK)
            {
                $VerboseMessage = "There are BAK files to be removed"
                Write-Verbose -Message $VerboseMessage
                Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
                ForEach($BAKFile in $AllBAK)
                    #For each file that is in the list do the folowing
                    {
                        if($BAKFile.LastWriteTime -lt (Get-Date).AddDays($BackupRetentiondays))
                        #Re-confirm that nothing slipped through initial filter and if it is really this old then remove
                        #DEVNOTE#- This is not needed opimization can be removed since this age criteria was moved above to the filter.
                        {
                            Remove-Item -Path $BAKFile.FullName -Force
                            #Forces the removal of the Backup Files using the full name to ensure its the pathed to item.
                            $Name = $BAKFile.Name
                            $VerboseMessage = "Removed $Name"
                            Write-Verbose -Message $VerboseMessage
                            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
                        }
                    }
            }
            else
            {
                #If nothing met the backup purge criteria exits to here and continues on.
                $VerboseMessage = "No backup files need to be removed"  
                Write-Verbose -Message "No backup files need to be removed"   
                Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
            }
            #Logs completion of the process
            $VerboseMessage = "Backup Process has been completed"
            Write-Verbose -Message $VerboseMessage
            Write-Log -LogDirectory $LogDirectory -LogName $LogName -Message $VerboseMessage
        }
    catch
    {
        Write-Error -Message "Something went wrong run the script manually with Verbose specified to validate"
    }
}
}

Everything else at the end is the action of actually calling the functions within the script in the correct order with the required parameters. I wouldn’t recommend using this for something like the ConfigMgr database but for something simple like the SUSDB it should work just fine.

(3289)

Jordan Benzing

Jordan has been working in the Industry since 2009. Since starting he’s worked with Active Directory, Group Policy, SCCM, SCOM and PowerShell. Jordan most recently worked in the healthcare industry as an SCCM Infrastructure Team lead supporting over 150,000 endpoints. Jordan currently works as a Senior consultant for TrueSec Inc in the U.S. Most recently his focus has been in SQL Reporting for SCCM, creation of PowerShell scripts to automate tasks and PowerBI.

Add comment

Sponsors