shell-leaderboard/docs/backup.md

# Database Backup & Restore

Scripts for backing up and restoring your database.

## Quick Start

```bash
# Create backup
make backup

# List backups
ls -la backups/

# Restore from backup
make restore file=backups/laravel_20240306_120000.sql.gz
```

## Backup

The backup script automatically:
- Detects database type (MySQL, PostgreSQL, SQLite)
- Creates timestamped backup
- Compresses with gzip
- Keeps only last 10 backups

### Manual Backup

```bash
./scripts/backup.sh
```

Output:
```
==========================================
Database Backup
==========================================
Connection: mysql
Database: laravel

Creating MySQL backup...

✓ Backup created successfully!
  File: backups/laravel_20240306_120000.sql.gz
  Size: 2.5M

Recent backups:
-rw-r--r-- 1 user user 2.5M Mar 6 12:00 laravel_20240306_120000.sql.gz
-rw-r--r-- 1 user user 2.4M Mar 5 12:00 laravel_20240305_120000.sql.gz
```

### Backup Location

```
backups/
├── laravel_20240306_120000.sql.gz
├── laravel_20240305_120000.sql.gz
└── laravel_20240304_120000.sql.gz
```

## Restore

```bash
# With make
make restore file=backups/laravel_20240306_120000.sql.gz

# Or directly
./scripts/restore.sh backups/laravel_20240306_120000.sql.gz
```

**Warning:** Restore will overwrite the current database!

## Automated Backups

### Using Scheduler

Add to your Laravel scheduler (`routes/console.php`):

```php
use Illuminate\Support\Facades\Schedule;

// Daily backup at 2 AM
Schedule::exec('bash scripts/backup.sh')
    ->dailyAt('02:00')
    ->sendOutputTo(storage_path('logs/backup.log'));
```

### Using Cron (Production)

```bash
# Edit crontab
crontab -e

# Add daily backup at 2 AM
0 2 * * * cd /var/www/html && bash scripts/backup.sh >> /var/log/laravel-backup.log 2>&1
```

## Remote Backup Storage

### Copy to S3

```bash
# Install AWS CLI
pip install awscli

# Configure
aws configure

# Upload backup
LATEST=$(ls -t backups/*.gz | head -1)
aws s3 cp "$LATEST" s3://your-bucket/backups/
```

### Automate S3 Upload

Add to `scripts/backup.sh`:

```bash
# After backup creation
if command -v aws &> /dev/null; then
    echo "Uploading to S3..."
    aws s3 cp "$BACKUP_FILE" "s3://${S3_BUCKET}/backups/"
fi
```

### Using Laravel Backup Package

For more features, use [spatie/laravel-backup](https://github.com/spatie/laravel-backup):

```bash
composer require spatie/laravel-backup
php artisan vendor:publish --provider="Spatie\Backup\BackupServiceProvider"
```

```php
// config/backup.php
'destination' => [
    'disks' => ['local', 's3'],
],

// Schedule
Schedule::command('backup:run')->daily();
Schedule::command('backup:clean')->daily();
```

## Database-Specific Notes

### MySQL

```bash
# Manual backup
docker-compose exec mysql mysqldump -u laravel -p laravel > backup.sql

# Manual restore
docker-compose exec -T mysql mysql -u laravel -p laravel < backup.sql
```

### PostgreSQL

```bash
# Manual backup
docker-compose exec pgsql pg_dump -U laravel laravel > backup.sql

# Manual restore
docker-compose exec -T pgsql psql -U laravel laravel < backup.sql
```

### SQLite

```bash
# Manual backup (just copy the file)
cp src/database/database.sqlite backups/database_backup.sqlite

# Manual restore
cp backups/database_backup.sqlite src/database/database.sqlite
```

## Backup Strategy

### Development
- Manual backups before major changes
- Keep last 5 backups

### Staging
- Daily automated backups
- Keep last 7 days

### Production
- Hourly incremental (if supported)
- Daily full backup
- Weekly backup to offsite storage
- Keep 30 days of backups
- Test restores monthly

## Testing Restores

**Important:** Regularly test your backups!

```bash
# Create test database
docker-compose exec mysql mysql -u root -p -e "CREATE DATABASE restore_test;"

# Restore to test database
gunzip -c backups/latest.sql.gz | docker-compose exec -T mysql mysql -u root -p restore_test

# Verify data
docker-compose exec mysql mysql -u root -p restore_test -e "SELECT COUNT(*) FROM users;"

# Cleanup
docker-compose exec mysql mysql -u root -p -e "DROP DATABASE restore_test;"
```

## Troubleshooting

### Backup fails with permission error
```bash
chmod +x scripts/backup.sh scripts/restore.sh
mkdir -p backups
chmod 755 backups
```

### Restore fails - database locked
```bash
# Stop queue workers
make queue-stop

# Run restore
make restore file=backups/backup.sql.gz

# Restart queue workers
make queue-start
```

### Large database backup timeout
```bash
# Increase timeout in docker-compose.yml
environment:
  MYSQL_CONNECT_TIMEOUT: 600
```