Why backup strategy prevents data loss in MySQL - See It in Action

Why Backup Strategy Prevents Data Loss

📖 Scenario: You manage a small online store database. You want to protect your data from accidental loss or damage.

🎯 Goal: Build a simple backup strategy using SQL commands to save and restore your data.

📋 What You'll Learn

Create a table called products with columns id, name, and price

Insert three specific products into the products table

Create a backup table called products_backup with the same structure

Copy all data from products into products_backup to simulate a backup

💡 Why This Matters

🌍 Real World

Backing up data is essential for any business to avoid losing important information due to mistakes or hardware failures.

💼 Career

Database administrators and developers use backup strategies daily to ensure data safety and business continuity.

1

Create the products table

Write a SQL statement to create a table called products with columns id as INT primary key, name as VARCHAR(50), and price as DECIMAL(5,2).

MySQL

-- Create the products table
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Insert products into the table

Insert these three products into the products table: (1, 'Pen', 1.20), (2, 'Notebook', 2.50), and (3, 'Eraser', 0.80). Use three separate INSERT INTO statements.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
-- Insert the three products below
-- Your code here

Need a hint?

Use INSERT INTO products (id, name, price) VALUES (...) for each product.

3

Create a backup table

Create a new table called products_backup with the same columns and types as products. Use CREATE TABLE with the same structure.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES (1, 'Pen', 1.20);
INSERT INTO products (id, name, price) VALUES (2, 'Notebook', 2.50);
INSERT INTO products (id, name, price) VALUES (3, 'Eraser', 0.80);

-- Create the backup table below
-- Your code here

Need a hint?

Use CREATE TABLE products_backup with the same columns as products.

4

Copy data to backup table

Copy all rows from products into products_backup using a single INSERT INTO ... SELECT statement.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (id, name, price) VALUES (1, 'Pen', 1.20);
INSERT INTO products (id, name, price) VALUES (2, 'Notebook', 2.50);
INSERT INTO products (id, name, price) VALUES (3, 'Eraser', 0.80);

CREATE TABLE products_backup (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

-- Copy data from products to products_backup below
-- Your code here

Need a hint?

Use INSERT INTO products_backup (id, name, price) SELECT id, name, price FROM products;