SQL Backup for Windows — version 1.1.6

PowerShell 5.1 utility for local Microsoft SQL Server FULL and differential backups. One application and one .env configuration can serve multiple Windows Scheduled Tasks.

Important behavior

• -BackupType FULL or -BackupType DIFF has priority over BACKUP_MODE in .env.
• BACKUP_MODE=FULL|DIFF|BOTH determines which Scheduled Tasks the installer creates.
• master, model and msdb are automatically included only in FULL runs when enabled.
• System databases are always excluded from DIFF runs.
• Every backup uses SQL Server CHECKSUM.
• Compression is used automatically whenever the detected edition supports it.
• FULL backups can be checked by RESTORE VERIFYONLY WITH CHECKSUM.
• Retention cleanup runs only after successful backup and requested verification.
• FULL and DIFF files have separate retention and directories.
• FULL and DIFF can have separate user-database lists and schedules.
• A lock file prevents concurrent FULL/DIFF processes.
• Index maintenance is intentionally not part of this application.

RESTORE VERIFYONLY improves detection of unusable backup media, but it is not a replacement for a regular test restore followed by database consistency checks.

Files

• SqlBackup.ps1 — backup application.
• example.env — documented configuration template.
• Install-SqlBackup.ps1 — prerequisites, folders, ACLs and optional Scheduled Tasks.
• Start-Installation.cmd — recommended interactive installer launcher; keeps its window open.
• .env — local configuration created by the administrator; do not distribute it.

Initial installation

Extract the complete package, copy and edit the configuration:

Copy-Item .\example.env .\.env
notepad .\.env

The simplest installation method is to right-click Start-Installation.cmd and select Run as administrator. After prerequisite checks, the installer asks whether it should create or update the configured Scheduled Tasks. If confirmed, a Windows credential dialog requests the task account and password. The launcher always keeps its window open so that the final status or error remains visible.

Alternatively, run Windows PowerShell 5.1 as Administrator:

.\Install-SqlBackup.ps1

The interactive Yes/No question is the default. For automation, force either result with -CreateScheduledTasks or -SkipScheduledTasks. TaskUser is optional and only pre-fills the credential dialog:

.\Install-SqlBackup.ps1 -TaskUser 'DOMAIN\svc_sqlbackup' -CreateScheduledTasks
.\Install-SqlBackup.ps1 -SkipScheduledTasks

The installer securely prompts for the task account password. Windows Task Scheduler stores the supplied credentials using password logon, so the jobs run even when that user is not logged on. Both tasks are registered with the Highest run level. The password is not written to .env or a log. After creation, Task Scheduler is opened so the resulting settings can be inspected.

Every installation writes a transcript to the install-logs subfolder. If that folder is not writable, it uses %TEMP%\SqlBackupInstallerLogs. The PowerShell installer waits for Enter before closing unless -NoPause is supplied.

If .env is absent, or example.env itself is passed as the configuration, installation stops and requests a copied and completed .env file.

The installer restricts .env to local Administrators, SYSTEM and the selected task account. It grants Modify access on backup folders to the task account and the detected local SQL Server service account.

Two schedules, one installation

The installer creates commands equivalent to:

# Daily FULL task, for example at 05:00
.\SqlBackup.ps1 -ConfigPath .\.env -BackupType FULL

# Repeating DIFF task, for example hourly from 06:00 through 18:00
.\SqlBackup.ps1 -ConfigPath .\.env -BackupType DIFF

No duplicate application or configuration is required. Set BACKUP_MODE=BOTH to create both tasks, FULL for only the FULL task, or DIFF for only the DIFF task. Each task passes its own explicit -BackupType parameter.

When starting SqlBackup.ps1 manually, the command-line -BackupType always has precedence. If BACKUP_MODE=BOTH and -BackupType is omitted, the run stops with an explanatory error; it does not run FULL and DIFF immediately after each other.

