Over the years there has been numerous discussion in the Qlik community about how to best back up a client-managed Qlik Sense Enterprise on Windows system (QSEoW).

The backup and data security policies of different organisations of course differs, but if we focus on the Qlik Sense side of things there is a relatively well defined set of data that should be backed up.

This article is an attempt to pull together 10 years of experience doing various backup solutions for Qlik Sense into a single PowerShell script that can do unattended, automatic backups of QSEoW.

A second part of this article will focus on backing up apps from Qlik Sense.

Enjoy!

What should be backed up?

First: Qlik has excellent instructions for the various steps involved in backing up Qlik Sense.

I’ve however always missed concrete scripts that automate the entire process. Qlik’s help pages break things down very nicely but don’t assemble those pieces into an easy to use backup process. So let’s try to fix that.

There are quite a few things that should be handled during a full QSEoW backup:

• QS certificates
• Postgres repository db
• Postgres log db (no need to back up log db once centralised logging has been disabled)
• QS system files
• QS app files
• QS custom configuration

Let’s look at these separately.

QS certificates

Sense uses self-signed certificates when talking to other nodes in a Sense cluster. Without these certificates it’s next to impossible to rebuild a Sense cluster from scratch – a full reinstall would be needed.

Each server issues its own certificates when Sense is first installed on the server. This means the certificates for each server has to be backed up.

Postgres repository db

This database is the heart of every Qlik Sense system. It keeps track of users, permissions, security rules, streams, content libraries, reload schedules, …

This is a central resource that is shared across all nodes in a Sense cluster, so it only needs to be backed up once during a backup cycle.

But – and this is important – you should NOT do that backup when the Sense cluster is still running! All Sense services on all nodes in the cluster should be stopped before the repository db is backed up.

Postgres log db

The Sense logging db is deprecated as of summer 2021.

No real reason to back it up thus, if anything you should spend some time on moving away from log db. There are instructions available from Qlik.

If you still want to back up log db the PowerShell script at the end of this article does have that feature too..

QS system files

These files are what makes Sense tick. It’s binary files, all the files that make up the Sense hub, QVF app files, search indexes, extensions, system log files, reload logs etc.

Same thing as above – these files should be backed up while all Sense services on all nodes are stopped.

QS app files

While not strictly needed to rebuild a Sense cluster from a serious crash, it’s of course a good idea to have a backup strategy for all those QVDs, CSVs, Excel files etc that your Sense apps rely on.

For a large Sense cluster with lots of data these files can add up to very large data volumes! You should probably also have a strategy to remove backups older than X days/weeks/months.

The PowerShell script at the end of this article includes a few lines that remove old backups.

QS custom config files

A few of the Sense services have their own config files.

These are sometimes used when you need to tweak some low-level setting of Sense that is not available via the QMC.

For completeness these files should thus also be backed up:

• %ProgramFiles%\Qlik\Sense\Repository\Repository.exe.config
• %ProgramFiles%\Qlik\Sense\Proxy\Proxy.exe.config
• %ProgramFiles%\Qlik\Sense\Scheduler\Scheduler.exe.config
• %ProgramFiles%\Qlik\Sense\ServiceDispatcher\services.conf

What else should a Sense backup solution do?

As the backup requires Sense to be stopped it will probably happen during a scheduled service window.

This can be a good time to do other kinds of housekeeping, here are some ideas:

Clean out old app search indexes

When a user searches for something in a Sense app a search index is built for that app and search term. The index files are stored in the \Apps\Search folder of the shared persistence file share.

For large apps with many users these index files can become large – 100s of Mbyte for individual files has been seen, with total file size in the 10s of Gbytes.

The backup script removes all index files older than a configurable number of days.

Delete old system log files

On one hand it’s nice to keep old log files around, as they are the only source for things such as old usage metrics (how many users did we have in Sense two years ago?).

There usually comes a time though when you want to delete at least some old log files. The backup script is a good place to do this.

The PowerShell script below has a section for deleting old system log files.

Delete old reload script log files

Similarly to system log files, reload logs can over time use lots of disk space. They typically become less and less interesting the older they get.

Deleting reload logs older than certain number of days can easily be done from the backup script.

