All jobs / Database administrator

Automation interview questions

Automation questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.

  1. You are writing a small shell script that takes a backup on a Linux database server. What must it do besides running the backup command?

    The strongest answer: Stop on the first error instead of carrying on, check the backup command’s exit code, write a timestamped log, remove old files by a retention rule, and exit non-zero so the scheduler knows it failed

    Why it works: Right — a backup script that fails quietly is worse than no script, because everyone believes it is working.

    • Fail on the first error.
    • Check the exit code.
    • Exit non-zero so schedulers notice.
    • Never hardcode credentials.

  2. What do you put into a SQL script before it is allowed to run against production?

    The strongest answer: An explicit transaction, a check that this is the right server and database, the affected row count shown before the commit, batching for anything large, and a tested way back

    Why it works: Exactly — the environment check and the tested way back are the two people skip, and a second reviewer catches what you have stopped seeing.

    • Wrap it in a transaction.
    • Prove which server you are on.
    • Show the row count first.
    • Have a tested way back.

  3. The nightly maintenance job has not run for three weeks. Nobody noticed, because the dashboard only shows failures.

    What do you change?

    The strongest answer: Alert on the absence of a success, not only on errors — a heartbeat with an expected completion time, so a job that never starts also alerts — and send outcomes where people look

    Why it works: Exactly — silence is the failure mode here, and only a positive, time-bounded success signal catches a job that never ran.

    • Alert on missing success.
    • Give every job an expected time.
    • Silence must page, not reassure.
    • Send outcomes where people look.

  4. What does it take to make a maintenance script safe to run twice, and why insist on it?

    The strongest answer: It checks state before acting, so a second run changes nothing new — create if missing, skip completed work, record progress durably — because scripts do get re-run after a timeout or failover

    Why it works: Exactly — idempotence is what lets you retry without thinking, which is the whole point at three in the morning.

    • Check state before acting.
    • A second run changes nothing.
    • Record progress durably.
    • Assume retries will happen.

  5. How do your maintenance and backup jobs authenticate to the databases, without a password sitting in a script?

    The strongest answer: A managed identity where the platform offers one, otherwise a service account whose secret lives in a secret store and is fetched at run time — scoped to that job, and rotated automatically

    Why it works: Exactly — no human-held secret in the path, least privilege per job, and a rotation nobody has to remember.

    • No secrets in scripts.
    • Managed identity where possible.
    • One scoped account per job.
    • Rotate automatically; audit use.

  6. Two hundred database servers need the same configuration change, and you are writing a PowerShell script to do it.

    How do you script it so that it is safe?

    The strongest answer: Idempotent, driven by one inventory, with a report-only mode showing what would change; then roll out in waves with a check between them, log per server, and keep a way to stop

    Why it works: Exactly — a dry run plus waves means a mistake costs you five servers instead of two hundred.

    • One inventory, one script.
    • Report-only mode first.
    • Roll out in waves.
    • Log per server; keep a way back.

  7. You inherit a fleet where each DBA keeps their own scripts on their own jump box, and the checks differ from server to server.

    How do you turn that into an automation strategy?

    The strongest answer: Scripts into version control with review, a standard set of jobs every server gets, deployed from one place against an inventory, compliance reported centrally, exceptions dated — then the personal copies go

    Why it works: Exactly — the value is in one defined standard and a report naming the servers that are not on it, not in the scripts themselves.

    • Scripts in version control, reviewed.
    • One standard set per server.
    • Deploy from one place.
    • Report compliance; date exceptions.