Database administrator interview questions, with worked answers

How to use these questions

Answer each question in the terms of the product the ad names, then add a real case: the database, the size, what failed, what you did and how long it took. DBA interviewers often ask you to talk through a night on call, so pick one and know its timeline.

What a database administrator is expected to know

• Recovery: backups, restores, point-in-time recovery and the recovery objectives the business agreed.
• Availability: replication, standby databases, failover and planned maintenance with little downtime.
• Performance: SQLRead and tune any query: joins, indexes, execution plans, transactions and locks, in the dialect of your product., execution plans, indexes, statistics, locks and the server's memory and storage.
• Security and change: least privilege, encryption, audit trails, patches and upgrades through a change processIncidents, changes and problems in large IT departments; a database change usually passes a change process..
• Products and platforms: at least one of PostgreSQLRun it in production: replication, backups with point-in-time recovery, vacuum, upgrades and extensions., Oracle Database, Microsoft SQL Server or MySQL in depth, on LinuxMost databases run on Linux: storage, memory, file systems, services and the logs that explain a crash. or Windows, on premises or in the cloud.

Backup, restore and availability

How do you design backups for a database, and how do you know a restore will work?

What they check: that you design from the business's recovery needs and test restores.

Short answer: "I start from two figures the business agrees: the recovery point objective, how much data it may lose, and the recovery time objective, how long it may be down. From them I choose full and incremental backups plus the transaction logs, keep copies in another place and one that cannot be changed, and restore a real backup on a schedule to prove it works and to measure how long it takes."

The full answer: A backup nobody has restored is a hope, not a backup. Each product has its own tools, such as RMAN for Oracle Database, native backups for SQL Server, or base backups with archived logs for PostgreSQL, but the plan is the same: a copy away from the server, one that ransomware cannot encrypt, a written restore procedure, and a test that checks the data, not only that the restore finished. Watch backup jobs with alerts, because a silent failure is found on the day it hurts most.

A weak answer: "We back up every night, so we are safe." Follow-up: "The business says it can lose five minutes at most." Explain why nightly backups cannot meet that and how log backups or replication can.

Someone deleted rows from an important table an hour ago. How do you restore them?

What they check: calm, careful recovery without making it worse.

Short answer: "First I stop the damage and find the exact time of the delete from the logs or the application. Then I restore the latest backup to a separate server and roll the transaction logs forward to just before that moment, a point-in-time recovery. I copy the missing rows back into production after checking them with the data owner."

The full answer: Restoring the whole production database to an earlier point would also undo an hour of good work, so a side restore is usually right. Some products offer quicker ways for recent mistakes, such as flashback features or a delayed replica kept an hour behind. Afterwards, ask why the delete was possible: should that account have had the right, should the change have gone through review?

A weak answer: "I restore last night's backup over production." Follow-up: "The table is two terabytes." Discuss restoring only that table or schema where the product allows it, and the time it takes.

How does replication work, and how do you plan high availability and failover?

What they check: that you know what each design loses and how long it takes to switch.

Short answer: "Replication sends the primary's changes to one or more replicas. Synchronous replication waits for a replica to confirm each change, so nothing committed is lost on failover, at the cost of speed; asynchronous replication is faster but may lose the last changes. High availability adds automatic failover to a replica, with a way to stop two servers both acting as primary."

The full answer: Each product has its own names: Always On availability groups in SQL Server, Data Guard in Oracle Database, streaming replication with a failover manager in PostgreSQL, group replication in MySQL, replica sets in MongoDB. The questions are the same: how much data can be lost, how fast the switch is, how applications find the new primary, and how the old primary rejoins. Replication is not a backup, because a delete is copied to every replica at once. Test failover regularly, in working hours, before you need it at night.

A weak answer: "We have a replica, so we do not need backups." Follow-up: "The network between two sites splits." Explain how a quorum or witness prevents both sides from accepting writes.

Performance, indexes and locks

A query that took a second now takes a minute. How do you find out why it is slow?

What they check: a method that starts with evidence, not with adding an index at random.

Short answer: "I find the query in the monitoring or the slow query log, then read its execution plan, the steps the database chose to answer it. I compare it with the old plan if I have it: a changed plan often means stale statistics, grown data or a different parameter. Then I check waits on the server: locks, storage, memory or CPU."

The full answer: An execution plan shows how tables are read, joined and sorted, and how many rows the database expected against how many it found; a large gap points to statistics. Every product has tools for this, such as the query store in SQL Server, the performance reports of Oracle Database or the statistics views of PostgreSQL. Fix the cause: refresh statistics, add or change an index, rewrite the query with the developers, or pin a known good plan for a while. Measure after the change, with real data volumes.

