Posey's Tips & Tricks

PowerShell-Based SQL Server Backups Revisited, Part 2

Let's follow up the last column with a step-by-step breakdown shows how the PowerShell script automates SQL Server backups, manages retention, logs activity and verifies data integrity.

• By Brien Posey
• 09/25/2025

In my first post in this series, I talked about my SQL Server Express Edition backup script and its new capabilities. I ended that article by giving you the source code used by my script. Now, I want to take a step back and explain how the code works. My script is organized into 10 steps.

The script begins by defining a few variables and creating a function called Perform-Query that handles most of the database queries. This function is then put to work in Step 1, which involves reading the various settings from the Settings table:

### Step 1: Read backup  settings from the database

Write-Host "Backup  in Progress"

  $QueryStatement =  "SELECT * FROM settings where setting_name='backup_path'"

  $Query = Perform-Query  $QueryStatement

  $Result = $Query

  $BackupDirectory =  $Result.setting_value
$QueryStatement =  "SELECT * FROM settings where setting_name='backup_retention'"

  $Query = Perform-Query  $QueryStatement

  $Result = $Query

  [int]$RetentionCount =  $Result.setting_value
$QueryStatement =  "SELECT * FROM settings where setting_name='backup_prefix'"

  $Query = Perform-Query  $QueryStatement

  $Result = $Query

  $Prefix =  $Result.setting_value

As you can see, Step 1 performs three separate database queries. These queries are designed to read the backup path, the retention count (the number of backup copies to keep), and the backup prefix (what do you want to call the backups?) from the database.

Step 2 tests to make sure that the specified backup path exists. If the path does not exist, then the script will automatically create the necessary folder on your hard disk.

### Step 2: Make sure that  the backup path exists

If (-not (Test-Path  $BackupDirectory)) {

  New-Item -ItemType Directory -Path  $BackupDirectory | Out-Null

  }

The third step in the process is to determine the next backup sequence number. As you may recall, one of the settings allows you to define the backup prefix. The backup prefix is the backup name and a sequence number is appended to that name. If for example, the prefix is SQL_Backup_ and you allow for five backup copies to be retained, then your backup files will be SQL_Backup_1, SQL_Backup_2, SQL_Backup_3, SQL_Backup_4, and SQL_Backup_5.

The script initially sets the sequence number to 1. It does this in case no backups have yet been created. However, the script then reads the contents of the backup log and determines from the date and time stamp which backup was created most recently. It then checks that backup’s sequence number and adds 1. If the resulting sequence number is greater than the backup retention number then the sequence number is set to 1. This allows the backup files to be rotated in order of their age.

### Step 3: Determine the  next backup sequence number

  ### If no backups exist,  the sequence will be set to 1

  ### If the backup  retention limit has been reached, the sequence will be set to 1

[int]$Sequence = 1

  $QueryStatement =  "SELECT backupdate FROM backup_log"

  $Query = Perform-Query  $QueryStatement

  $Result = $Query
$MostRecentBackup =  $Result.backupdate | Sort-Object -Descending | Select-Object -First 1

  $QueryStatement =  "Select sequence_number from backup_log WHERE backupdate='" +  $MostRecentBackup + "'"

  $Query = Perform-Query  $QueryStatement

  [int]$StoredSequenceNumber  = $Query.sequence_number
If ($StoredSequenceNumber  -eq $Null){$Sequence = 1}

  Else {$Sequence =  $StoredSequenceNumber + 1}
If ($Sequence -gt  $RetentionCount) {$Sequence = 1}

Step 4 in the process is to build the backup filename. This is done by forming a string called $BackupFile. This string combines the backup directory, the prefix, the sequence number, and the .BAK file extension. Once formed, the $BackupFile variable stores the full path and filename for the backup file that is to be created.

### Step 4: Determine the  backup filename

$BackupFile =  $BackupDirectory + "\" + $Prefix + $Sequence + ".bak"

The fifth step in the process is to perform the actual backup. I don’t want to spend much time talking about this step, because it is essentially the same as what I covered in my original article.

### Step 5: Back up the  database

$QueryStatement =  "BACKUP DATABASE " + $Database + " TO DISK = '" +  $BackupFile + "' WITH INIT, FORMAT, NAME = 'Rotated Backup of  $Database'"

  $Query = Perform-Query  $QueryStatement

Step 6 involves figuring out the backup’s size and when the backup was created so that this information can be logged. Figuring out the backup size is simply a matter of taking the file length and dividing it by 1 MB. The number 2 that you see in the code indicates that two decimal places will be used. Determining when the backup was created is a matter of using PowerShell’s Get-Date cmdlet.

### Step 6: Determine the backup size and when the backup  was created

$FileSizeMB =  [math]::Round((Get-Item $BackupFile).Length / 1MB, 2)

  $Now = Get-Date

Step number 7 involves checking to see if an old backup was overwritten by a new backup and removing the old backup from the backup log. Remember, the script already knows the sequence number associated with the backup that it just created. As such, the script checks the backup log table to see if any of the existing records match the backup sequence number. If so, then a SQL query is used to delete that record.

### Step 7: If an old  backup was overwritten, remove the old backup from the backup log

$QueryStatement =  "DELETE FROM backup_log WHERE sequence_number = '" + $Sequence +  "'"

  $Query = Perform-Query  $QueryStatement

The eighth step in the process is to write a log entry for the backup that was just created. This step uses a SQL query to create a new record within the backup log table. This record includes the sequence number, the backup path and filename, the date and time when the backup was created, and the backup size.

### Step 8: Add the new backup to the backup log

$QueryStatement =  "INSERT INTO backup_log (sequence_number, filename, backupdate, filesize)  VALUES ('" + $Sequence + "', '" + $BackupFile + "', '"  + $Now + "', '" + $FileSizeMB + "')"

  $Query = Perform-Query  $QueryStatement

Step number 9 is the simplest of all the steps. It merely uses a couple of Write-Host statements to display messages indicating that the backup process has completed and that the verification process is beginning.

### Step 9: Provide a  message stating that the backup has been completed and is now being verified

Write-Host "Backup  complete: $BackupFile (Seq: $Sequence, Size: $FileSizeMB MB)"

  Write-Host  "Verification in progress"

The last step in the process is to verify the backup. The actual verification process uses a single SQL command (RESTORE VERIFYONLY FROM DISK). However, the script uses a try and catch to determine whether or not the verification process was successful. If the verification process does succeed, then the Try section instructs the script to display a Backup Verification Successful message and to update the backup job entry within the backup log to indicate that the verification succeeded.

If on the other hand, the verification process does not succeed, then the Catch section will display a message indicating that the verification process has failed. It will also attach a failure status to the job’s record within the backup log.

### Step 10: Perform the  verification process. The results are displayed on screen and written to the  backup log.

$QueryStatement =  "RESTORE VERIFYONLY FROM DISK = '" + $BackupFile + "'"
Try {Invoke-SQLcmd  -ServerInstance BrienMain\SQLExpress -Database SampleData -Query  $QueryStatement -ErrorAction STOP

  Write-Host "Backup  Verification Success"

  $QueryStatement =  "UPDATE backup_log SET state='Success' Where sequence_number='" +  $Sequence +"'"

  $Query = Perform-Query  $QueryStatement
}

  Catch {

  Write-Host "Backup  Verification Failure"

  $QueryStatement =  "UPDATE backup_log SET state='Failed' Where sequence_number='" +  $Sequence +"'"

  $Query = Perform-Query  $QueryStatement

  }