SOP DB Backup and Restore
SOP Number IT/013/R.1
SOP Title DB Backup and Restore
NAME
TITLE
DATE
Author
Anand Ghatnatti
Database Admin
27/11/2022
Reviewer
Milind Khedekar
Senior Manager
27/11/2022
Authoriser
Mahaveer Devannavar
General Manager
27/11/2022
Effective Date :
01/12/2022
Review Date :
28/11/2022
1. purpose
The purpose of the database server backup is to create a copy of data that can be recovered in the event of a primary data failure. Primary data failures can be the result of hardware or software failure, data corruption, or a human-caused event, such as a malicious attack ( virus or malware ), or accidental deletion of data
Database server restore is the process of copying backup data from secondary storage and restoring it to its original location or a new location. A restore is performed to return data that has been lost, stolen or damaged to its original condition or to move data to a new location
Rebuilding some or all data on permanent storage media by applying appropriate data from removable and/or permanent storage media to permanent storage media
2. introduction
Backup of database server is taken to safeguard the work carried by individual, team or department for organisation. It’s intellectual property of respective organisation. Restoration is the process carried out in case of non-availability of data at it’s original location due to corruption, deletion, virus attack
3. Scope
This procedure applies to database stored electronically on premises servers managed by Somaiya IT department
Database server is managed by Somaiya IT department may reside within premises or outside of premise
4. responsibilities
4.1 Role of Database Administrator:
The Database Administrator is responsible for ensuring that database server backup are scheduled and performed regularly. In the event that these uncover backup and/or recovery problems, the database administrator is responsible for escalating the problem to the Senior Manager, Head of Department, ensuring that a root cause analysis is performed and confirming that the problems are corrected. Such failure incidence are recorded in incidence report for future references and analysis
5. SPECIFIC PROCEDURE
5.1 Data to be backed up:
All data of database server that are considered to be business critical for running day-to-day operations considered “Critical Data” and must be protected from loss (accidental or intended). These include,
5.1.1 Data that, if list, would prevent Organisation from conducting normal business
operations now or in the future
5.1.2 Data that has been entrusted to Organisation, either by customers or partners
5.1.3 Data that, if lost, could subject Organisation to legal or contractual penalties
5.1.4 Data that, if lost, could adversely affect Organisation reputation
5.2 General Procedure:
Backup of database server shall be conducted regularly according to an established backup schedule. A record of all database server backup activities shall be maintained in a backup log. Backup logs must be reviewed daily by the System Administrator
5.3 Backup Schedule:
5.3.1 Log backup is performed every 15 minutes after the full backup is completed
5.3.2 Full backups is performed everyday in night at around 2.30 AM
5.3.3 dmp backup is performed everyday in night
5.4 System backup policy:
In addition to business data, IT Team is performing regular database server backup and restoration tests managed by Department of Information Technology. Data to be backed up includes,
5.4.1 Database servers backup, include full backup, RMAN log backup, archive log backup, dmp backup
5.5 Backup media storage:
Backup media must be stored in a location separate from the server room. The location
must be secure from intrusion and include fire and water damage prevention
mechanisms as part of disaster recovery plan
5.6 Backup media retention:
5.6.1 Daily log backup is pointed on on-premise NAS box and retain for two weeks.
5.6.2 Everyday full backup is taking place on NAS box and retain for two weeks.
5.7 Back-up device health check:
Health check of back-up devices are performed on regular basis on every quarter. Upgradation of backup devices are considered on used case basis considering the health, performance and technical scalability of backup device
5.8 Restore testing procedure:
Backup tests are to be performed quarterly for database backup system. A restoration test must consist of a minimum of either 25 files or 100 megs of data. Restoration test documentation is to be kept by IT in repository. The data restoration is tested on test environment to test the integrity of data backed up
5.9 Restore Procedures:
Restores are performed on incidence basis. Documentation of the incidence and confirmation of the restore is documented with sign off from requestor
5.10 Database restoration:
This is process of restoring the database from latest backup
RPO : RPO recovery point objective is a time-based measurement of the maximum amount of data loss that is tolerable to an organization. Also called backup recovery point objective, RPO is additionally important to determining whether the organization's backup schedule is sufficient to recover after a disaster. RPO (Recovery Point Objective) is 15 minutes
RTO : The recovery time objective (RTO) is the targeted duration of time between the event of failure and the point where operations resume. A recovery point objective (RPO) is the maximum length of time permitted that data can be restored from, which may or may not mean data loss. RTO is 120 minutes
5.11 Mock drill:
Mock drill for database is performed once every six month by system administrator
6. Definitions:
6.1 Backup:
Backups are files, equipment, data and procedures (“Data”) available for use in the event of a failure or loss, if the originals are destroyed or out of service
6.2 Full backup:
A full backup includes all the data that are specified in the backup selections list for the policy, regardless of when they were last modified or backed up
6.3 Archive log backup:
The archive log files are included in database backups and are used for roll-forward recovery of the database to the current point-in-time. All logs since the last full database backup must be available to the restore function. These log files are stored in the archive log
6.4 RMAN:
RMAN log file is a just text file that stores the complete output of RMAN commands. Generally, this file is used to check the backup has been done successfully or not. If there is any error occurs during a backup it will be captured in this log file
6.5 Restore:
The process of reinstating data that have been lost
7. forms / Templates to be used
A standard templates is used to record incidence by Department of Information Technology
8. Change History
SOP No.
Effective
Date
Significant Changes
Previous
SOP no.
IT/013/R.1
01/12/2022
First version
N.A.
IT/013/R.2
01-10-2024
Second Version
IT/013/R.1