zf-sql/README.md

# backupmult.ps1 - Parallel SQL Server Database Backup Script

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

1. **Download the Script**:
   ```powershell
   # Place backupmult.ps1 in your preferred scripts directory
   # Example: C:\Rubrik\Scripts\
   ```

2. **Install Ola Hallengren Maintenance Solution**:
   ```sql
   -- Run on your SQL Server instance
   -- Download from https://ola.hallengren.com/
   -- Execute the installation script
   ```

3. **Install SQL Server PowerShell Module** (if not already installed):
   ```powershell
   Install-Module -Name SqlServer -AllowClobber -Force
   ```

4. **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
```powershell
.\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
```powershell
.\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
```powershell
.\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)
```powershell
.\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:

1. **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

2. **DIFFERENTIAL Backup**:
   - Scheduled: Daily (except full backup days)
   - Flag file: `last_diff.flag` in the first backup directory

3. **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
```powershell
Install-Module -Name SqlServer -AllowClobber -Force
```

#### "Ola Hallengren procedures not found"
**Solution**: Install Ola Hallengren Maintenance Solution on the SQL Server instance
```sql
-- 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.</content>
<parameter name="filePath">README.md