Unit 7

Database Backup Recovery, and Security

7.1 INTRODUCTION TO BACKUP

7.2 TYPES OF BACKUP

7.2.1 PHYSICAL BACKUP

7.2.2 LOGICAL BACKUP

7.3 REASONS FOR DATABASE FAILURE

7.4 METHODS OF DATABASE BACKUP

7.5 CONCEPT OF RECOVERY, REDO/UNDO

7.6 INTRODUCTION TO DATABASE SECURITY

7.7 COMMON THREATS IN DATABASE

1
INTRODUCTION TO BACKUP

 A database backup is a copy of the data stored in a database at a specific point in time.
 It includes the database's schema (structure) as well as its contents (data).
 These backups serve as a safeguard against data loss, corruption, accidental deletion, or other
disasters.
 Database backups are essential for ensuring the integrity, availability, and recoverability of data
stored within databases.

WHY ARE DATABASE BACKUPS IMPORTANT?

 Databases store critical information for organizations, such as customer data, financial records,
and business transactions.
 Losing this data could have severe consequences, including financial loss, legal ramifications, and
damage to reputation.
 Database backups provide a way to restore data to a previous state in the event of data loss or
corruption.

Advantages of Data Backup, Recovery & Security:

Data backup and recovery offer several advantages for individuals and organizations:

1. Data Loss Prevention: The primary advantage of data backup and recovery is the prevention of data
loss. By regularly backing up data, organizations can protect against accidental deletion, hardware
failures, software corruption, cyber-attacks, and other forms of data loss.

2. Business Continuity: Data backups are essential for maintaining business continuity. In the event of a
disaster or data loss incident, organizations can quickly recover their critical data and resume operations,
minimizing downtime and preventing significant financial losses.

3. Protection against Cyber-attacks: With the increasing frequency and sophistication of cyber-attacks
such as ransomware, data backup and recovery have become critical for protecting against data extortion.
Having backups allows organizations to restore their data without paying ransom demands.

4. Peace of Mind: Knowing that data is securely backed up and can be quickly restored provides peace of
mind to individuals and organizations. It reduces anxiety about potential data loss incidents and allows
focus to remain on core business activities.

5. Cost Savings: The financial impact of data loss, including lost productivity, revenue, and reputation
damage, often far outweighs the investment in backup solutions.

6. Data Integrity: Backup and recovery processes help maintain data integrity by preserving the
consistency, accuracy, and reliability of stored information. By restoring data from backups,
organizations can ensure that they are working with clean, uncorrupted data.

7. Disaster Recovery Preparedness: Data backup is a critical component of disaster recovery planning. By
having robust backup and recovery processes in place, organizations can effectively respond to disasters
such as natural disasters, hardware failures, or cyber-attacks, minimizing their impact on operations.

8. Competitive Advantage: Having reliable data backup and recovery capabilities can provide a
competitive advantage by enhancing customer confidence, demonstrating commitment to data security,
and enabling faster recovery from disruptions compared to competitors without robust backup solutions.

2
Challenges of Database Backup, Recovery and Security:

Certainly, here are some challenges associated with database backup, recovery, and security:

 Scheduling backups regularly: Data backup cannot be a one-time process; it requires continuous
backup creation to guarantee that you always have the most recent file available. There should be to
establish a regular routine for this process.

 Preventing data corruption: If you find that some of the files are corrupted, or worse, completely
missing, recovering the backup copies of those files won't help you much. For this reason, it is
obviously crucial to keep your backup secure.

 Aligning with your architecture: Do you have the physical IT assets/resources needed to back up all
your data, secure it, and have it available when you need it most? It’s important to make sure your
backup strategies and your IT architecture are on the same page, working together.

 Setting data backup priorities: The truth is that not all data is created equal – some files are more
important than others, and in a time of crisis, you need access to your most important stuff first. If you
have a clear list of your top data priorities, you can act quickly under pressure and make smart
decisions.

 Protecting against ransomware: Attacks from ransomware and other threats are common, and