A weak answer: "I restart the database." Follow-up: "The query is fast in test and slow in production." Compare data volumes, statistics, parameters and the plans in both.

How do you decide which indexes to add, and which to remove?

What they check: that you know an index has a cost as well as a benefit.

Short answer: "I add an index for frequent, important queries that filter or join on columns with many different values, and check the plan uses it. Every index slows writes and takes space, so I look for indexes that are never used or duplicate others, and remove them after watching them for a full business cycle, month end included."

The full answer: The order of columns in a combined index matters: it helps queries that use its first columns. Covering indexes can answer a query without reading the table. Too many indexes on a busy table can make inserts and updates the bottleneck. Keep a record of why each index exists, and agree new ones with the developers who own the queries.

A weak answer: "I index every column to be safe." Follow-up: "Inserts have become slow." Check how many indexes the table carries and which are used.

Users say the application hangs, and you see blocking locks and deadlocks. What do you do?

What they check: that you understand transactions, locks and isolation, and fix causes with the developers.

Short answer: "I find the session at the head of the blocking chain and what it is doing, often a long transaction that was never committed. Deadlocks the database resolves by itself, cancelling one transaction, so I read the deadlock report to see which objects and statements were involved, and work with the developers to change the order of access or shorten the transactions."

The full answer: A transaction holds its locks until it ends, so long transactions, user input inside a transaction or missing indexes that make updates scan whole tables all spread blocking. The isolation level decides what readers and writers wait for; some products offer row versions so readers do not block writers. Killing a session ends the symptom; the fix is in the application or the indexes.

A weak answer: "I kill the blocking session and move on." Follow-up: "It happens every day at nine." Look at the batch jobs and reports that run then, and their transaction size.

Upgrades, security and monitoring

How do you plan a major version upgrade and monthly patches with little downtime?

What they check: careful change management and a way back.

Short answer: "I read the release notes for changes that affect us, test the upgrade on a copy of production with the application's tests and a performance check, and write a step-by-step plan with a rollback point. Patches go first to test, then to replicas, then to the primary after a failover, so downtime stays short."

The full answer: Each product has an end-of-support date, and running past it means no security fixes. Options for the upgrade itself include upgrading in place, building a new server and copying data across with replication, then switching over. Plan the change through the change process with the application owners, a time window and a named go or no-go point. Check query plans after the upgrade, because a new optimiser can change them.

A weak answer: "We stay on the old version; it works." Follow-up: "The version leaves support in three months." Show the plan, the test effort and the risk of waiting.

How do you secure a database: accounts, encryption and audit?

What they check: least privilege and evidence an auditor can read.

Short answer: "Every person and application gets its own account with only the rights it needs, least privilege, through roles; no shared admin passwords. Data is encrypted on disk and on the network, backups too. Logins and changes to sensitive data and rights are written to an audit trail that DBAs cannot quietly edit, and access is reviewed regularly."

The full answer: Keep admin access personal, logged and, where possible, granted only for the time it is needed. Mask or remove personal data in test copies. Patch quickly when a security flaw is published. In the EU, the GDPR, the EU data protection law, sets rules for personal data, and many companies are audited against ISO 27001Security controls auditors check: access reviews, encryption, backups, logging and documented procedures.; other countries have their own rules, so ask what applies where the data is kept.

A weak answer: "The application account has full rights, it is simpler." Follow-up: "An auditor asks who changed a salary table last month." Show where the audit trail answers it.

What do you monitor on a database, and what should wake you at night?

What they check: alerts that point to real risk, not noise.

Short answer: "I watch availability, replication lag, failed backups, free storage and its growth, connections, long transactions and locks, and the response time of key queries. Only what threatens data or service wakes someone: a primary down, replication broken, storage about to fill, backups failing. The rest goes to a daily review."

The full answer: Collect metrics into a central system, such as Prometheus with GrafanaCollect database metrics and alert on what matters: replication lag, connections, storage, slow queries. or the product's own tools, and keep history so trends show before limits are hit. Each alert needs a runbook: what it means and the first steps. Review alerts after each incident and remove the ones nobody acted on.

A weak answer: "We alert on everything above eighty percent." Follow-up: "The on-call DBA gets thirty alerts a night." Sort them by action needed and tune or remove the rest.

Capacity, cloud, automation and incidents

How do you plan capacity for the next year?

