Restoring Databases & Running SQL Dump Scripts0%

Restoring Databases & Running SQL Dump Scripts

Beginner12 min read•Updated: 2026-09-12

Restoring Databases & Running SQL Dump Scripts

Taking regular backups is only half the battle; knowing how to restore them under pressure during an outage or when setting up a fresh development environment is what separates junior coders from professional database engineers. In this tutorial, you will learn the standard techniques for importing and restoring SQL scripts.

1. Method 1: Restoring via Operating System Shell Redirection

The fastest and most common way to import a .sql dump file is using shell input redirection (<):

1# Step 1: Create the target database if it does not already exist:
2mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS ecommerce_db;"
3 
4# Step 2: Stream the SQL dump file into the database:
5mysql -u root -p ecommerce_db < ecommerce_backup.sql

Restoring a Gzipped Backup:

If your backup is compressed (.gz), you can decompress and pipe it directly into MySQL without extracting the full file to disk first:

1# Linux / macOS / Git Bash:
2gunzip < ecommerce_backup.sql.gz | mysql -u root -p ecommerce_db

2. Method 2: Importing Inside MySQL CLI with `SOURCE`

If you are already logged into the interactive mysql> prompt, use the built-in SOURCE command (or shorthand \.):

1-- Step 1: Select the database you want to populate
2USE ecommerce_db;
3 
4-- Step 2: Execute the external script file:
5SOURCE C:/Users/Public/Documents/ecommerce_backup.sql;
6 
7-- Or on Linux/macOS:
8SOURCE /var/backups/ecommerce_backup.sql;

Note

[!NOTE] On Windows, always use forward slashes (/) or escaped backslashes (\\) in file paths inside the SOURCE command! Writing C:\Users can trigger escape character interpretation errors.

3. Method 3: Visual Import in MySQL Workbench

For visual database management:

1
Open MySQL Workbench and connect to your server.
2
In the menu, go to Server -> Data Import.
3
Select "Import from Self-Contained File" and browse to your .sql file.
4
Under "Default Target Schema", select your destination database (or click "New...").
5
Click the "Start Import" button in the lower right corner and monitor progress.

4. Speeding Up Massive Data Imports

When importing huge database dumps (gigabytes of data), standard execution can be slow because every inserted row checks foreign keys and recalculates secondary indexes. You can speed up imports by wrapping the session:

1-- Speed up restoration of massive dumps:
2SET FOREIGN_KEY_CHECKS = 0; -- Disable referential integrity checks during load
3SET UNIQUE_CHECKS = 0;      -- Disable unique index checks during load
4SET AUTOCOMMIT = 0;         -- Disable auto-commit per line
5 
6-- Import your script here --
7 
8COMMIT;
9SET UNIQUE_CHECKS = 1;      -- Re-enable unique checks
10SET FOREIGN_KEY_CHECKS = 1; -- Re-enable foreign key checks

5. Best Practices & Common Pitfalls

Avoid the "No Database Selected" Error: If your .sql file does not contain an explicit USE db_name; statement at the top, running mysql -u root -p < file.sql will abort with Error 1046: No database selected. Always specify the database name on the command line!
Check max_allowed_packet on Large Inserts: If the backup contains giant batch insert statements, ensure the target server's max_allowed_packet is set large enough to receive the payload.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which command-line syntax imports a file named backup.sql into a database named school_db?

Question 2

Which command executed inside the interactive mysql> prompt executes an external SQL script file?

Question 3

Why should Windows file paths use forward slashes (e.g. C:/data/backup.sql) inside the SOURCE command?

Question 4

What is the cause of "Error 1046: No database selected" when importing a script?

Question 5

Why do DBAs temporarily disable FOREIGN_KEY_CHECKS during large database restorations?

Next Lesson

Exporting & Importing CSV Data

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Backups with mysqldump	Exporting & Importing CSV Data

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Exporting & Importing CSV Data

Restoring Databases & Running SQL Dump Scripts

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Restoring Databases & Running SQL Dump Scripts

1. Method 1: Restoring via Operating System Shell Redirection

The fastest and most common way to import a .sql dump file is using shell input redirection (<):

