Database Administrator interview questions and answers

A full backup copies the whole database. A differential copies only what has changed since the last full backup, so each differential grows during the week until the next full. A transaction log backup copies the log records since the previous log backup, which allows point-in-time recovery; in SQL Server it needs the FULL or BULK_LOGGED recovery model.

A typical plan: a weekly full on Sunday night, a daily differential, and log backups every 15 minutes. To restore, you need the last full, the latest differential and every log backup after it, in sequence. Oracle RMAN works the same way with level 0 and level 1 incremental backups plus archived redo logs. Base the frequency on the RPO the business agrees.

RPO, the recovery point objective, is how much data the business can afford to lose, measured in time: an RPO of 15 minutes means you must be able to recover to within 15 minutes of a failure. RTO, the recovery time objective, is how long the system may be down before it's working again.

RPO drives backup frequency: a 15-minute RPO needs log backups at least every 15 minutes, or replication. RTO drives how fast you must restore: a multi-terabyte database can take hours to restore from backup, so a 30-minute RTO needs a standby you can fail over to. Agree both with the business for each system, because a payments database and an internal reporting database need very different levels.

First, don't overwrite anything, and find the exact time of the drop. In SQL Server with the FULL recovery model, restore the last full backup as a new database WITH NORECOVERY, then the latest differential, then the log backups, using STOPAT just before 2:15 pm. Copy the table back into production, check row counts and constraints, and let the application team verify. Restoring as a separate copy means production doesn't lose the work done since 2:15.

In Oracle, if the table is still in the recycle bin, FLASHBACK TABLE orders TO BEFORE DROP is almost instant; otherwise RMAN can recover a single table to a point in time (12c and later). Afterwards, ask why a developer had DROP rights in production.

A successful job only proves a file was written. Test it: restore backups regularly to a separate server, at least monthly for critical databases, then run DBCC CHECKDB on the restored copy in SQL Server, plus a few application queries. In Oracle, RMAN's RESTORE DATABASE VALIDATE reads the backup pieces and checks them without restoring, but a real test restore is still needed. Time each restore and compare it with the RTO.

Also take backups WITH CHECKSUM, alert on failed or missed jobs, make sure the log backup chain isn't broken, and keep copies off the server, in another location or in object storage, so a disk failure or ransomware doesn't take the backups too. Keep a written restore runbook someone else can follow at 3 am.

First find why the log can't be reused: SELECT name, log_reuse_wait_desc FROM sys.databases. The most common answer is LOG_BACKUP: the database is in the FULL recovery model but nobody is taking log backups, so the log grows forever. Other causes are ACTIVE_TRANSACTION, a long-open transaction (find it with DBCC OPENTRAN), REPLICATION, or an availability group replica that has fallen behind (AVAILABILITY_REPLICA).

Fix the cause: schedule log backups, or commit or kill the stuck transaction after checking with its owner. Only then shrink the file, once, to a sensible size. Switching to SIMPLE recovery frees the log but breaks the backup chain and point-in-time recovery, so don't do it on production without agreement.

In SQL Server a login is at the server (instance) level and lets someone connect: a Windows login or a SQL Server login with a password. A user is at the database level and is mapped to a login; permissions inside the database are granted to the user, usually through roles such as db_datareader or your own custom roles. A login with no user in a database can connect to the server but can't use that database.

In Oracle, a user is created in the database and is also a schema: the objects that user creates belong to it. Privileges are granted directly or through roles. An orphaned user, a database user whose login is missing after a restore to a new server, is a common follow-up question.

Least privilege means each account gets only the permissions it needs for its job and nothing more. For a web application, create a dedicated account, not a shared admin login, and never give it sysadmin, db_owner or DBA. Grant only what the app does: EXECUTE on the stored procedures it calls, or SELECT, INSERT and UPDATE on specific tables, without DROP or ALTER. Use a separate account for deployments that change the schema.

Give developers read-only access in production, if any, through a role, and grant to roles rather than individuals so access is easy to review and remove. Keep the password in a secrets vault, not in code, and review access regularly, especially when people leave.

If nothing was set up beforehand, you may not be able to answer fully: the transaction log and backups show what changed but not reliably who did it. So set up auditing now. In SQL Server, use SQL Server Audit with a database audit specification for UPDATE and DELETE on the salary table; temporal tables or change data capture can also keep the old values. In Oracle, create a unified auditing policy on that table.

Record who, when, from which machine and what statement. Send the audit records somewhere the DBAs themselves can't alter, because auditors will ask how you know the log wasn't edited. Audit only what matters: auditing every SELECT on a busy system costs performance and storage.

TDE encrypts the database's data and log files on disk, and its backups, and decrypts pages automatically as they're read into memory, so applications need no change. SQL Server has it, and Oracle offers it through Advanced Security. It protects data at rest: a stolen disk, data file or backup is unreadable without the key.

It doesn't protect against anyone who can log in and query, including an attacker using a compromised application account, because they see decrypted data. For that you need access control, column encryption or Always Encrypted for fields like Aadhaar or card numbers, and TLS for data in transit. The critical step: back up the TDE certificate and key separately, or those backups can never be restored on another server.

