Backup, Recovery, and Security in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Learn backup, recovery, and security in DBMS with backup types, recovery techniques, logs, checkpoints, access control, authentication, and authorization.
Backup, recovery, and security are important parts of database administration. A database does not only need to store data; it must also protect data from loss, damage, failure, and unauthorized access.
Backup means creating a copy of database data so it can be restored later. Recovery means bringing the database back to a correct state after a failure. Security means making sure only authorized users can access or change the data.
These topics are important because real databases store valuable information such as student records, payments, bank balances, hospital reports, orders, passwords, and business data.
Why Backup Is Needed
A database can be damaged or lost because of hardware failure, software bugs, accidental deletion, cyberattack, power failure, natural disaster, or human mistake.
If there is no backup, data loss may become permanent. For a company or college system, losing data can cause financial loss, legal issues, and loss of trust.
Backup gives a safety copy. If the main database fails, the backup can be used to restore important data.
Types of Backup
Common backup types are listed below.
1. Full backup: Copies the complete database.
2. Incremental backup: Copies only data changed since the last backup.
3. Differential backup: Copies data changed since the last full backup.
4. Transaction log backup: Copies transaction logs used for point-in-time recovery.
5. Hot backup: Taken while the database is running.
6. Cold backup: Taken when the database is stopped.
Each backup type has a different purpose. Full backup is simple but may take more time and storage. Incremental and differential backups save time and storage, but recovery may need multiple backup files.
Backup Types Summary
This table compares important backup types.
| Backup Type | What It Copies | Main Benefit | Common Limitation |
| --- | --- | --- | --- |
| Full backup | Entire database | Easy to restore | Takes more time and storage |
| Incremental backup | Changes since last backup | Saves storage | Restore may need many backup files |
| Differential backup | Changes since last full backup | Faster restore than many incrementals | Grows larger until next full backup |
| Log backup | Transaction log records | Supports point-in-time recovery | Needs log management |
| Hot backup | Backup while DB runs | Less downtime | More complex |
| Cold backup | Backup while DB stopped | Simple and consistent | Requires downtime |
A good backup plan often combines more than one backup type. For example, a system may take a full backup weekly, incremental backups daily, and log backups frequently.
Full Backup
A full backup copies the entire database. It is the simplest backup to understand because it contains everything needed from that backup time.
The main advantage is easy restoration. If a full backup is available, the database can be restored to the state it had when the backup was taken.
The disadvantage is cost. Full backups may take more time and storage, especially for large databases.
Incremental Backup
An incremental backup stores only the changes made since the last backup, whether that last backup was full or incremental.
For example, if a full backup is taken on Sunday and incremental backups are taken on Monday and Tuesday, Tuesday's incremental backup stores only changes since Monday.
Incremental backups save storage and time. However, restoration may require the full backup plus all required incremental backups in correct order.
Differential Backup
A differential backup stores changes made since the last full backup.
If a full backup is taken on Sunday, then Monday's differential backup stores Monday changes. Tuesday's differential backup stores changes from Sunday to Tuesday.
Differential backups are usually easier to restore than many incremental backups because we need the full backup and the latest differential backup. But differential backups become larger over time until the next full backup.
Recovery in DBMS
Recovery means restoring the database to a correct state after a failure. The goal is to preserve committed transactions and undo incomplete transactions.
A database failure can happen while transactions are running. Some transactions may have committed, while others may be incomplete. Recovery decides what should be redone and what should be undone.
The DBMS uses transaction logs, checkpoints, backups, and recovery algorithms to restore consistency.
Types of Failure
Failures can be understood in categories.
1. Transaction failure: A transaction fails because of logical error, deadlock, or constraint violation.
2. System failure: The system crashes because of power failure or operating system issue.
3. Media failure: Storage device fails or data files are damaged.
4. Communication failure: Network interruption affects distributed database operations.
5. Human error: A user accidentally deletes or changes important data.
Different failures need different recovery actions. A transaction failure may only need rollback, while media failure may require restoring from backup.
Transaction Log
A transaction log records important actions performed by transactions. It stores information such as transaction start, updates, commits, and rollbacks.
The log helps the DBMS know what happened before a crash. During recovery, the DBMS can inspect the log and decide which operations should be redone and which should be undone.
```text
Transaction Log Example
START T1
T1 updates Account 101 balance from 1000 to 500
T1 updates Account 202 balance from 2000 to 2500
COMMIT T1
START T2
T2 updates Account 303 balance from 700 to 600
CRASH
```
Here, T1 committed before the crash, so its changes should remain. T2 did not commit, so its incomplete changes should be undone.
Undo and Redo
Undo and redo are two important recovery actions.
1. Undo: Reverses changes made by incomplete or failed transactions.
2. Redo: Reapplies changes made by committed transactions if they were not fully written to disk before failure.
Undo protects the database from half-completed work. Redo protects committed work from being lost after a crash.
In simple words, undo removes unsafe changes, and redo restores safe committed changes.
Checkpoint
A checkpoint is a point where the DBMS saves enough information to make recovery faster. It reduces the amount of log that must be checked after a crash.
Without checkpoints, the DBMS may need to scan a very long log from the beginning. With checkpoints, recovery can start from a more recent safe point.
```text
Log records before checkpoint | CHECKPOINT | Log records after checkpoint
After crash, recovery can focus mainly near the checkpoint instead of scanning everything.
```
Checkpoints improve recovery performance, especially in systems that run continuously for long periods.
Security in DBMS
Database security protects data from unauthorized access, misuse, damage, and leakage. It is not enough to store data correctly; the DBMS must also control who can see and modify it.
For example, a student may be allowed to view their own marks, but not change marks. A teacher may enter marks for assigned subjects. An admin may create accounts and manage permissions.
Security is important for privacy, legal compliance, financial protection, and trust.
Main Security Concepts
Important DBMS security concepts are listed below.
1. Authentication: Verifies who the user is.
2. Authorization: Decides what the user is allowed to do.
3. Access control: Applies permissions to database objects.
4. Encryption: Converts data into unreadable form without the correct key.
5. Auditing: Records user actions for monitoring and investigation.
6. Backup security: Protects backup files from theft or misuse.
Authentication answers the question: who are you? Authorization answers: what are you allowed to access?
Authentication
Authentication checks the identity of a user. The most common method is username and password, but modern systems may also use OTP, biometric login, tokens, or multi-factor authentication.
If authentication is weak, unauthorized users may enter the system. That is why strong passwords, secure login systems, and multi-factor authentication are important.
Authentication happens before permission checking. First the system confirms the user identity, then it checks allowed actions.
Authorization
Authorization decides what an authenticated user can do. One user may read a table, another user may insert data, and an admin may update or delete records.
In SQL, permissions can be granted and revoked.
```sql
GRANT SELECT ON Students TO teacher_user;
REVOKE SELECT ON Students FROM teacher_user;
```
`GRANT` gives permission, while `REVOKE` removes permission. These commands help control access at database level.
Roles and Privileges
A role is a group of permissions. Instead of assigning permissions one by one to every user, an admin can create roles such as Student, Teacher, Accountant, or Admin.
For example, the Teacher role may have permission to read student records and update marks. The Student role may only read personal result data. The Admin role may manage users and tables.
Roles make permission management cleaner and safer in large systems.
Encryption
Encryption protects data by converting it into unreadable form. Without the correct key, the data cannot be understood easily.
Encryption can protect data stored in the database and data moving between application and database. Passwords should not be stored as plain text. Sensitive fields such as payment details should be protected carefully.
Encryption does not replace access control, but it adds another layer of protection.
Auditing
Auditing means recording important database actions. It helps answer questions such as who logged in, who changed a record, who deleted data, and when an action happened.
Audit logs are useful for security investigation, debugging, compliance, and accountability.
For example, if marks are changed incorrectly, an audit log can help identify which user changed them and at what time.
Backup Security
Backups contain copies of important data, so they must also be protected. A stolen backup can be as dangerous as a stolen database.
Backup files should be encrypted, stored safely, tested regularly, and accessible only to authorized users. Organizations should also keep backups in separate locations to protect against local disasters.
A backup is useful only if it can be restored successfully. That is why backup testing is an important part of database maintenance.
Important Points to Remember
Backup protects against data loss, recovery restores a correct database state, and security protects data from unauthorized access.
Recovery commonly uses logs, checkpoints, undo, and redo. Undo cancels incomplete work, while redo restores committed work.
Security depends on authentication, authorization, access control, encryption, auditing, and safe backup handling. These ideas work together to keep database data reliable and protected.