This wiki page documents the implementation of a robust backup schedule for a SQL Server using PowerShell scripts and Windows Task Scheduler. The solution leverages Ola Hallengren’s Maintenance Solution to perform full, differential, and transaction log backups, with tasks scheduled to run automatically.
The backup schedule includes:
The scripts use Ola Hallengren’s DatabaseBackup stored procedure, and Windows Task Scheduler automates their execution.
SqlServer) installed (Install-Module -Name SqlServer if needed).NT AUTHORITY\SYSTEM) must have access to scripts and logs.C:\Scripts (adjust as needed).C:\Logs for error logging.MaintenanceSolution.sql from Ola Hallengren’s website.DatabaseBackup stored procedure in the master database.SELECT * FROM sys.procedures WHERE name = 'DatabaseBackup'.The following scripts call the DatabaseBackup stored procedure to perform backups. They include error handling and SSL workaround for SQL Server instances using untrusted certificates. Save them in C:\Scripts.
Performs a full backup of specified databases.
Import-Module SqlServer
$server = "your_sql_server_name" # Replace with your SQL Server instance
$databases = "USER_DATABASES" # Or specific databases, e.g., 'DB1,DB2'
$backupDirectory = "your_backup_directory" # Replace with backup path, e.g., 'D:\Backups'
$logFile = "C:\Logs\BackupLog.txt"
try {
$query = @"
EXECUTE dbo.DatabaseBackup
@Databases = '$databases',
@Directory = '$backupDirectory',
@BackupType = 'FULL'
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query -TrustServerCertificate
Add-Content -Path $logFile -Value "$(Get-Date): Full backup completed successfully."
}
catch {
$errorMessage = "$(Get-Date): Full backup failed. Error: $($_.Exception.Message)"
Add-Content -Path $logFile -Value $errorMessage
}
Performs a differential backup of specified databases.
Import-Module SqlServer
$server = "your_sql_server_name"
$databases = "USER_DATABASES"
$backupDirectory = "your_backup_directory"
$logFile = "C:\Logs\BackupLog.txt"
try {
$query = @"
EXECUTE dbo.DatabaseBackup
@Databases = '$databases',
@Directory = '$backupDirectory',
@BackupType = 'DIFF'
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query -TrustServerCertificate
Add-Content -Path $logFile -Value "$(Get-Date): Differential backup completed successfully."
}
catch {
$errorMessage = "$(Get-Date): Differential backup failed. Error: $($_.Exception.Message)"
Add-Content -Path $logFile -Value $errorMessage
}
Performs a transaction log backup of specified databases.
Import-Module SqlServer
$server = "your_sql_server_name"
$databases = "USER_DATABASES"
$backupDirectory = "your_backup_directory"
$logFile = "C:\Logs\BackupLog.txt"
try {
$query = @"
EXECUTE dbo.DatabaseBackup
@Databases = '$databases',
@Directory = '$backupDirectory',
@BackupType = 'LOG'
"@
Invoke-Sqlcmd -ServerInstance $server -Query $query -TrustServerCertificate
Add-Content -Path $logFile -Value "$(Get-Date): Transaction log backup completed successfully."
}
catch {
$errorMessage = "$(Get-Date): Transaction log backup failed. Error: $($_.Exception.Message)"
Add-Content -Path $logFile -Value $errorMessage
}
your_sql_server_name with your SQL Server instance (e.g., SERVER01 or SERVER01\INSTANCE).your_backup_directory with the backup path (e.g., D:\Backups or \\NetworkShare\Backups).$databases to target specific databases or use SYSTEM_DATABASES for system databases.C:\Logs exists for logging.The following PowerShell commands create scheduled tasks to run the backup scripts automatically. Run these commands in PowerShell as Administrator.
$action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NoProfile -File 'C:\Scripts\FullBackup.ps1'"
$trigger = New-ScheduledTaskTrigger -Daily -At "2:00 AM"
$principal = New-ScheduledTaskPrincipal -UserId "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount
Register-ScheduledTask -TaskName "SQLFullBackup" -Action $action -Trigger $trigger -Principal $principal -Description "Daily full backup of SQL Server databases"
$action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NoProfile -File 'C:\Scripts\DiffBackup.ps1'"
$trigger = New-ScheduledTaskTrigger -Once -At "6:00 AM" -RepetitionInterval (New-TimeSpan -Hours 4) -RepetitionDuration (New-TimeSpan -Days 1)
$principal = New-ScheduledTaskPrincipal -UserId "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount
Register-ScheduledTask -TaskName "SQLDiffBackup" -Action $action -Trigger $trigger -Principal $principal -Description "Differential backup every 4 hours"
$action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-NoProfile -File 'C:\Scripts\LogBackup.ps1'"
$trigger = New-ScheduledTaskTrigger -Once -At "12:00 AM" -RepetitionInterval (New-TimeSpan -Minutes 15) -RepetitionDuration (New-TimeSpan -Days 1)
$principal = New-ScheduledTaskPrincipal -UserId "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount
Register-ScheduledTask -TaskName "SQLLogBackup" -Action $action -Trigger $trigger -Principal $principal -Description "Transaction log backup every 15 minutes"
C:\Scripts or update the -Argument parameter with the correct path.NT AUTHORITY\SYSTEM for elevated privileges. If using a specific account, replace -UserId and ensure it has script, backup directory, and log directory access.-Once with repetition to achieve daily schedules (4 hours and 15 minutes, respectively).Check Task Scheduler:
taskschd.msc).SQLFullBackup, SQLDiffBackup, SQLLogBackup) exist under the root path (\).SQLFullBackup: Daily at 2:00 AM.SQLDiffBackup: Starts at 6:00 AM, repeats every 4 hours for 24 hours.SQLLogBackup: Starts at 12:00 AM, repeats every 15 minutes for 24 hours.Test Scripts Manually:
C:\Scripts\FullBackup.ps1, C:\Scripts\DiffBackup.ps1, C:\Scripts\LogBackup.ps1..bak (full/differential) or .trn (log) files.C:\Logs\BackupLog.txt for success or error messages.Test Tasks:
Test Restores:
SSL Certificate Error:
-TrustServerCertificate to bypass untrusted certificate errors. For production, configure SQL Server with a trusted certificate (Microsoft SQL Server Encryption).Syntax Error in Query:
$query is correctly formatted in the scripts.DatabaseBackup procedure fails.Script Errors:
SqlServer module is installed (Install-Module -Name SqlServer).your_sql_server_name, your_backup_directory) are replaced.DatabaseBackup procedure errors.Task Failures:
NT AUTHORITY\SYSTEM or the specified account has permissions to C:\Scripts, C:\Logs, and the backup directory.Execution Policy:
Get-ExecutionPolicy).RemoteSigned if needed: Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned.Transaction Log Backup Issues:
SELECT name, recovery_model_desc FROM sys.databases.@Verify = 'Y' to the DatabaseBackup procedure to check backup integrity.@CleanupTime = 4320 (3 days) to delete old backups automatically.C:\Logs\BackupLog.txt regularly or use SQL Server Agent alerts for failures.Last Updated: May 17, 2025