Backing up and Restoring databases

Backing up and restoring databases is a crucial aspect of database management, ensuring data availability and integrity in case of failures, data corruption, or accidental deletions. The process and tools used for backup and restoration depend on the database management system (DBMS) in use.

MySQL
Backup

Using mysqldump:
mysqldump -u [username] -p [database_name] > backup.sql

  • -u [username]: Specifies the MySQL username.
  • -p: Prompts for the password.
  • [database_name]: The name of the database to back up.
  • > backup.sql: Redirects the output to a file named backup.sql.

    • Example:
    mysqldump -u root -p mydatabase > backup.sql


    Restore
    Using mysql:
    mysql -u [username] -p [database_name] < backup.sql

    Example:
    mysql -u root -p mydatabase < backup.sql


    PostgreSQL
    Backup

    Using pg_dump:
    pg_dump -U [username] -d [database_name] -F c -b -v -f backup.dump

  • -U [username]: Specifies the PostgreSQL username.
  • -d [database_name]: The name of the database to back up.
  • -F c: Specifies the format (custom).
  • -b: Includes large objects.
  • -v: Verbose mode.
  • -f backup.dump: The output file

    • Example:
    pg_dump -U postgres -d mydatabase -F c -b -v -f backup.dump

    Restore
    Using pg_restore:
    pg_restore -U [username] -d [database_name] -v backup.dump

    Example:
    pg_restore -U postgres -d mydatabase -v backup.dump


    SQL Server
    Backup

    Using T-SQL:
    BACKUP DATABASE [database_name] TO DISK = 'C:\path\to\backup.bak'
    Example:
    BACKUP DATABASE [mydatabase] TO DISK = 'C:\backups\mydatabase.bak'


    You can run this T-SQL command using SQL Server Management Studio (SSMS) or any SQL Server client tool.
    Restore
    Using T-SQL:

    RESTORE DATABASE [database_name] FROM DISK = 'C:\path\to\backup.bak'

    Example:
    RESTORE DATABASE [mydatabase] FROM DISK = 'C:\backups\mydatabase.bak'

    Oracle
    Backing up and restoring databases in Oracle can be done using various methods, including the Oracle Recovery Manager (RMAN), Data Pump, and SQL*Plus. Here, I'll focus on RMAN , which is the most commonly used tool.

    Using RMAN

    Backup

    Full Database Backup:

    1.Connect to RMAN:

    rman target /

    2.Run the backup command:
    BACKUP DATABASE;

    Restore

    1.Connect to RMAN:
    rman target /

    2.Restore and recover the database:
    SHUTDOWN IMMEDIATE;
    STARTUP MOUNT;
    RESTORE DATABASE;
    RECOVER DATABASE;
    ALTER DATABASE OPEN;

    Comments

    Post a Comment

    Popular posts from this blog

    KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

     About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
    Read more

    KTU DBMS LAB CSL 333 Syllabus and Experiments

     Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
    Read more

    Creating a Database and Table ,executing queries - MySQL Lab Exercise

    Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
    Read more