1# Step 1: Create the target database if it does not already exist:
2mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS ecommerce_db;"
3 
4# Step 2: Stream the SQL dump file into the database:
5mysql -u root -p ecommerce_db < ecommerce_backup.sql

Restoring a Gzipped Backup:

If your backup is compressed (.gz), you can decompress and pipe it directly into MySQL without extracting the full file to disk first:

1# Linux / macOS / Git Bash:
2gunzip < ecommerce_backup.sql.gz | mysql -u root -p ecommerce_db

2. Method 2: Importing Inside MySQL CLI with `SOURCE`

If you are already logged into the interactive mysql> prompt, use the built-in SOURCE command (or shorthand \.):

1-- Step 1: Select the database you want to populate
2USE ecommerce_db;
3 
4-- Step 2: Execute the external script file:
5SOURCE C:/Users/Public/Documents/ecommerce_backup.sql;
6 
7-- Or on Linux/macOS:
8SOURCE /var/backups/ecommerce_backup.sql;

Note

[!NOTE] On Windows, always use forward slashes (/) or escaped backslashes (\\) in file paths inside the SOURCE command! Writing C:\Users can trigger escape character interpretation errors.

3. Method 3: Visual Import in MySQL Workbench

For visual database management:

1
Open MySQL Workbench and connect to your server.
2
In the menu, go to Server -> Data Import.
3
Select "Import from Self-Contained File" and browse to your .sql file.
4
Under "Default Target Schema", select your destination database (or click "New...").
5
Click the "Start Import" button in the lower right corner and monitor progress.

4. Speeding Up Massive Data Imports

1-- Speed up restoration of massive dumps:
2SET FOREIGN_KEY_CHECKS = 0; -- Disable referential integrity checks during load
3SET UNIQUE_CHECKS = 0;      -- Disable unique index checks during load
4SET AUTOCOMMIT = 0;         -- Disable auto-commit per line
5 
6-- Import your script here --
7 
8COMMIT;
9SET UNIQUE_CHECKS = 1;      -- Re-enable unique checks
10SET FOREIGN_KEY_CHECKS = 1; -- Re-enable foreign key checks

5. Best Practices & Common Pitfalls

Avoid the "No Database Selected" Error: If your .sql file does not contain an explicit USE db_name; statement at the top, running mysql -u root -p < file.sql will abort with Error 1046: No database selected. Always specify the database name on the command line!
Check max_allowed_packet on Large Inserts: If the backup contains giant batch insert statements, ensure the target server's max_allowed_packet is set large enough to receive the payload.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which command-line syntax imports a file named backup.sql into a database named school_db?

Question 2

Which command executed inside the interactive mysql> prompt executes an external SQL script file?

Question 3

Why should Windows file paths use forward slashes (e.g. C:/data/backup.sql) inside the SOURCE command?

Question 4

What is the cause of "Error 1046: No database selected" when importing a script?

Question 5

Why do DBAs temporarily disable FOREIGN_KEY_CHECKS during large database restorations?

Next Lesson

Exporting & Importing CSV Data

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Backups with mysqldump	Exporting & Importing CSV Data

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Restoring Databases & Running SQL Dump Scripts

1. Method 1: Restoring via Operating System Shell Redirection

Restoring a Gzipped Backup:

2. Method 2: Importing Inside MySQL CLI with SOURCE

3. Method 3: Visual Import in MySQL Workbench

4. Speeding Up Massive Data Imports

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Exporting & Importing CSV Data

Related Lessons

Practice Quiz

Loading Page Content...

Restoring Databases & Running SQL Dump Scripts

1. Method 1: Restoring via Operating System Shell Redirection

Restoring a Gzipped Backup:

2. Method 2: Importing Inside MySQL CLI with SOURCE

3. Method 3: Visual Import in MySQL Workbench

4. Speeding Up Massive Data Imports

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Exporting & Importing CSV Data

Related Lessons

Practice Quiz

2. Method 2: Importing Inside MySQL CLI with `SOURCE`

2. Method 2: Importing Inside MySQL CLI with `SOURCE`