A PowerShell script that performs parallel database backups using Ola Hallengren's DatabaseBackup stored procedure with the DatabasesInParallel feature for optimal performance and load distribution.

Features

Parallel Processing: Utilizes Ola Hallengren's DatabasesInParallel=Y feature to automatically distribute database backup workloads across multiple concurrent jobs
Smart Backup Type Detection: Automatically determines whether to run FULL, DIFFERENTIAL, or LOG backups based on schedule and overdue status
Multi-Directory Support: Supports striped backups across multiple directories for improved I/O performance
Comprehensive Logging: Thread-safe logging with job-specific log files that are consolidated into a main log
Error Handling: Robust error detection and reporting with detailed SQL error information
Job Monitoring: Real-time monitoring of backup job progress and completion status
Mount Point Validation: Optional mount point checking to ensure backup destinations are accessible

Requirements

System Requirements

PowerShell: Version 5.1 or higher
Operating System: Windows Server 2016 or later (or Windows 10/11 for development/testing)
SQL Server: SQL Server 2016 or later (Express, Standard, Enterprise, or Developer editions)
Permissions: SQL Server sysadmin privileges or appropriate database backup permissions

Software Dependencies

Ola Hallengren Maintenance Solution: Must be installed on the target SQL Server instance
- Download from: https://ola.hallengren.com/
- Install the DatabaseBackup stored procedure
SQL Server PowerShell Module: Either SqlServer or SQLPS module must be available
- Install with: Install-Module -Name SqlServer -AllowClobber

Network Requirements

SQL Server Connectivity: The script must be able to connect to the target SQL Server instance
Backup Destination Access: Read/write access to all specified backup directories
Mount Points: If using mount points, they must be accessible and have sufficient space

Installation

Download the Script:

# Place backupmult.ps1 in your preferred scripts directory
# Example: C:\Rubrik\Scripts\

Install Ola Hallengren Maintenance Solution:

-- Run on your SQL Server instance
-- Download from https://ola.hallengren.com/
-- Execute the installation script

Install SQL Server PowerShell Module (if not already installed):
```
Install-Module -Name SqlServer -AllowClobber -Force
```
Verify Permissions:
- Ensure the account running the script has SQL Server sysadmin privileges
- Verify write access to backup directories and log file locations

Usage

Basic Syntax

.\backupmult.ps1 -SqlInstance "ServerName\InstanceName" [parameters]

Parameters

Parameter	Type	Required	Default	Description
`SqlInstance`	String	Yes	-	SQL Server instance name (e.g., "SERVER01\SQL2019")
`Directories`	String	No	Auto-generated	Comma-separated list of backup directories
`Jobs`	Integer	No	2	Number of parallel backup jobs (1-8)
`Force`	Switch	No	False	Skip mount point accessibility checks

Examples

Basic Usage with Default Settings

.\backupmult.ps1 -SqlInstance "PRODSQL01\SQL2019"

Uses 2 parallel jobs
Auto-generates 4 backup directories under C:\Rubrik\SQL2019\
Performs appropriate backup type based on schedule

Custom Directories and Job Count

.\backupmult.ps1 -SqlInstance "TESTSQL\SQL2022" -Directories "D:\Backups\Dir1, E:\Backups\Dir2, F:\Backups\Dir3" -Jobs 3

Uses 3 parallel jobs
Backs up to specified directories
Striped backup across 3 volumes

Force Mode (Skip Mount Checks)

.\backupmult.ps1 -SqlInstance "DEVSQL" -Force

Skips mount point accessibility validation
Useful for scenarios where mount points may not be immediately accessible

Backup Type Logic

The script automatically determines the backup type based on:

FULL Backup:
- Scheduled: Every Thursday (configurable)
- Overdue: If more than 7 days since last full backup
- Flag file: last_full.flag in the first backup directory
DIFFERENTIAL Backup:
- Scheduled: Daily (except full backup days)
- Flag file: last_diff.flag in the first backup directory
LOG Backup:
- Default: When full or diff already completed for the day
- Continuous: For transaction log backups

Output and Logging

Console Output

Clean, essential status messages
Job progress and completion notifications
Error messages and warnings
Suppresses verbose Ola Hallengren messages for better readability

Log Files

Main Log: C:\Rubrik\backup-multi-{InstanceName}.log
- Consolidated log with all job outputs
- Timestamped entries with job identification
Job Logs: C:\Rubrik\backup-multi-{InstanceName}-job{N}.log
- Individual job-specific logs
- Automatically consolidated into main log after completion
- Include detailed SQL messages and errors

Log Format

2025-10-22 14:30:15 [JOB-1] Starting backup job
2025-10-22 14:30:15 [JOB-1] Executing backup command...
2025-10-22 14:30:16 [JOB-1] Backup completed successfully

Error Handling

SQL Connection Errors

Detected and reported with detailed error information
Includes procedure name and line number for SQL errors
Jobs marked as "FAILED" with comprehensive error details

Mount Point Issues

Validates backup directory accessibility (unless -Force is used)
Reports inaccessible mount points before starting backups
Prevents failed backups due to storage issues

Job Monitoring

Real-time monitoring of PowerShell background jobs
Automatic cleanup of completed jobs
Timeout handling for unresponsive jobs

Troubleshooting

Common Issues

"No SQL Server PowerShell module found"

Solution: Install the SqlServer module

Install-Module -Name SqlServer -AllowClobber -Force

"Ola Hallengren procedures not found"

Solution: Install Ola Hallengren Maintenance Solution on the SQL Server instance

-- Download and execute the installation script from https://ola.hallengren.com/

"Access denied" to backup directories

Solution: Ensure the service account has read/write permissions to all backup directories

Jobs fail with mount point errors

Solution: Use -Force parameter to skip mount checks, or ensure mount points are accessible

Log files not consolidating

Solution: Check permissions on log file directory and ensure no file locks exist

Debug Mode

Enable debug output by modifying the script to show debug messages:

Look for Write-Output "DEBUG:" lines in the job scriptblock
Check job-specific log files for detailed error information

Performance Tuning

Job Count: Start with 2-4 jobs and monitor SQL Server performance
Directories: Use separate physical disks/volumes for striped backups
Memory: Ensure adequate memory for parallel operations
Network: Consider network bandwidth for remote backup destinations

Configuration

Backup Schedule

Full Backups: Thursdays (modify $fullBackupDay variable)
Overdue Threshold: 7 days (modify $fullBackupOverdueDays variable)
Cleanup Time: 168 hours (7 days) for full/diff, 24 hours for log

Directory Structure

Default structure: C:\Rubrik\{InstanceName}\Dir1, Dir2, Dir3, Dir4

Modify the default directory logic in the script for custom structures
Ensure all directories exist and are writable

Log File Location

Default: C:\Rubrik\backup-multi-{InstanceName}.log

Modify $logFile variable for custom log locations
Ensure log directory exists and is writable

Security Considerations

SQL Permissions: Requires sysadmin or appropriate backup permissions
File System Access: Read/write access to backup directories and log locations
Service Account: Use dedicated service account with minimal required permissions
Log Security: Log files may contain sensitive database information

Support and Maintenance

Monitoring

Regularly review log files for errors and warnings
Monitor backup completion times and success rates
Check disk space usage in backup directories

Maintenance Tasks

Clean up old backup files according to retention policies
Archive and rotate log files periodically
Update Ola Hallengren scripts when new versions are available

Version History

Track changes to the backup script
Test updates in development environments before production deployment

License

This script is provided as-is for database backup automation. Ensure compliance with your organization's backup and retention policies. README.md