Backup Automation with Cron, SQL Server Agent, and Shell Scripts

Why Automate Backups?

Manual backups depend on a person remembering to run them. That dependency is a single point of failure. Automated backup schedules run whether or not anyone is at the keyboard, and they create audit trails — logs that prove a backup happened and record whether it succeeded. Every major certification exam and every production environment expects DBAs to automate backups and to capture evidence of each run.

Cron and Shell Scripts for PostgreSQL (and Linux-hosted Oracle)

Cron is the standard Linux/Unix job scheduler. Each user has a crontab — a table of scheduled commands. You edit it with `crontab -e`. Each line has five time fields (minute, hour, day-of-month, month, day-of-week) followed by the command to run.

A well-written backup shell script should do four things:

1. Call the backup utility with a timestamped filename. For PostgreSQL, the utility is `pg_dump`. A timestamped name such as `mydb_2025-07-14_02-00.dump` prevents files from overwriting each other and makes it easy to identify when each backup was created.

2. Check the exit code. Every Linux command sets an exit code when it finishes: `0` means success; anything else means an error. The shell variable `$?` holds the exit code of the last command. Your script must read `$?` immediately after the backup command and branch accordingly.

3. Write a result to a log file. Append a timestamped line to a persistent log file for every run — success and failure. Operations staff and auditors rely on this log.

4. Delete backup files older than the retention window. Storage is not free. Use `find /backup/dir -name "*.dump" -mtime +N -delete` where `N` is the number of days in your retention policy. Make `N` a variable at the top of the script so it is easy to change.

After you write the script, make it executable (`chmod +x backup.sh`), then add a crontab entry. For example, to run at 2:00 AM every day:

```

0 2 * /opt/scripts/pg_backup.sh >> /var/log/pg_backup_cron.log 2>&1

```

Verify the schedule by checking `crontab -l` and — after the scheduled time passes — inspecting the log file.

Security note: Store database credentials in a restricted file (such as `~/.pgpass` for PostgreSQL, with permissions `600`) rather than in plain text inside the script. Never hard-code passwords where other users can read them.

SQL Server Agent Jobs

SQL Server Agent is a Windows service that ships with SQL Server (including Developer Edition). It must be started and set to start automatically before any scheduled job will run. Verify its status in SQL Server Configuration Manager or Services before building jobs.

A job is a named container for one or more steps and one or more schedules. Each step runs a T-SQL statement, an operating-system command, or another subsystem action. You can view all recent executions in the Job Activity Monitor and in the job history stored in the `msdb` system database.

A classic three-tier backup strategy inside a single Agent job uses separate steps:

• Full backup (Sunday): Backs up the entire database. Provides the baseline for all restores.
• Differential backup (Monday–Saturday): Backs up only pages changed since the last full backup. Smaller and faster than a full backup.
• Transaction-log backup (every 15 minutes): Captures committed transactions since the last log backup. Enables point-in-time recovery and keeps the log file from growing without bound (in Full recovery model).

Use job-step conditions ("on success go to next step," "on failure write to history") to control flow. Configure the job to write history so that the Activity Monitor shows success or failure for every execution. Set the maximum job-history rows high enough that you retain at least several days of evidence.

Logging and Email-Alert Placeholders

Both platforms support alerting. In bash scripts, you can add a placeholder call to a mail utility (such as `sendmail` or `mail`) inside the failure branch, commented with a note explaining what SMTP configuration is needed. In SQL Server Agent, you can configure Operators (named email addresses) and attach notifications to a job so that the Agent emails the operator on failure. Even if email is not yet configured in your lab, your job definition should include the notification step as a placeholder.

Good log entries include: timestamp, database name, backup file path, file size if available, and success/failure status. Consistent log formats make it far easier to write monitoring scripts or feed entries into a centralized logging system later.

Code Quality and Peer Review

Automation scripts are production assets. They should be:

• Commented — explain what each major block does and why.
• Parameterized — put retention days, backup directory, database name, and other settings in variables at the top of the script, not scattered as magic numbers.
• Readable — use meaningful variable names and consistent indentation.

Peer review catches mistakes before they reach production. Use the provided checklist systematically: timestamped filenames, exit-code checking, log writing, retention cleanup, and commented code. Give specific, constructive written feedback, then revise your own script based on what your reviewer found.

A Note on Safety and Access Control

Backup scripts run with real database credentials and write files to disk. Follow the principle of least privilege: the OS user running the script should have only the database permissions needed to perform the backup, and only write access to the backup directory. Restrict the backup directory so that other OS users cannot read the dump files, which may contain sensitive data. Log files should also be protected from unauthorized access.