Restore Database

Restores a MySQL database from a backup file. Imports database structure and data from a SQL dump file.

Common Properties

• Name - The custom name of the node.
• Color - The custom color of the node.
• Delay Before (sec) - Waits in seconds before executing the node.
• Delay After (sec) - Waits in seconds after executing node.
• Continue On Error - Automation will continue regardless of any error. The default value is false.

info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs

• Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
• Backup File Path - The full file path to the backup file to restore from. For example: C:\backups\mydb_2024-01-15.sql or /home/user/backups/mydb.sql

Options

• Credentials - MySQL database credentials (optional). Use this if you want to restore without a Connect node.
• Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output

• None - The database is restored from the backup file.

How It Works

The Restore Database node imports a database from a backup file. When executed, the node:

1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
2. Validates the backup file path is not empty
3. Verifies the backup file exists
4. Creates a MySqlBackup object
5. Imports the database from the specified file path
6. Executes all SQL statements in the backup file (CREATE TABLE, INSERT, etc.)
7. Restores all tables, data, and structure to the database

Requirements

• Either: A valid connection ID from Connect node OR valid database credentials
• Valid backup file path
• The backup file must exist and be readable
• Database user must have appropriate permissions (CREATE, INSERT, DROP, etc.)
• Connection to the target database
• Sufficient disk space in the database for restored data

Error Handling

The node will return specific errors in the following cases:

• ErrInvalidArg - Backup file path is empty
• ErrNotFound - Backup file not found at specified path or connection ID not found
• ErrRuntime - Restore operation failed (permissions, invalid backup file, SQL errors)

Backup File Format

The restore expects a standard MySQL dump file (.sql) containing:

• CREATE TABLE statements
• INSERT statements with data
• Database structure (indexes, constraints)
• SQL comments

This format is compatible with:

• Backups created by the Backup Database node
• MySQL mysqldump utility
• phpMyAdmin exports
• MySQL Workbench exports

Usage Examples

Basic Restore

Connect
  ↓
Restore Database
  - Backup File Path: C:\backups\mydb_2024-01-15.sql
  ↓
Disconnect

Restore with Verification

Connect
  ↓
Restore Database
  ↓
Query (verify data exists)
  - SQL: SELECT COUNT(*) FROM main_table
  ↓
JavaScript (check row count)
  ↓
Disconnect

Automated Recovery

Try
  ├─ Connect
  ├─ Restore Database (restore from backup)
  ├─ Query (verify critical tables exist)
  └─ Send Email (restore successful)
Catch
  └─ Send Alert (restore failed)
Finally
  └─ Disconnect

Common Use Cases

1. Disaster Recovery - Restore database after data loss or corruption
2. Environment Setup - Set up development/test databases from production backup
3. Data Migration - Move database from one server to another
4. Rollback Changes - Undo unwanted database changes by restoring previous backup
5. Clone Database - Create copy of database for testing
6. Periodic Refresh - Refresh test environment with production data
7. Testing Backups - Verify backups are restorable

Restore Strategies

Point-in-Time Restore

// Restore to specific point in time
const backupDate = '2024-01-15';
const backupPath = `C:\\backups\\mydb_${backupDate}.sql`;

Production to Development

Connect (to dev database)
  ↓
Restore Database
  - Backup File Path: C:\backups\production_latest.sql
  ↓
Non Query (anonymize sensitive data)
  - UPDATE users SET email = CONCAT('user', id, '@test.com')
  ↓
Disconnect

Selective Restore

For single table restore, modify backup file or use mysqldump with --tables option to create single-table backup first.

Restore from Remote Backup

FTP Download (download backup file)
  ↓
Connect
  ↓
Restore Database (local file)
  ↓
Disconnect
  ↓
Delete Local Backup File

Tips for Effective Use

• Verify Backup First - Ensure backup file exists and is valid before restoring
• Backup Before Restore - Create a backup before restoring to preserve current state
• Test in Dev First - Test restore in development environment before production
• Check Permissions - Ensure database user has CREATE, DROP, INSERT permissions
• Monitor Progress - For large restores, monitor database connection and disk space
• Decompress First - If backup is compressed (.gz, .zip), decompress before restoring
• Database Selection - Ensure you're connected to the correct target database
• Warning: Destructive - Restore may drop and recreate tables, losing existing data

Best Practices

Pre-Restore Checklist

1. Verify backup file exists and is valid
  ↓
2. Create backup of current database state
  ↓
3. Connect to target database
  ↓
4. Restore from backup file
  ↓
5. Verify restore completed successfully
  ↓
6. Test critical functionality

Safe Restore Pattern

Connect
  ↓
Backup Database (safety backup)
  - File: C:\backups\pre_restore_safety.sql
  ↓
Restore Database (restore from backup)
  - File: C:\backups\restore_source.sql
  ↓
Try
    ├─ Query (verify data)
    └─ If (verification passes)
        └─ Success
Catch
    └─ Restore Database (rollback to safety backup)
        - File: C:\backups\pre_restore_safety.sql
Finally
    └─ Disconnect

Development Environment Refresh

Schedule Trigger (weekly)
  ↓
FTP Download (get latest prod backup)
  ↓
Connect (to dev database)
  ↓
Restore Database
  ↓
Non Query (anonymize sensitive data)
  ↓
Non Query (update configuration for dev)
  ↓
Disconnect
  ↓
Send Email (dev environment refreshed)

Multi-Database Restore

Loop (over database list)
  ↓
  Connect (to database)
    ↓
  Restore Database
    - File: C:\backups\{{dbname}}_latest.sql
    ↓
  Disconnect

Important Warnings

Data Loss Warning

danger