they’re only becoming more so in today’s climate. You will need to frequently update your security
infrastructure, ensuring that you always have the right tools to detect and neutralize suspicious
activity.

TYPES OF BACKUP:
 Database Backup is a copy of storage of data that is stored on a server.
 It is a safeguard against unexpected data loss and application errors.
 It protects the database against data loss.
 If the original data is lost, then using the backup it can be reconstructed.
 There are two types of database backup i.e.
 Physical backup &
 Logical backup
Physical Backup:

 Physical database backups are backups/copies of physical files/actual files that are used to store
and recover/restore databases.
 These include different data files, control files, archived redo logs, and many more.
 Typically, physical backup data is kept/maintained in the cloud, offline storage, magnetic tape, or
on a disc.
 This type of backup is useful when the user needs to restore the complete database in a short
period.
 It is beneficial to provide details of transactions and changes made in databases.
 It is considered the foundation of the recovery mechanism. This form of backup has the drawback
of slowing down database operations.
 There are two methods to perform a physical backup: They are Operating system utilities and
Recovery manager.

3
Advantages of Physical Backup:
 It is useful when the user needs to restore the complete database in a short period.
 They provide details of transactions and changes made in databases.
Disadvantages of Physical Backup:
 This slows down database operations.

Logical Backup:

 It contains logical data/information which is retrieved from the database.

 It contains a view, procedure, function, and table.
 This backup is useful when users want to restore or transfer a copy of the database to a
different/another location.
 Logical backups are not as secure as physical backups in terms of preventing data loss.
 It only provides structural details.
 Every week, complete logical backups should be performed.
 Logical backups are used as a supplement to a physical backup.

Advantages of Logical Backup:

 This is useful when the user needs to restore the complete database to the last time.
 It was more complex and provides granular recovery capabilities.

Disadvantages of Logical Backup:

 Critical for recovery of special components.
 Less secure compared to physical backup.
 It only provides structural details.

4
Physical Backup Vs Logical Backup:

Physical Backup Logical Backup

Physical database backups are backups of physical Logical database backups are backups of logical
files that are used to store and recover databases. files that are retrieved from the database.

It contains data files, control files, and archived It contains a view, a procedure, a function, and a
redo logs. table.

It copies data files when data is running or stopped. Using the EXPORT keyword Logical backup is
done

This is useful when users want to restore or

A user needs to restore the complete database in a
transfer a copy of the database to a different
short period of time.
location.

More secure than logical backup. Less secure as compared to Physical backup.

REASONS FOR DATABASE FAILURE:

 Failure in terms of a database can be defined as its inability to execute the specified transaction or
loss of data from the database.
 A DBMS is vulnerable to several kinds of failures and each of these failures needs to be managed
differently.
 There are many reasons that can cause database failures such as network failure, system crash,
natural disasters, carelessness, sabotage (corrupting the data intentionally), software errors, etc.

Transaction Failure:

If a transaction is not able to execute or it comes to a point from where the transaction becomes
incapable of executing further then it is termed as a failure in a transaction.

5
Reason for a transaction failure in DBMS:
1. Logical error: A logical error occurs if a transaction is unable to execute because of some mistakes
in the code or due to the presence of some internal faults.
2. System error: Where the termination of an active transaction is done by the database system itself
due to some system issue or because the database management system is unable to proceed with the
transaction. For example– The system ends an operating transaction if it reaches a deadlock
condition or if there is an unavailability of resources.

System Crash:

 A system crash usually occurs when there is some sort of hardware or software breakdown. Some
other problems which are external to the system and cause the system to abruptly stop or eventuall y
crash include failure of the transaction, operating system errors, power cuts, main memory crash,
etc.
 These types of failures are often termed as soft failures and are responsible for the data losses in the
volatile memory. It is assumed that a system crash does not have any effect on the data stored in the
non-volatile storage and this is known as the fail-stop assumption.

Data-transfer Failure:

 Data-transfer failures are defined as disc failures that occur during data transfer operations and
