SQL Server backup using powershell in Task scheduler

Question

I am using PowerShell to backup a SQL Server database and use Windows task scheduler to schedule the script

Test

1. Run the script manually, it works.
   Both SQL Server backup file (test.bak) and test file (HelloWorld.txt) were created.

2. Run via Windows task scheduler, only HelloWorld.txt was created, the SQL Server backup file was not created.

So at least;

1. The PowerShell script did run.
2. Credentials were ok for the manual run

Problem

Why is the SQL SErver backup file not created when run from Windows task scheduler?

Here is my SQLbackup.ps1 file:

$credential = import-clixml -path c:\pp\test\sqlcred.xml
Backup-SqlDatabase -ServerInstance Server264\SUPPORT2K19 -Database PPTest -BackupAction Database -BackupFile "C:\PP\Test\Test.bak" -Credential $credential

Set-Content -Path "C:\PP\Test\HelloWorld.txt" -Value "Hello World"

Task scheduler settings are:

• User is SYSTEM,
• Select run whether user is logged on on or not
• Tick run with highest privilege

In action tab:

• Program: powershell
• Add arguments: -executionpolicy bypass -file C:\PP\Test\sqlbackup.ps1

Any suggestion why the SQL Server backup file was not created?

Answer 1

I think the cmdlet Backup-SqlDatabase produce an error, and you do not have a mechanism to capture that.

Try implementing try/catch to capture the error and then you can do what you want with that information, for example :

$credential = import-clixml -path c:\pp\test\sqlcred.xml
try {
    Backup-SqlDatabase -ServerInstance Server264\SUPPORT2K19 -Database PPTest -BackupAction Database -BackupFile "C:\PP\Test\Test.bak" -Credential $credential -ErrorAction Stop
}
catch {
    Set-Content -Path "C:\PP\Test\Error.txt" -Value "$_"
}
Set-Content -Path "C:\PP\Test\HelloWorld.txt" -Value "Hello World"

This would capture the potential error and write it in a Error.txt file, same way as the HelloWorld.txt.

Important part is that Try/Catch capture only terminating errors, so we add the -ErrorAction Stop at the end of the cmdlet to make any error terminating so we can execute the script in the catch script block.

You can read more about it in the Microsoft docs : https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_try_catch_finally?view=powershell-7.2

P.S. The documentation is for Powershell 7.2