Backup and Restore

Backup Strategy Overview

SQL Server Backup

-- Full backup (run daily via SQL Agent job)
BACKUP DATABASE [OctopusDB]
TO DISK = 'D:\Backups\OctopusDB_Full_$(date).bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Differential backup (run every 4 hours)
BACKUP DATABASE [OctopusDB]
TO DISK = 'D:\Backups\OctopusDB_Diff_$(datetime).bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- Transaction log backup (run every 15 minutes)
BACKUP LOG [OctopusDB]
TO DISK = 'D:\Backups\OctopusDB_Log_$(datetime).trn'
WITH COMPRESSION, CHECKSUM;

-- Restore procedure:
RESTORE DATABASE [OctopusDB] FROM DISK = 'OctopusDB_Full.bak' WITH NORECOVERY;
RESTORE DATABASE [OctopusDB] FROM DISK = 'OctopusDB_Diff.bak' WITH NORECOVERY;
RESTORE LOG      [OctopusDB] FROM DISK = 'OctopusDB_Log.trn'  WITH RECOVERY;

Qdrant Snapshot Backup

# Create a snapshot for each agent collection
# (call via Qdrant REST API or schedule as a daily script)

# List all collections
GET http://qdrant:6333/collections

# Create snapshot for a specific collection
POST http://qdrant:6333/collections/agent_{agentId}/snapshots

# Response:
# {
#   "result": {
#     "name": "agent_7f3a8b2c-2025-03-01-08-00-00.snapshot",
#     "creation_time": "2025-03-01T08:00:00Z",
#     "size": 45231872
#   }
# }

# Download the snapshot file for off-site storage
GET http://qdrant:6333/collections/agent_{agentId}/snapshots/{snapshotName}

# Restore a snapshot
POST http://qdrant:6333/collections/agent_{agentId}/snapshots/recover
{
  "location": "http://backup-storage/agent_{agentId}.snapshot"
}

Backup Automation Script (PowerShell)

# OctopusBackup.ps1 — Run daily via Windows Task Scheduler or Azure Automation

param(
    [string]$QdrantUrl       = "http://qdrant:6333",
    [string]$BackupRootPath  = "D:\OctopusBackups",
    [string]$SqlInstance     = "localhost",
    [string]$SqlDatabase     = "OctopusDB"
)

$date = Get-Date -Format "yyyyMMdd-HHmm"

# 1. SQL Full Backup
$sqlBackupPath = "$BackupRootPath\SQL\${SqlDatabase}_Full_${date}.bak"
Invoke-Sqlcmd -ServerInstance $SqlInstance -Query @"
BACKUP DATABASE [$SqlDatabase]
TO DISK = '$sqlBackupPath'
WITH COMPRESSION, CHECKSUM
"@

# 2. Qdrant: enumerate collections and snapshot each
$collections = (Invoke-RestMethod "$QdrantUrl/collections").result.collections
foreach ($col in $collections) {
    $name = $col.name
    Invoke-RestMethod "$QdrantUrl/collections/$name/snapshots" -Method POST
    Write-Output "Snapshotted collection: $name"
}

Write-Output "Backup complete: $date"

Disaster Recovery Checklist