FULL_DATABASES and DIFF_DATABASES are independent comma-separated lists. master, model and msdb do not need to be listed; the application adds them only to FULL according to INCLUDE_SYSTEM_DATABASES_IN_FULL.

Dry run

Run the dry run under the same Windows account that will execute the Scheduled Tasks:

.\SqlBackup.ps1 -ConfigPath .\.env -BackupType FULL -DryRun
.\SqlBackup.ps1 -ConfigPath .\.env -BackupType DIFF -DryRun

In PowerShell ISE, invoke the script directly with &; do not start a nested powershell.exe process:

& 'D:\sqlbackup\SqlBackup.ps1' -ConfigPath 'D:\sqlbackup\.env' -BackupType FULL -DryRun

Scheduled Tasks still correctly use powershell.exe; the ISE limitation affects only interactive nested execution.

Dry run connects to SQL Server and reports:

• effective backup type and databases;
• Windows or SQL login;
• destination directories;
• backup permission for each database;
• compression availability;
• FULL verification setting;
• whether the latest non-copy-only full backup exists and its file is accessible before a DIFF run.

It does not create a backup, apply retention, or send email.

Required permissions

Windows task account

When SQL credentials in .env are empty, the task account is also the SQL login. It needs:

• Log on as a batch job on Windows (normally granted when the task is registered by an administrator);
• Read access to the application and .env;
• Modify access to BACKUP_ROOT, LOG_ROOT and the lock-file directory, because PowerShell creates directories, logs and removes expired backup files;
• a SQL Server login for the same Windows identity;
• BACKUP DATABASE permission in every selected database;
• access to SQL metadata used by the checks and to msdb backup history;
• sufficient restore/verification permission when FULL_VERIFY_AFTER_BACKUP=true.

Membership in SQL Server sysadmin is the simplest operational configuration and supports all current checks, system-database backups and VERIFYONLY, but it is highly privileged. If organizational policy requires least privilege, grant and test the individual permissions on every target database and rerun -DryRun as the task account. Permission design for RESTORE VERIFYONLY should also be tested on the exact SQL Server version in use.

SQL Server service account

The SQL Server Database Engine service, not PowerShell, writes .bak contents. Its Windows service account therefore needs Modify access to BACKUP_ROOT and all database/type subfolders. The installer detects the local default or named SQL instance service and grants this access.

SQL Authentication

If SQL_USERNAME and SQL_PASSWORD are populated, the SQL permission checks use that login instead of the Windows task identity. Both values must be set together. Plaintext secret storage is retained in version 1.1 by request; protect .env and never commit or email it.

Verification overrides

FULL_VERIFY_AFTER_BACKUP controls the default. A one-off command can override it:

.\SqlBackup.ps1 -BackupType FULL -VerifyAfterBackup
.\SqlBackup.ps1 -BackupType FULL -SkipVerifyAfterBackup

Verification overrides have no effect on a DIFF run in version 1.1.

Exit codes

• 0 — successful run, dry run, or run completed with nonfatal warnings;
• 1 — one or more database backups failed;
• 2 — fatal configuration, connection, prerequisite, permission or startup error.

Task Scheduler should treat every nonzero code as failure.

Directory layout

D:\SQLBackups\
  ApplicationDb\
    FULL\
    DIFF\
  master\
    FULL\
  model\
    FULL\
  msdb\
    FULL\
  _logs\
  _sqlbackup.lock

Each run gets its own timestamped log. Backup names include database, type and millisecond timestamp.

Operational notes

• Run FULL before the first DIFF. DIFF skips a database if SQL Server has no non-copy-only full backup in msdb, or its latest base backup file is not locally accessible.
• Do not use a COPY_ONLY full backup as the scheduled DIFF base.
• Keep at least one recoverable FULL file for every retained DIFF chain.
• Test real restoration regularly on an isolated SQL Server.
• Monitor Scheduled Task exit codes, email delivery and free disk space.
• Version 1.1 assumes the script runs on the same Windows host as SQL Server.