Restoring a database may DROP and RECREATE tables, causing data loss. Always backup the current database before restoring.

Production Restore Warning

warning

Never restore to production without:

1. Creating a safety backup first
2. Testing the restore in development
3. Verifying the backup file is correct
4. Having approval from appropriate stakeholders

Overwrite Warning

The restore operation will:

• Drop existing tables (if they exist in backup)
• Recreate tables with backup structure
• Insert data from backup (overwriting existing data)

Performance Considerations

• Database Size - Large databases take longer to restore
• Disk Speed - SSD vs HDD affects restore time
• Network - Remote databases restore slower than local
• Indexes - Index creation happens after data import
• Foreign Keys - Foreign key checks may slow restore

Restore Time Estimation

Database Size	Estimated Time
< 1 GB	1-10 minutes
1-10 GB	10-60 minutes
10-100 GB	1-3 hours
> 100 GB	Several hours

Common Errors and Solutions

Backup File Path Cannot Be Empty

Error: ErrInvalidArg: Backup file path cannot be empty

Solutions:

• Provide a valid file path in the Backup File Path field
• Ensure the path variable has a value
• Use absolute path (e.g., C:\backups\db.sql)

Backup File Not Found

Error: ErrNotFound: Backup file not found

Solutions:

• Verify the file path is correct
• Check that the backup file exists at the specified location
• Ensure file hasn't been moved or deleted
• Check file permissions (robot can read the file)
• Use File Exists node to verify file before restoring

Database Restore Failed

Error: ErrRuntime: Database restore failed

Common Causes:

• Insufficient disk space
• Database user lacks permissions (CREATE, DROP, INSERT)
• Invalid or corrupted backup file
• Connection lost during restore
• SQL syntax errors in backup file
• Version incompatibility

Solutions:

• Check available disk space
• Grant appropriate permissions to database user
• Verify backup file is valid (test in MySQL client)
• Use stable connection
• Ensure backup was created from compatible MySQL version
• Review MySQL error logs for specific errors

Access Denied

Error: Permission denied errors during restore

Solutions:

• Ensure database user has CREATE, DROP, INSERT, ALTER permissions
• Connect as user with sufficient privileges (e.g., root)
• Grant necessary permissions: GRANT ALL ON database.* TO 'user'@'host'

Table Already Exists

Error: Table 'tablename' already exists

Solutions:

• Backup file should include DROP TABLE IF EXISTS statements
• Manually drop existing tables before restoring
• Edit backup file to include DROP TABLE statements

Advanced Examples

Disaster Recovery Workflow

Monitoring System Detects Database Corruption
  ↓
Send Alert
  ↓
Connect (to affected database)
  ↓
Backup Database (backup corrupted state for analysis)
  - File: C:\backups\corrupted_{{timestamp}}.sql
  ↓
Restore Database (restore from last good backup)
  - File: C:\backups\hourly\last_good.sql
  ↓
Query (verify critical data)
  ↓
If (verification passes)
  ├─ Send Success Email
  └─ Resume Application
Else
  ├─ Restore from earlier backup
  └─ Send Critical Alert
  ↓
Disconnect

Clone Production to Staging

Connect (to staging database)
  ↓
Restore Database
  - File: \\network\backups\production_latest.sql
  ↓
Non Query (update configuration)
  - UPDATE config SET environment = 'staging'
  ↓
Non Query (anonymize PII)
  - UPDATE users SET email = CONCAT('user', id, '@staging.test')
  - UPDATE users SET phone = NULL
  ↓
Non Query (disable external integrations)
  - UPDATE api_keys SET enabled = 0
  ↓
Disconnect
  ↓
Send Email (staging environment ready)

Automated Test Environment Setup

Schedule Trigger (nightly)
  ↓
Download Latest Production Backup
  ↓
Loop (over test databases: test1, test2, test3)
    ↓
    Connect (to test database)
      ↓
    Restore Database
      ↓
    Non Query (configure for testing)
      ↓
    Disconnect
  ↓
Send Email (test environments ready for tomorrow)

Restore with Data Transformation

Connect
  ↓
Restore Database (restore backup)
  ↓
Non Query (transform data for new schema)
  - ALTER TABLE users ADD COLUMN full_name VARCHAR(255)
  - UPDATE users SET full_name = CONCAT(first_name, ' ', last_name)
  ↓
Non Query (cleanup old columns)
  - ALTER TABLE users DROP COLUMN first_name, DROP COLUMN last_name
  ↓
Disconnect

Verification After Restore

Basic Verification

-- Check table count
SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = 'your_database';

-- Check row counts
SELECT COUNT(*) FROM important_table;

-- Verify recent data
SELECT MAX(created_date) FROM orders;

Comprehensive Verification

Restore Database
  ↓
Query: Count tables
  ↓
Query: Count rows in main tables
  ↓
Query: Verify latest records
  ↓
JavaScript: Compare with expected values
  ↓
If (all checks pass)
  └─ Log Success
Else
  └─ Alert and Investigate

Compressed Backups

If backup is compressed, decompress first:

GZip (.gz)

Shell Command: gunzip backup.sql.gz
  ↓
Restore Database
  - File: backup.sql

7-Zip (.7z)

Shell Command: 7z e backup.7z
  ↓
Restore Database
  - File: backup.sql

Platform-Specific Paths

Windows

const backupPath = 'C:\\backups\\mydb.sql';
// or
const backupPath = 'C:/backups/mydb.sql'; // also works

Linux/macOS

const backupPath = '/home/user/backups/mydb.sql';
// or
const backupPath = '/var/backups/mysql/mydb.sql';

See Also

• Backup - Create database backups
• Connect - Establish database connections
• Query - Verify restored data
• Disconnect - Close connections