Scheduled n8n Backups

@echo off
setlocal

REM Backup Script for n8n Configuration and Database Files

:: This will automate the process of backing up the 'config' and 'database.sqlite' files
:: from n8n's main folder to a specified destination as a password-protected 'backup.zip' file.
:: The backup is performed only if either of these files have been modified since the last backup.
::
:: The script uses 7-Zip for compression and encryption, and PowerShell for file timestamp retrieval.
::
:: Key Features:
:: - Checks if 7-Zip and PowerShell are installed.
:: - Retrieves and compares the last modification timestamps of the files.
:: - Performs a backup only if the files have been modified since the last backup.
:: - Writes human-readable timestamps to the backup timestamps file.
::
:: Configuration:
:: - SEVENZIP: Path to the 7-Zip executable.
:: - SOURCE_DIR: Directory containing the files to back up.
:: - DESTINATION: Path to the backup ZIP file.
:: - TIMESTAMP_FILE: File to store the last backup timestamps.
:: - PASSWORD: Password for the encrypted ZIP file.

:: Configuration
set "SEVENZIP=C:\Program Files\7-Zip\7z.exe"
set "SOURCE_DIR=%USERPROFILE%\.n8n"
set "DESTINATION=X:\Storage\.n8n\backup.zip"
set "TIMESTAMP_FILE=%USERPROFILE%\.n8n\backup_timestamps.txt"
set "PASSWORD=password123" || REM Set the 7-Zip password to encrypt the backups

:: Check if 7-Zip exists
if not exist "%SEVENZIP%" (
    echo 7-Zip is not installed or the path is incorrect.
    exit /b 1
)

:: Check if PowerShell is installed
powershell -Command "Write-Host 'PowerShell is working'" >nul 2>&1
if errorlevel 1 (
    echo PowerShell is not installed or not accessible.
    exit /b 1
)

:: Retrieve last backup timestamps
if exist "%TIMESTAMP_FILE%" (
    for /f "tokens=1,*" %%A in (%TIMESTAMP_FILE%) do (
        if "%%A"=="config" set LAST_CONFIG_TIMESTAMP_HUMAN=%%B
        if "%%A"=="database.sqlite" set LAST_DATABASE_TIMESTAMP_HUMAN=%%B
    )
)

:: Debug output to verify last timestamps
echo Last config timestamp (human-readable): %LAST_CONFIG_TIMESTAMP_HUMAN%
echo Last database.sqlite timestamp (human-readable): %LAST_DATABASE_TIMESTAMP_HUMAN%

:: Get current timestamps using PowerShell
for %%F in (config database.sqlite) do (
    echo Fetching timestamp for %%F
    for /f "delims=" %%T in ('powershell -Command "Get-Item \"%SOURCE_DIR%\%%F\" | Select-Object -ExpandProperty LastWriteTime | ForEach-Object { $_.ToString('yyyy-MM-dd HH:mm:ss') }"') do (
        set "CURRENT_%%F_TIMESTAMP_HUMAN=%%T"
    )
)

:: Debug output to verify current timestamps
echo Current config timestamp (human-readable): %CURRENT_config_TIMESTAMP_HUMAN%
echo Current database.sqlite timestamp (human-readable): %CURRENT_database.sqlite_TIMESTAMP_HUMAN%

:: Compare timestamps and decide if backup is needed
set NEED_BACKUP=0

if not defined LAST_CONFIG_TIMESTAMP_HUMAN (
    set NEED_BACKUP=1
) else (
    if "%CURRENT_config_TIMESTAMP_HUMAN%" gtr "%LAST_CONFIG_TIMESTAMP_HUMAN%" (
        set NEED_BACKUP=1
    )
)

if not defined LAST_DATABASE_TIMESTAMP_HUMAN (
    set NEED_BACKUP=1
) else (
    if "%CURRENT_database.sqlite_TIMESTAMP_HUMAN%" gtr "%LAST_DATABASE_TIMESTAMP_HUMAN%" (
        set NEED_BACKUP=1
    )
)

:: Perform backup if needed
if "%NEED_BACKUP%"=="1" (
    echo Backing up files...
    "%SEVENZIP%" a -tzip -p%PASSWORD% "%DESTINATION%" "%SOURCE_DIR%\database.sqlite" "%SOURCE_DIR%\config"

    :: Update timestamp file
    > "%TIMESTAMP_FILE%" (
        echo config %CURRENT_config_TIMESTAMP_HUMAN%
        echo database.sqlite %CURRENT_database.sqlite_TIMESTAMP_HUMAN%
    )
    echo Backup completed.
) else (
    echo No backup needed. Files are up-to-date.
)

endlocal