An execution plan is the set of steps the optimiser chose to run a query: which indexes to use, the join methods (nested loops, hash or merge) and the order of the tables. You see it with EXPLAIN PLAN and DBMS_XPLAN in Oracle, EXPLAIN in MySQL and PostgreSQL, or the estimated and actual plans in SQL Server.

Look first at the most expensive steps, then at full scans of large tables where you expected an index seek, lookups repeated thousands of times, and sorts or hash joins spilling to disk. The most useful check is estimated rows against actual rows: if the optimiser expected 10 rows and got 10 lakh, its statistics or the query's conditions are misleading it.

Statistics describe the data in a table or index: the number of rows, the number of distinct values, and a histogram of how the values are spread. The optimiser uses them to estimate how many rows each step returns, and chooses the plan from those estimates.

If statistics are stale, for example after a large overnight data load, the optimiser may think a table has 1,000 rows when it has 50 lakh, and choose a nested-loop join or an index lookup that's far too slow. Fix it by updating statistics: UPDATE STATISTICS or sp_updatestats in SQL Server, DBMS_STATS.GATHER_TABLE_STATS in Oracle, ANALYZE in PostgreSQL. Schedule it after big loads rather than relying only on automatic updates.

Normal CPU with everything stuck usually means sessions are waiting, often on locks. In SQL Server, query sys.dm_exec_requests for blocking_session_id and wait_type, follow the chain to the head blocker, and see its SQL and how long its transaction has been open. In Oracle, check v$session for blocking_session and the event column, and use ASH or AWR reports for the top waits.

Typically the head blocker is a long transaction, an uncommitted update left in someone's open session, or a batch job running in business hours. Contact its owner before killing it, because rolling back a big transaction can itself take time. Afterwards, fix the cause: shorter transactions, the right indexes, or rescheduling the batch job.

Most likely the execution plan changed. Common reasons: statistics were updated or went stale; the data grew past a point where a different plan was chosen; or, in SQL Server, parameter sniffing: the plan was compiled for an unusual parameter value, such as a customer with very few orders, and is now reused for a customer with lakhs of orders. Oracle has the same effect with bind variable peeking.

Compare today's plan with the old one. Query Store in SQL Server keeps plan history and lets you force the good plan; in Oracle, use AWR history and SQL plan baselines. Short-term fixes include updating statistics or recompiling; longer term, fix the query or index so any reasonable plan is fast.

Turn on query logging with a threshold. In MySQL, enable slow_query_log and set long_query_time, say to 1 second, then summarise the log with mysqldumpslow or pt-query-digest, which groups similar queries together. In PostgreSQL, set log_min_duration_statement, and enable the pg_stat_statements extension, which keeps the number of calls, total time and rows for each normalised query.

Sort by total time, not only by the slowest single run: a 50 ms query called a million times a day costs far more than a 30-second monthly report. Then take the top few, read their plans and fix them one at a time. SQL Server's equivalents are Query Store and sys.dm_exec_query_stats.

High availability keeps the database running through local failures, such as a server, disk or software crash, usually by failing over automatically to a standby in the same data centre or region within seconds or minutes. Disaster recovery is about surviving the loss of a whole site, such as a fire, flood or regional outage, by recovering in another location, often with a manual decision and some data loss.

They use different tools: HA uses clustering or synchronous replicas close together; DR uses asynchronous replicas or backups copied far away. A common setup has both: a synchronous standby nearby and an asynchronous one in another city. Each has its own RPO and RTO, agreed with the business.

In synchronous replication, the primary waits until the replica confirms it has received the change before telling the application the commit succeeded. If the primary fails, nothing committed is lost, but every commit is slower by the network round trip, so the replica needs to be close by. In asynchronous replication, the primary commits immediately and sends the changes afterwards. Commits are fast and the replica can be far away, but if the primary fails, the last few seconds of changes may never reach it.

SQL Server availability groups have synchronous-commit and asynchronous-commit modes; Oracle Data Guard has Maximum Availability (synchronous) and Maximum Performance (asynchronous) modes. Choose based on the RPO and the distance.

With automatic failover, a cluster manager or observer detects that the primary is down and promotes a synchronous standby: Windows Server Failover Clustering for SQL Server availability groups, the fast-start failover observer for Oracle Data Guard, or a tool like Patroni for PostgreSQL. Applications connect through a listener or virtual name, so they reach the new primary without configuration changes. A quorum or witness prevents split brain, where two servers both act as the primary.

Afterwards, confirm the applications are connecting and writing, check whether any data was lost if the standby was asynchronous, make sure backups and jobs now run on the new primary, and rebuild the old server as a standby before failing back in a planned window.

First measure the lag: SHOW REPLICA STATUS in MySQL (Seconds_Behind_Source), the replay_lag column of pg_stat_replication on a PostgreSQL primary, or the availability group dashboard in SQL Server. Then work out whether changes aren't arriving at all (a network or disk problem, or replication stopped on an error) or are arriving but applied too slowly.