A PowerShell backup script for Qlik Sense

Finally – here is a script that backs up all the data described above.

The script can be scheduled to run unattended using Windows Scheduler or manually on-demand from a PowerShell shell.

You should review the entire script and make sure you understand what the various parts do. All important configuration parameters are defined at the beginning of the script.

The script is also available in the qs-util repository over at GitHub. There you also find discussion forums etc for this and other similar scripts/tools.

# Script should be executed on the server where the Postgres repository database is running.
# The script will connect to Sense servers as specified below, shutting down Sense services before doing the db backup.
# Once backup is done the Sense services will be started again.


# Automatic execution of this script assumes there is a pgpass.conf file present in the roaming profile of the user 
# executing the script. For user joewest this would mean 'C:\Users\joewest\AppData\Roaming\postgresql\pgpass.conf'
#
# To create that file, execute the following while in the C:\Users\joewest\AppData\Roaming\postgresql (in the case of user joewest) directory:
# "localhost:4432:$([char]42):postgres:ENTER_POSTGRES_PASSWORD_HERE" | set-content pgpass.conf -Encoding Ascii

# Regarding firewalls. The following ports must be allowed inbound on the various Sense servers, to allow for services to be stopped/started:
# TCP port: 80,139,443,445,5985,5986
# UDP port: 137,138
# Ephemeral ports: (TCP 1024-4999, 49152-65535)

# The script assumes a few things about the Qlik Sense environment that will be backed up:
# - Qlik Sense is installed in C:\Program Files\Qlik\Sense on all servers in the Sense cluster
# - Sense system data is stored in in C:\ProgramData\Qlik\Sense on all servers in the Sense cluster
# - There is a system file share c$ on all servers in the Sense cluster, and that the Windows user used to run the backup script has access to those file shares

# ------- Begin config -------

$Today = Get-Date -UFormat "%Y%m%d_%H%M"

# File share on which the target directory resides
# The target directory is where all backup files will be copied
$FileShare = "\\<IP, FQDN or host name>\<fileshare name>"

# User and password for connecting to the target file share.
# If the file share is on an Active Directory connected/enabled server 
# the DestFolderUser would be something like "domain\userid" 
$DestFolderUser = "<AD domain>\<user ID>"
$DestFolderPwd = "<password>"

# Top level folder of backups, within the target file share
$FolderRoot = "$FileShare\backup\qlik_sense_system"

# Location of Postgres binary files
$PostgresLocation = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin"

# Qlik Sense app related files, for example QVDs, CSVs, config files etc.
# Enable/disable depending on whether this set of files should be included or not
# $SenseAppFiles = "\\<IP, FQDN or host name for file server>\appdata"

# Qlik Sense system related files, for example app QVFs, search indexes etc.
$SenseSystemFiles = "\\<IP, FQDN or host name for file server>\sensedata"

# Cutoff for removal of old app indexing files. Index files older than this many days will be deleted.
$SearchIndexDaysCutoff = 30

# Cutoff for removal of old system log files. Log files older than this many days will be deleted.
$SystemLogFilesDaysCutoff = 400

# Servers where Qlik Sense services are running.
$servers = @(
  "<IP, FQDN hostname or host name of 1st Sense server>"
  "<IP, FQDN hostname or host name of 2nd Sense server>"
  "<IP, FQDN hostname or host name of 3rd Sense server>"
)

# ------- End config -------



# Directory where data from this particular backup run will be stored
# Each backup run is stored in its own date-named directory
$folder = "$FolderRoot\$((Get-Date).ToString('yyyy-MM-dd'))"

# Authenticate. May not be needed if backup destination is in same Windows domain as QS server.
# This auth gives the backup script access to the top folder or file share of $FolderRoot
net use $FileShare /user:$DestFolderUser $DestFolderPwd

# Create target directory if it does not exist
If(!(test-path "$folder")) {
    New-Item -ItemType Directory -Force -Path "$folder"
}