cause the loss of content from disc storage. Some other reason for disk failures includes disk head
crash, disk unreachability, formation of bad sectors, read-write errors on the disk, etc.
 In order to quickly recover from a disk failure caused in the middle of a data-transfer operation, the
backup copy of the data stored on other tapes or disks can be used. Thus it’s a good practice to
backup your data frequently.

There are some common causes of failures such as,

1. System Crash
2. Transaction Failure
3. Network Failure
4. Disk Failure
5. Media Failure
Each transaction has ACID property. If we fail to maintain the ACID properties, it is the failure of the
database system.

METHODS OF DATABASE BACKUP :

Backup Methods:
 Full backup
 Incremental backup
 Differential backup
 Mirror backup
 Full PC backup

6
 Local Backup
 Off side backup
 Online backup
 Remote Backup
 Cloud Backup
 FTP backup

1. Full backup: VI

 A full backup is a method of backup in which all of the documents and envelopes selected for
backup are supported.
 When the backup is later restored, the complete list of documents will be kept.
 The advantage of this backup is that it makes recovery quick and simple because it continually
saves the entire list of documents.
 The disadvantage is that because the entire list of documents is duplicated, each backup
operation uses a significant amount of energy.
 Full backups take up a lot more space than developing or splitting backups.

2. Incremental backup: VI

 A developing backup is a backup of all changes made since last backup.

 With expanding backup, a full backup happens first, and then subsequent backups carry out
progressions created from the last backup.
 The result is a rapid/quick backup, and afterward full backup for each backup run.
 The use of additional space is minimal compared to full backup, and there is little difference
between the two.
 Reestablishing is slower than full backup and differential backup.

3. Differential backup:

 All advancements made since the last full backup are included in the differential backup.
 In a differential backup, the last full backup is used as starting point, and the subsequent
backup runs the progressions that were created from it.
 The end result is a very rapid backup, followed by a full backup for each backup run.
 When expanding backup is used, extra space is not used as much as it would be with a complete
backup.
 Reclamations are often speedier than full backups, but they are slower than full backups in
general.

4. Mirror Backup:

 Mirror Backup supports an up-to-date mirror of the source, as the name implies.
 When a record is deleted from the source, it will unavoidably be deleted from the mirror backup
as well.

7
 Because both mirror backup and records can be destroyed by virus or unintentional record
decimation, it is important to use mirror backup carefully.

5. Full PC backup:

 In this backup, it's not supported up close to home documents, but the entire contents of the
PC's hard disc are backuped.
 You can restore the PC hard drive to its carefully maintained state after backing it up
completely.
 With full PC backup, you may restore not just work files, photos, videos, and audio files but
also operating systems, device drivers, framework data, libraries, programmes, messages and
more.

6. Local backup:

 Any backup method where the capacity media is kept nearby, in the same structure as the
source, is referred to as local backup.
 This can be a network attached storage device, an external hard drive connected to the
computer, a second internal hard drive, or CD/DVD and others.
 Local backups defend a sophisticated material from infection attacks and hard disc failures.
 They reassure or correct mistakes that were done accidently.
 Backups are always nearby, and they are easy to reinstall and useful.

7. Offsite backup:

 At the point when backup stockpiling medium is set in an alternate topographical area from
source, it is called offsite backup.
 Backups should be possible locally from outset, however, in wake of moving capacity media
to another area, it turns into an offsite backup.
 Instances of offsite backup are moving backup media or hard drives at home, in another
place of business, or in bank safe store box.

Not with standing similar insurance given by local backups, offsite backups give extra
assurance against burglary, fire, flood, and other cataclysmic events.
 Setting backup media in following room isn’t considered offsite backup as it doesn’t give
backup security against robbery, fire, flood, and other cataclysmic events.

8. Online backup:

 These are backups that are made continuously or repeatedly using storage or capacity media
and are continuously linked to a source that is maintained.

8
 As a general rule, capacity media is off-site and connected to the backup source via a system or
internet connection.
 It excludes the use of human intermediaries to connect drives and storage media and conduct
backups.
 It is currently made available to customers as membership administration by numerous
