pg_dump Fails to Create Backup in Windows Task Scheduler

pg-dumppostgresqlpostgresql-9.4scheduled-taskswindows

I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with no problem, I have to run the pg_dump as a System because I want it to run even if the user is not logged on. Unfortunately, to run a task when a user is logged off, you have to run it in System user and that doesn't work for pg_dump. I couldn't see any error. It simply creates an empty backup file and after that, pg_dump keeps on running, blocking future runs.

Part of my pg_dump code is the following.

%PG_BIN%pg_dump -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f %BACKUP_FILE% %DB%

Part of my Schtaks code that creates the task.

SchTasks /Create /SC MINUTE /mo 30 /RU "SYSTEM" /TN "Database Backup" /TR "%SCRIPT_DIR%backup_db.bat"

Best Answer

I found a work around for the problem. I had to change the run as user to the current logged in user after first setting it to SYSTEM. This will prompt the user to enter the password. This change doesn't remove the radio selection on "Run whether the user is logged on or not" and pg_dump works with the current user. The nice thing is, the code runs silently. The code I added under the schedule task creator batch script is the follow.

SchTasks /Change /TN "Database Backup" /RU "%username%" /RP *

Related Solutions

Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

SQL Job with one Job Step
The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

an instance-level Credential
a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -

Sql-server – Automate backup using Windows Task Scheduler

I recommend using Ola Hallengren's backup solution.

The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure.

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

Best Answer

Related Solutions

Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler

Sql-server – Automate backup using Windows Task Scheduler

Related Question