How to restore a Supabase project from a SQL backup

When You Need This Guide

Your production database is gone. Maybe a migration went wrong. Maybe someone ran DROP TABLE. Maybe your Supabase project was deleted. You have a .sql.gz backup in your Google Drive from Supakeep. Now what?

This guide walks through the full restore process — from downloading the backup to verifying the restored data to documenting the test for compliance.

Source: [Supabase Docs — Backup and Restore using the CLI](https://supabase.com/docs/guides/platform/migrating-within-supabase/backup-restore)

Prerequisites

Before you start, you need:

1. 1A .sql.gz backup file (from Supakeep's Google Drive folder)
2. 2A fresh Supabase project (create one in the dashboard — never restore to production)
3. 3The psql command-line tool installed locally
4. 4Your new project's connection string (found in Dashboard > Settings > Database)

Step 1: Download the Backup

Backup folder structure (Google Drive):

  supabackups/
    |__ db_2026-07-30.sql.gz      <- Database dump
    |__ storage_2026-07-30.tar     <- Storage objects
    |__ auth_2026-w30.json.gz      <- Auth data
    |__ edge_2026-w30.zip          <- Edge functions

Download the db_*.sql.gz file for the date you want to restore to. If you also have storage and auth backups, download those too — you'll need them for a complete restore.

Step 2: Create a Fresh Supabase Project

Never restore to your production database. Always create a new project for testing.

1. 1Go to https://supabase.com/dashboard
2. 2Click "New Project"
3. 3Choose a name like restore-test-2026-07
4. 4Select the same region as your original project
5. 5Set a strong database password
6. 6Wait for the project to provision (2-3 minutes)

Step 3: Decompress and Restore

# Decompress the backup
gunzip db_2026-07-30.sql.gz

# Restore to the new project
psql "postgresql://postgres:YOUR_PASSWORD@db.YOUR_REF.supabase.co:5432/postgres" \
  -f db_2026-07-30.sql

Restore output:

  SET
  CREATE TABLE
  CREATE TABLE
  ALTER TABLE
  COPY 10000
  COPY 5000
  CREATE INDEX
  ...
  (no errors = success)

If you see COPY X lines with numbers, data is being inserted. If you see errors, check the troubleshooting section below.

Step 4: Restore Storage Objects (If Applicable)

If your backup includes a storage_*.tar file, you need to upload the files back to your new project's storage buckets:

# Extract storage archive
tar -xf storage_2026-07-30.tar

# Upload files via the Supabase Storage API
# (Use the Supabase CLI or SDK for this step)

This step is only needed if your app uses Supabase Storage for user uploads. Without this, storage URLs will return 404.

Step 5: Verify the Restore

-- Check table row counts
SELECT 'users' as table, count(*) FROM users
UNION ALL
SELECT 'posts', count(*) FROM posts
UNION ALL
SELECT 'orders', count(*) FROM orders;

-- Check RLS policies are intact
SELECT tablename, policyname, cmd 
FROM pg_policies 
WHERE schemaname = 'public';

-- Check roles exist
SELECT rolname FROM pg_roles 
WHERE rolname IN ('anon', 'authenticated', 'service_role');

If all row counts match, RLS policies are present, and roles exist — your restore is successful.

Step 6: Document for Compliance

If you're restoring as part of a compliance audit (SOC 2, ISO 27001), document the test:

Restore Test Record

  Date:           2026-07-30
  Backup file:    db_2026-07-30.sql.gz
  Target project: restore-test-2026-07
  Result:         SUCCESS
  Rows restored:  17,500 (users: 10k, posts: 5k, orders: 2.5k)
  RLS policies:   3 verified intact
  Roles:         anon, authenticated, service_role present
  Verified by:   [Your name]

Screenshot the verification queries and attach to your audit evidence file. This is the documentation auditors look for.

Source: [Konfirmity — SOC 2 Backup And Recovery](https://www.konfirmity.com/blog/soc-2-backup-and-recovery-for-soc-2)

Troubleshooting

Related reading: Supabase backup best practices for small teams and pg_dump + GitHub Actions: the free way, and the 5 things it misses.