Integrated Backup and Recovery Capstone Lab: Full Scenario Test

Integrated Backup and Recovery Capstone Lab: Full Scenario Test

What This Lab Is About

This capstone lab brings together every backup and recovery skill you have built during this course. You will receive a scenario packet describing a specific database environment, a simulated failure, and time-bound targets. Your job is to plan, execute, document, and explain a complete backup-and-recovery cycle—just as you would in a real production incident. This is the closest a classroom can get to an actual on-call event.

Understanding Your Targets Before You Touch the Keyboard

Two terms govern every recovery decision you will make.

Recovery Time Objective (RTO) is the maximum acceptable time from the moment a failure is detected to the moment the database is back in service. If your scenario packet states a two-hour RTO, a restore that takes two hours and five minutes is a failure regardless of how clean the data looks.

Recovery Point Objective (RPO) is the maximum acceptable data loss, expressed as a point in time. An RPO of fifteen minutes means no more than fifteen minutes of committed transactions may be lost. If you restore from a backup that is three hours old and you have no log backups to roll forward, you have violated the RPO even if the database opens cleanly.

Read the scenario packet completely before writing a single command. Identify the platform mix (PostgreSQL plus Oracle XE or SQL Server), the failure type, and both targets. Then write your backup and recovery plan in the incident log template before you proceed.

Planning Your Backup Strategy

A solid plan answers four questions:

1. What backup type is appropriate—full, differential, incremental, or a combination?

2. What is the backup location and how will you verify it is readable before introducing the failure?

3. Which platform-specific tools will you use (for example, `pg_dump` or `pg_basebackup` on PostgreSQL; RMAN on Oracle XE; SQL Server Management Studio or T-SQL `BACKUP DATABASE` on SQL Server)?

4. What is the exact sequence of restore steps, and which step brings you back within RPO?

Document all four answers in the planning section of your incident log before you execute anything. This mirrors industry practice: change management and incident response both require a written plan with a back-out procedure.

Executing the Backup and Introducing the Failure

Once your plan is approved or timed-in, execute your backups and verify them. Verification is not optional—take a checksum or test-restore a small object before you proceed. Only then should you introduce the assigned failure (dropped table, corrupted datafile, or deleted transaction logs).

Record every command you run, the exact timestamp, and the output or error message in the structured incident log. If a command fails, record the error and what you did next. Incident logs that skip failed attempts or show only successful commands are incomplete and will not meet the documentation rubric.

After introducing the failure, work through your recovery sequence. Keep one eye on the clock relative to your RTO. If you realize mid-recovery that your original approach will exceed the RTO, note the decision point and explain what you changed—that kind of documented real-time reasoning is exactly what the debrief questions will probe.

Verifying the Restore

A recovery is not complete until you verify it. Verification steps depend on the failure type:

• Dropped table: Query the table; confirm row count matches a pre-failure baseline you recorded.
• Corrupted datafile: Run the platform's consistency check (`PRAGMA integrity_check` equivalent, RMAN `VALIDATE`, or `DBCC CHECKDB`) and confirm no errors are reported.
• Deleted transaction logs: Confirm the database is online, writable, and that data up to the RPO boundary is present.

Document all verification queries and their results in the incident log.

Writing the Restore Runbook

After recovery, you will formalize your steps into a restore runbook. A runbook is a reusable, step-by-step procedure written so that a qualified colleague who was not present could repeat the recovery reliably. It includes prerequisites (tool versions, permissions required), numbered steps with exact commands or UI paths, expected output for each step, and a verification checklist. Leave nothing to memory.

Preparing for the Debrief

You will present your incident log and runbook in a ten-minute debrief and answer three instructor questions. Expect questions about why you chose a specific backup type over another, how you tracked progress against the RTO, and what you would do differently if the RTO were halved. Prepare honest, specific answers grounded in your actual incident log—not generic theory.

Safety and Professional Conduct

Even in a lab environment, treat the workstation and shared network resources with care. Do not introduce failures on any database outside your assigned instance; shared lab servers may affect other students. Shut down services cleanly before restoring system-level files to avoid file-lock errors or data corruption that could require a full lab reset. Follow your institution's acceptable-use policy for lab systems. These habits translate directly to the professional obligation to never perform untested recovery procedures on production systems without a verified backup in hand.

Credentials This Lab Supports

The skills practiced here align directly with the PostgreSQL CE (EDB) hands-on backup objectives, Oracle DBA CA recovery scenarios, Microsoft DP-300 managed-instance backup and restore tasks, and the DAMA CDMP Associate data protection and continuity domains. Mastering documented, verifiable recovery is a requirement of all four credentials.