Once again an essential MSSQL topic. The backup of a database (in this case an Express version). Since this version is freely available and usable, the customer of course doesn't want to spend money for the backup). But thank god he has the money for me and my effort to develop a solution...

Requirements:

• Powershell Module dbatools - Link

There are 2 variables in the script that must be set:

• Local Archive Path (this is the path (local) on the target system)
• Archive Server (the target system)

The other variables can, but do not have to be adjusted.

• Archive Name (name pattern)
• Temporarly Dump Backup Path (staging area)
• Retention limit (how long the archives should be kept)

The script:

For the sake of good order, a short description of what the script does. It examines the server on which it is executed for database instances (we assume there is only one) and the databases it contains.  It creates a cache area if necessary and saves the found databases there. These backups are then compressed and the original uncompressed database backups are deleted. Afterwards the last created file is copied to our archiving target (again, we assume that there was only one database to backup. For the backup of several databases (possibly located in several instances), you would have to make script adjustments). Finally, cleanup work is required. Depending on the defined retention limit (by default 7 days), a corresponding number of backups remain on the archive system.

############################################################################
#                                                                          #
#   Created by                                                             #
#                                                                          #
# 	  Powershell Script - Databasebackup                               #
#	  15.01.2020                                                       #
#	  Thomas Bründl                                                    #
#                                                                          #
############################################################################

# Prequistes
# -------------------------------------------------------------------------------
#   - Powershell dbatools-Module
# -------------------------------------------------------------------------------

# Vars
# -------------------------------------------------------------------------------

# Local Archive Path
$ArchivePath = "<Diskletter>:\<Path>"

# Archive Server
$ArchiveServer = "<Hostname>"

# Archive Name
$ArchiveName = "Backup_"+(Get-Date  -Format ddMMyy)+".zip"

# Temporarly Dump Backup Path
$SQLBackupPath = "C:\Temp"

# Retention limit
$limit = (Get-Date).AddDays(-7)

# Script
# -------------------------------------------------------------------------------

# Find the local SQL Instance
$SQLInstance = Find-DbaInstance -ComputerName localhost

# Query all Databases
$SQlDatabases = (Find-DbaInstance -ComputerName localhost | Get-DbaDatabase -ExcludeDatabase master,tempdb,model,msdb)

# Test whether the temporary dump backup path exists and create it if necessary
if (-Not (Get-Item $SQLBackupPath)) { New-Item -ItemType dir $SQLBackupPath } 

# Backup each Database
foreach ($SQLDatabase in $SQlDatabases) {
$SQlDatabaseName = $SQLDatabase.Name
Write-Host "Starting Backup" -ForegroundColor Yellow
Backup-DbaDatabase -SqlInstance $SQLInstance -Path $SQLBackupPath -Database $SQlDatabaseName -Type Full
}

# Archive Backup Dumps
Get-ChildItem -Path $SQLBackupPath -Filter *.bak | Compress-Archive -DestinationPath "$SQLBackupPath\$ArchiveName"

# Remove unnecessary Backup Dump Files
Get-ChildItem -Path $SQLBackupPath -Filter *.bak | Remove-Item

# Get the last created file
$ArchiveFile  = Get-ChildItem -Path $SQLBackupPath -Filter *.zip | select -last 1

# Connect to the Archive System
$Session = New-PSSession -ComputerName $ArchiveServer

# Copy Archive to the Archive System
Copy-Item ($ArchiveFile.FullName) -Destination $ArchivePath -ToSession $Session

# Delete files older than the $limit.
invoke-command -computername $ArchiveServer {param($ArchivePath,$limit) Get-ChildItem -Path $ArchivePath | Where-Object { !$_.PSIsContainer -and $_.LastWriteTime -lt $limit } | Remove-Item -Force} -ArgumentList $ArchivePath,$limit

# Check the Archive Folder
# invoke-command -computername "IVBBKPSRV" {param($RemoteBackupPath) Get-ChildItem -Path $RemoteBackupPath}  -ArgumentList $RemoteBackupPath

# Remove unnecessary Archive Files
Get-ChildItem -Path $SQLBackupPath -Filter *.zip | Remove-Item

# Reference:
# https://dbatools.io/download/

The script is executed via the task scheduler. For this purpose, I have already made a blog entry that can help you here (see link below).

References: