Take a Snapshot Before Major Schema Changes

The Story: When a "Quick" Migration Went Wrong

Meet Alex, a backend developer working on a feature that required restructuring the user authentication system. The change involved:

Renaming the users table to accounts
Splitting the email field into primary_email and secondary_email
Adding new indexes and foreign key constraints
Migrating existing data to the new structure

⏰ Friday, 3:45 PM - The Migration Begins

Alex was confident. The migration script had been tested in development, reviewed by the team, and looked solid. "This should take 5 minutes," Alex thought, running the migration on the staging database.

But Alex didn't take a snapshot first.

⏰ Friday, 3:52 PM - The First Error

The migration script failed halfway through. A foreign key constraint couldn't be created because some orphaned records existed. The database was now in an inconsistent state:

The users table was partially renamed
Some columns were split, others weren't
Indexes were partially created
The application was completely broken

⏰ Friday, 4:15 PM - The Recovery Attempt

Alex tried to manually fix the database state. But without a clear picture of what the database looked like before, it was like trying to solve a puzzle with missing pieces. Every fix attempt seemed to break something else.

⏰ Friday, 6:30 PM - The Realization

After hours of debugging, Alex realized the only way forward was to restore from the last nightly backup. But that backup was from 18 hours ago, and staging had received important test data since then. Restoring would mean:

Losing a full day of test data
Re-running all the tests that had been done
Delaying the feature release
Explaining to the team why staging was down

The Lesson

Taking a snapshot before a major schema change takes 30 seconds. Not taking one can cost you hours, or even days, of recovery work. It's the difference between a quick rollback and a full-blown crisis.

How It Should Have Been Done

Here's what Alex should have done, and what you should do before any major schema change:

1

Take a Snapshot Before the Change

Before running any migration script, create a snapshot of your database. With Quemsi, this takes literally 30 seconds:

Open the Quemsi web UI
Navigate to your database
Click "Create Backup"
Tag it with something descriptive like pre-auth-migration or before-schema-change-v2
Wait for the backup to complete (usually 30-60 seconds)

# With Quemsi, you can also do this via API or CLI:
quemsi backup create \
  --database staging_db \
  --tag "pre-auth-migration-$(date +%Y%m%d)" \
  --description "Snapshot before user table restructuring"
        

2

Run Your Migration

Now you can run your migration script with confidence. If something goes wrong, you have a safety net.

3

If Something Goes Wrong, Restore Instantly

If the migration fails or produces unexpected results:

Open Quemsi web UI
Find your pre-migration snapshot in the timeline
Click "Restore"
Select the target database (or create a new one to test)
Confirm the restore

Your database is back to its pre-migration state in minutes, not hours.

✅ The Better Way

If Alex had taken a snapshot before the migration:

3:45 PM: Create snapshot (30 seconds)
3:46 PM: Run migration
3:52 PM: Migration fails
3:53 PM: Restore from snapshot (2 minutes)
3:55 PM: Database restored, ready to debug the migration script

Total downtime: 10 minutes instead of 3+ hours.

Best Practices for Schema Change Snapshots

📋 Use Descriptive Tags

Tag your snapshots with meaningful names that include:

The purpose: pre-migration, before-refactor
The feature or ticket number: pre-auth-v2-migration
The date: 2025-01-15-pre-schema-change

This makes it easy to find the right snapshot later.

🔄 Create Snapshots at Key Milestones

Don't just snapshot before the change—snapshot at key points:

Before the migration
After the migration (if successful)
Before data transformations
After each major step in a multi-step migration

🧪 Test Your Restore Process

Before you need it, test that you can restore:

Create a snapshot
Make a small test change
Restore from the snapshot
Verify the database is back to the original state

This ensures your restore process works when you really need it.

📝 Document in Commit Messages

When you create a snapshot, mention it in your commit message or PR description:

feat: restructure user authentication schema

- Rename users table to accounts
- Split email into primary_email and secondary_email
- Add new indexes and constraints

⚠️ Snapshot created: pre-auth-migration-20250115
   Restore point available in Quemsi if migration fails
        

Real-World Scenarios Where Snapshots Save Time

📸

Before Change

30 seconds to create snapshot

⚙️

During Migration

Migration runs

✅

After Success

Keep snapshot for rollback if issues appear later

Scenario 1: Migration Script Has a Bug

Your migration script works in development but fails in staging due to different data. With a snapshot, you restore in 2 minutes, fix the script, and try again.

Scenario 2: Performance Issues After Migration

The migration succeeds, but the new schema causes performance problems. You can restore to the previous state, optimize the migration, and try again.

Scenario 3: Data Corruption

During the migration, some data gets corrupted. Instead of trying to fix corrupted data, restore from the snapshot and fix the migration script.

Scenario 4: Need to Compare States

You want to compare query performance or data integrity between the old and new schema. Restore the snapshot to a different database and run comparisons.