What they check: that you forecast from data and talk to the business.

Short answer: "I take the growth of data, transactions and peak load over the past year from monitoring, add what the business plans, such as a new country or product, and project storage, memory and CPU with a margin. I review it each quarter, because plans change."

The full answer: Storage is the usual first limit, then memory for the working set, then CPU at peaks such as month end. Archiving old data and removing unused indexes free room. In the cloud, capacity is bought by the hour, so the plan becomes a cost plan too: right-size instances and storage, and reserve capacity for steady load.

A weak answer: "We buy more disks when it is full." Follow-up: "Storage grew twice as fast as planned." Find which tables grew and why, and agree retention with the data owners.

How would you migrate a database to the cloud or to another product?

What they check: a migration plan with low risk and a rollback.

Short answer: "I list what the database uses that the target may not have, test the conversion and the application on a copy, and move the data with continuous replication so the final switch takes minutes. Before the switch I compare row counts and checksums, and I keep the old system ready to take over for an agreed time."

The full answer: A move to managed services, such as Azure SQL Database, AWS RDS or Cloud SQL, removes patching and some backups from the DBA's list but brings limits on settings and access; read them first. A change of product, for example from Oracle Database to PostgreSQL, also changes stored procedures, data types and tools, and needs the developers. Plan performance tests, because the cloud's storage behaves differently.

A weak answer: "We export everything on Friday and import it on Saturday." Follow-up: "The export takes thirty hours." Explain continuous replication and a short cut-over.

What do you automate, and how?

What they check: that you treat repeated work as code, reviewed and tested.

Short answer: "Anything done more than twice: building servers, settings, backups and their checks, patching, user rights, restore tests and reports. I write it as scripts or configuration in Git, with AnsibleConfigure database servers the same way every time, from one reviewed playbook. or TerraformCreate database servers and managed instances as code, so environments match and changes are reviewed., run it through review, and test it on non-production first."

The full answer: Automation makes many databases the same, which makes them easier to run and audit. Database changes can travel with the application through migration tools in the pipeline, so schema changes are reviewed like code. Scripts in PythonAutomation and tooling: migrations, reports, checks against many servers, glue for the pipeline., BashScripts for backups, checks and routine jobs, written so another DBA can read and rerun them. or PowerShellAutomate SQL Server and Windows tasks: backups, permissions, checks across many instances. fill the gaps. Keep a person in the loop for risky steps, such as dropping data.

A weak answer: "I know the steps by heart." Follow-up: "You manage a hundred instances." Show how settings and patches stay the same across all of them.

The main production database is down. Walk us through the incident.

What they check: calm order: restore service, communicate, then find the cause.

Short answer: "I confirm the impact, open an incident and tell the incident manager what I know. I restore service first, by failover to a replica or a restart if it is safe, while keeping logs for later. I give updates at fixed times. After service is back, I find the root cause without blame and write down the changes that prevent it next time."

The full answer: Check before acting: is it the database, the server, storage, the network or the application? A failover that loses data may be worse than a few more minutes of downtime, so know the recovery objectives and who decides. Keep a timeline as you go. Afterwards, test the fix, update the runbook, and add the missing alert.

A weak answer: "I try things until it comes back." Follow-up: "The failover worked but the application still cannot connect." Check how the application finds the primary: names, connection strings, caches.

Lead database administrator, data architect and head of database engineering

These questions come up for senior database administrator, lead database administrator, data architect and head of database engineering roles, where the work spans many databases, teams and products.

As lead DBA or head of database engineering, how do you set standards across many teams and products?

What they check: that you can make many databases safe and alike without slowing the teams.

Short answer: "I agree a short list of supported products and versions, standard builds with backups, monitoring and security switched on from the first day, and service levels by importance. Teams get databases through automation, not tickets. I measure restore tests passed, patch level and incidents, and review them with engineering leadership."

The full answer: Fewer products mean deeper skills and less risk, but each team has reasons, so decide with them and write the reasons down. A platform team that offers databases as a service inside the company scales better than a DBA for each team. A data architect designs how data is modelled and flows between systems; a head of database engineering also hires, plans on-call, and holds the budget for licences and cloud costs.

A weak answer: "Every team can choose any database." Follow-up: "Licence costs rose by half in a year." Show how you would find the cause and what a product standard could save.

Your CV for these questions

Each database on your CV invites the question: how big, how critical, what went wrong, what did you change. Write lines that open those stories, each under the job where it happened:

Score your CV against a real DBA job before the interview; the gaps it shows are the stories to prepare first.