Common causes: one huge transaction, such as a bulk delete, that must replay as a whole; a replica smaller than the primary; long reports on the replica holding up the apply; or single-threaded apply in older MySQL set-ups. Fix the cause, break big batch jobs into chunks, and add an alert on lag, so the reporting team hears about it from you, not from wrong numbers.

In object storage, such as Amazon S3, Azure Blob Storage or Google Cloud Storage. It's cheap, very durable because the provider keeps several copies, and separate from the database server, so losing the server doesn't lose the backups. SQL Server can back up straight to a URL in Azure Blob Storage, and Oracle RMAN can write to object storage through Oracle's cloud backup module.

Set lifecycle rules to move older backups to cheaper archive tiers and delete them after the retention period. Encrypt them, keep a copy in another region for disaster recovery, and use versioning or object lock so ransomware or a mistaken delete can't remove them. Managed services keep their own automated backups, but many teams also keep an export for long-term retention.

The provider takes over the infrastructure work: the hardware and operating system, installing the database software, minor version patching in a maintenance window you choose, automated backups with point-in-time restore, and failover to a standby if you enable Multi-AZ.

Still yours: schema design, indexes and query tuning; users, roles and permissions; choosing the instance size and storage type; database settings through parameter groups; monitoring and alerts; planning major version upgrades and testing the application on them; and restore tests. You also can't log in to the operating system or get full sysadmin or SYSDBA rights, so some tools and features aren't available, which matters when migrating an older application.

SQL Server on an Azure VM is closest to what you have: full control of the instance and operating system and every feature, but you still patch, back up and run high availability yourself. Azure SQL Managed Instance is a managed service with near-complete compatibility with on-premises SQL Server, including SQL Agent jobs, cross-database queries, linked servers and Database Mail, so it suits moving an application that uses instance-level features. Azure SQL Database is a managed single database with the least administration, but no SQL Agent and no ordinary cross-database queries, so the application may need changes.

Run an assessment with Azure's migration tools first to list blockers, then compare monthly costs, including reserved capacity and reusing existing licences through Azure Hybrid Benefit.

For development and testing, yes: a container gives every developer the same database version in seconds. For production it can work, but the data must live on a persistent volume, not in the container's own file system, or it disappears when the container is replaced. On Kubernetes you'd use a StatefulSet with persistent volumes, usually managed by an operator that handles replication, failover and backups.

The hard parts are storage performance, backups, upgrades and troubleshooting, which need real experience to run safely. Many teams therefore keep production databases on a managed service or dedicated servers and use containers elsewhere. A good answer gives the trade-off and what you'd need to run it safely, rather than a flat yes or no.

Show that you understand the job and like its responsibility: "I enjoy keeping systems running and solving problems under the surface. During my [course or internship], I set up [SQL Server, Oracle or PostgreSQL] on a lab server, scheduled backups and restored them, set up a replica and tuned a few slow queries. What attracts me is that every application depends on the database, so the work directly affects the business. I'd like to grow from monitoring and backups into performance tuning and high availability."

Mention something specific you've practised; "I like databases" alone isn't convincing.

Answer honestly and show you understand why it's needed: "Yes. Patching, upgrades and failover tests have to happen when the business is quiet, and when a production database has a problem, someone has to respond quickly. I'm comfortable with that, and I'd like to understand how the rotation works here so I can plan around it."

If you have a real constraint, such as a fixed commitment on certain days, mention it politely now. Agreeing in the interview and refusing shifts after joining does far more damage than a clear answer today.

Give concrete habits, not just "I read online": "I keep a small lab with [SQL Server Developer Edition, Oracle Database Free or PostgreSQL] in virtual machines or containers, where I practise backups, restores and failovers. I follow the official documentation and release notes for the databases I use, and I'm preparing for [a certification, such as Microsoft's Azure Database Administrator Associate or Oracle's OCP]. When I solve something new, I write a short note so the team can use it too."

Only name a certification you're really working towards; interviewers may ask how far you've got.

Use STAR, and focus on calm, ordered actions and the follow-up. For example: "One night the main order database stopped accepting writes because its log disk was full (situation). I was on call (task). I informed the incident channel, found that log backups had been failing for two days, took a log backup to another drive to free space, and had the service back in about [time] (action). Afterwards I fixed the backup job, added alerts for disk space and failed backups, and wrote a short incident report (result)."

Interviewers listen for how you communicated during the outage and what you changed so it wouldn't happen again.

Show that you help the person and protect the system; a plain no isn't enough. "I'd first understand what they need to do. Usually it's to look at data or run a fix script, which doesn't need sysadmin. I'd give temporary, limited access, such as read access to the tables involved, or run the reviewed script myself under our change process. If elevated access is really needed, I'd follow the emergency-access procedure: approval from the right managers, time-limited and logged, and removed afterwards."

Interviewers want to see that you stay polite under pressure from a senior person and still follow the controls.