commercial server farms.
 Capacity server farms are located far from the information source, and information is securely
transported from the source to the put-away farm over the internet.

9. Remote backup:

 Remote backup is type of offsite backup that varies depending on your ability to access, restore,
or maintain backups that are located at your base area or elsewhere.
 To make a backup, you shouldn’t be present in backup storeroom.
 For instance, it is not considered remote backup if your backup hard disc is kept in your bank's
safe deposit box.
 Without going to the bank, you cannot complete this.
 Online backup is also frequently referred to as remote backup.

10. Cloud backup:

 The term is frequently utilized with online backup and remote backup.
 This is place information is sponsored up to help storerooms associated with Internet.
 With appropriate login accreditation’s, that backup can be gotten to or reestablished from
another PC with Internet.

11. Ftp Backup:

 This is sort of backup where backup is done by means of File Transfer Protocol (FTP) to FTP
worker on Internet.
 FTP worker is normally situated in business server farm away from source information.
 This is another type of offsite backup when FTP worker is in an alternate area.

Concept of Recovery, Redo/Undo:

 Recovery is the rebuilding of a database or table space after a problem such as media or storage
failure, power interruption, or application failure.
 If you have backed up your database or individual table spaces, you can rebuild them should
they become damaged or corrupted in some way.

Types of Transaction Recovery:

Recovery information is divided into two types:

9
 Undo (or Rollback) Operations
 Redo (or Cache Restore) Operations

Undo Operation:

 The DBMS server performs undo or transaction back out recovery.

 For example, when a transaction is aborted, transaction log file information is used to roll back all related
updates.
 The DBMS server writes the compensation Log Records (CLRs) to record a history of the actions taken
during undo operations.

[Compensation Log Record (CLR) notes the rollback of a particular change to the database. Each corresponds with exactly one other Update Log Record
(although the corresponding update log record is not typically stored in the Compensation Log Record).]

Redo Operation:

 A redo recovery operation is database-oriented.

 Redo recovery is performed after a server or an installation fails.
 Its main purpose is to recover the contents of the DMF cached data page that are lost when a fast-
commit server fails.
 Redo recovery is performed by the recovery process.
 Redo recovery precedes undo recovery.

[A Data Management Framework (DMF) is a set of software tools and technologies used to manage and organize data within a
SQL Server database.]

Introduction to Database Security:

 The method used to safeguard and secure databases from malicious or unintentional risks is known as
database security.
 Database security refers to the various measures taken to ensure the protection of database content
from unauthorized access, malicious destruction, alteration of data and other internal and external
threats.
 Database security refers to the range of tools, controls and measures designed to establish and preserve
the database confidentiality, integrity, availability and secrecy.

DB security must address and protect the following:

 The data in the database.

 The DBMS.
 Any associated applications.
 The physical database server or the virtual database and the underlying hardware.
 The computing and or network infrastructure used to access the database.

We consider database security about the following situations:

 Theft and fraudulent

 Loss of confidentiality or secrecy
 Loss of data privacy
 Loss of data integrity
 Loss of availability of data

Common threats in database:

10
 When referring to "common threats in a database," it typically encompasses the various risks and
vulnerabilities that databases face, potentially leading to unauthorized access, data breaches, data
loss, or other security incidents.
 These threats can arise from internal or external sources and may exploit vulnerabilities in the
database management system (DBMS), applications interacting with the database, or the
underlying infrastructure.

According to technology vendor Application Security, Inc., the following are the top 10 threats
related to databases:

 Default or weak passwords

 SQL injection: Attackers inject malicious SQL code into database queries, potentially allowing them to
access, modify, or delete data.
 Excessive user and group privileges
 Unnecessary DBMS features enabled
 Broken configuration management
 Buffer overflows
 Privilege escalation(increase)
 Denial of service: Attackers may overwhelm the database server with a flood of requests, causing
degraded performance or complete unavailability of services.
 Unencrypted data

To mitigate these threats, organizations should implement security measures such as encryption, access
controls, regular security audits, employee training on security awareness etc.

11