Posey's Tips & Tricks

PowerShell Based SQL Server Backups Revisited, Part 1

Let's refine some of my earlier work to make things more efficient.

• By Brien Posey
• 09/19/2025

A couple of months ago, I wrote an article on how to use PowerShell to back up a database within SQL Server Express Edition. Even though the technique that I described in that post works, the script that I wrote could be better. As such, I wanted to revisit the topic and provide you with a much more capable backup script.

This, of course, begs the question of what new capabilities I have baked into my new backup script. For starters, this particular script is configurable through the use of parameters and those parameters can be stored in the database itself. I don't want to completely rehash the discussion of storing configuration settings within a database, but I recently wrote a separate column in which I described how to do exactly that.

A second capability that I built into my script was native backup rotation. As previously noted, the new script supports the use of configurable parameters. One of those parameters is the number of backups to retain. So let's assume for a moment that you set the backup retention policy to 5. In such a case, each time that you run the script, a new backup file is created. Once you reach the limit (five in this case), the first backup file will be overwritten. From that point, the backup will always overwrite the oldest backup file and will never keep more backup copies than the number that you specify.

The third new capability that I have added to the script is automatic backup verification. Once the backup process completes, the script will immediately compare the backup against the original data as a way of ensuring that the backup is good.

Finally, the last of the new capabilities is backup logging. Each time a new backup is created, the backup script will add the new backup to the backup log. In doing so, it will create a record of the backup date and time, the name of the backup job, the size of the backup (in Megabytes), and whether or not the backup was successful.

I am using a modified version of my SQL backup script to protect the data within a line of business application that I am building. That application is configured to retain five backup copies. I built a backup log viewer into the application and you can see what that log viewer looks like in Figure 1. Even though this log viewer is not a part of the script that I am going to be providing you with, it shows the types of data that are stored in the backup logs.

So before I begin stepping you through the code, there are two database tables that you are going to need to create. The first of these tables is the Settings table, where the backup settings are stored. Here is the script that I used to create this table and to populate it with some initial values. You will obviously want to replace the values that I am using with values of your own. It's also worth noting that this script should be run inside of the SQL Server Management Studio:

CREATE TABLE settings (

  setting_display_name VARCHAR(100),

  setting_name VARCHAR(100),

  setting_value VARCHAR(100)

  )

INSERT INTO settings  (setting_display_name,setting_name,setting_value) VALUES ('Backup  Path','backup_path','C:\Backups')

  INSERT INTO settings  (setting_display_name,setting_name,setting_value) VALUES ('Number of Backups to  Retain','backup_retention','5')

  INSERT INTO settings  (setting_display_name,setting_name,setting_value) VALUES ('Backup  Prefix','backup_prefix',SQL_Server_Express_Backup_')
The second table that you are going to need is the backup  log table. Here is the script for creating that table:
CREATE TABLE backup_log (

  sequence_number INT PRIMARY KEY,

  filename VARCHAR(255),

  backupdate VARCHAR(100),

  filesize DECIMAL(20,2),

  state VARCHAR(100),

  error VARCHAR(250)

  )

The most important thing to note about this table is that it contains a column called Error. Right now, the Error column is not being used. However, I plan to eventually build a version of the script that logs any errors that are detected during the backup or verification process.

So with that said, I want to wrap up this article by providing you with my full PowerShell script. In Part 2, I will explain how the script works. Here is the code:

# --- Basic Configuration ---

$Server = "localhost\SQLEXPRESS"

  $Database = "SampleData"

  $ConnectionString="Invoke-SQLCmd -ServerInstance  BrienMain\SQLExpress -Database SampleData -Query "
### Function to perform SQL queries
Function Perform-Query {

  Param (

  [String]$QueryStatement

  )
$QueryString = $ConnectionString + '"' +  $QueryStatement + '"'

  $Query = Invoke-Expression $QueryString
Return $Query

  }
 
### 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
 
### Step 2: Make sure that the backup path exists
If (-not (Test-Path $BackupDirectory)) {

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

  }
### 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: Determine the backup filename
$BackupFile = $BackupDirectory + "\" + $Prefix  + $Sequence + ".bak"
### 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: Determine the backup size and when the backup  was created
$FileSizeMB = [math]::Round((Get-Item $BackupFile).Length  / 1MB, 2)

  $Now = Get-Date
### 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
### 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 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"
 
### 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

  }