# Loop over all servers in the QSEoW cluster, shutting down all services on each.
foreach($server in $servers) {
    write-host "----------------------------------------------------"
    write-host "Copying certificates from $server...."
    Copy-Item -Path "\\$server\C$\ProgramData\Qlik\Sense\Repository\Exported Certificates" -Destination "$folder\$server" -Recurse -Force
    
    write-host "----------------------------------------------------"
    write-host "Copying custom config files from $server...."
    Copy-Item -Path "\\$server\C$\Program Files\Qlik\Sense\Repository\Repository.exe.config" -Destination "$folder\$server" -Force
    Copy-Item -Path "\\$server\C$\Program Files\Qlik\Sense\Proxy\Proxy.exe.config" -Destination "$folder\$server" -Force
    Copy-Item -Path "\\$server\C$\Program Files\Qlik\Sense\Scheduler\Scheduler.exe.config" -Destination "$folder\$server" -Force
    Copy-Item -Path "\\$server\C$\Program Files\Qlik\Sense\ServiceDispatcher\services.conf" -Destination "$folder\$server" -Force


    write-host ""   
    write-host "Stopping Qlik Services on $server...."

    # Suffix the Stop-Service command with "-WarningAction SilentlyContinue" to suppress warning messages when a service takes long to stop
    # Removing "-Verbose" will also reduce the amount of logging done.
    Get-Service -ComputerName $server -Name QlikSenseProxyService | Stop-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseEngineService | Stop-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseSchedulerService | Stop-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSensePrintingService | Stop-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseServiceDispatcher | Stop-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseRepositoryService | Stop-Service -Verbose
    # Enable/disable as needed depending on whether log db is still in use or not
    # Get-Service -ComputerName $server -Name QlikLoggingService | Stop-Service -Verbose
}


Set-Location $PostgresLocation
write-host ""
write-host "Backing up PostgreSQL Repository Database ...."
.\pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "$folder\QSR_backup_$Today.tar" QSR

# Enable/disable as needed depending on whether log db is still in use or not
# write-host "Backing up PostgreSQL Log Database ...."
# .\pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "$folder\QLogs_backup_$Today.tar" QLogs


write-host "----------------------------------------------------"
write-host "Removing old search index files...."
$refDate = (Get-Date).AddDays(-$SearchIndexDaysCutoff)
Get-ChildItem -Path "$SenseSystemFiles\Apps\Search\" -Recurse -File | Where-Object { $_.LastWriteTime -lt $refDate } | Remove-Item -Force


write-host "----------------------------------------------------"
write-host "Removing old system log files...."
$refDate = (Get-Date).AddDays(-$SystemLogFilesDaysCutoff)
Get-ChildItem -Path "$SenseSystemFiles\ArchivedLogs\" -Recurse -File | Where-Object { $_.LastWriteTime -lt $refDate } | Remove-Item -Force


    
write-host ""
write-host "Backing up Qlik Sense files ...."

# Copy Sense system files, including app QVF files, search indexes etc.
robocopy $SenseSystemFiles $folder\sensedata /e

# Copy Sense application data, for example QVDs, CSVs, config files etc. 
# Enable/disable depending on whether this set of files should be included or not
# robocopy $SenseAppFiles $folder\appdata /e


# Loop over all servers in the QSEoW cluster, starting all services on each.
foreach($server in $servers) {
    write-host ""
    write-host "Starting Qlik Services on $server...."

    Get-Service -ComputerName $server -Name QlikSenseProxyService | Start-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseEngineService | Start-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseSchedulerService | Start-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSensePrintingService | Start-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseServiceDispatcher | Start-Service -Verbose
    Get-Service -ComputerName $server -Name QlikSenseRepositoryService | Start-Service -Verbose
    # Enable/disable as needed depending on whether log db is still in use or not
    # Get-Service -ComputerName $server -Name QlikLoggingService | Start-Service -Verbose
}


write-host "----------------------------------------------------"
write-host "Removing old backups...."

# Remove old backup folders
Get-ChildItem $FolderRoot -Force -ea 0 |
Where-Object {$_.PsIsContainer -and $_.LastWriteTime -lt (Get-Date).AddDays(-30)} |
ForEach-Object {
    write-host "Removing old directory $FolderRoot\$_ "

    Remove-Item –recurse -force –path "$FolderRoot\$_" 
    $_.FullName | Out-File $FolderRoot\deletedlog.txt -Append
}