Data Recovery Assignment: Database Backup, Recovery, and Scheduling

This assignment solution focuses on data recovery for a university's online Moodle database. It details the system's data update nature, recovery models, and the use of full mode recovery. The Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are defined in the context of databas...Read More

Running head: DATA RECOVERY
Data Recovery
Name of the Student:
Name of the University:
Author Note

1
DATA RECOVERY
Description of the system and the nature of the data updates
The database which has been taken into consideration for this report is the Online Moodle
for a university. The details such as the students, courses are stored in the database. In addition
to this, the enrollments for the students are done in the database. The enrollment procedure is
performed in the system in a yearly basis. Hence, the transactions in the databases are completed
on yearly basis and the concurrency would take place per transaction for the enrollments.
Recovery model
The full mode recovery is to be done for the Moodle database. The script provide below
can be used for the recovery of the database.
ALTER DATABASE MoodleDb SET RECOVERY FULL
GO
The recovery model for the database can be changed at any time as per the requirement of
the database administrator. However, it has been thought to be a good practice to create a full
recovery for the database.
RPO
RPO is Recovery Point Objective is one of the two solid business continuity and disaster
recovery plans. The RPO is used for the limiting the rollback in the database and the definition
for the maximum amount of allowable data which can be lost for the database in situations where
there is a failure to make a valid backup for the database.

2
DATA RECOVERY
RTO
RTO is Recovery Time Objective is one of the two solid business continuity and disaster
recovery plans. It is related to downtime for the representation of the restoring time of the
database. The restoration is generally done from the instances in which the user is able to
perform the usual functions.
Backup Schedule
The backup for the databases are scheduled to perform the reliability and safety check for
the databases and hence, it is important that the time for the backup is fixed and a schedule is
formed. This would be useful in regularly backing up the data and assuring that important data is
not lost. The university would be requiring their database to be backed up on monthly basis as
there are large number of students coming with their huge data sets.
Backup Statement
The following backup statements would be used for the backup of the database of
University:
CREATE PROCEDURE [dbo]. [sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR (1),
@backupLocation nvarchar (200)
AS
SET NOCOUNT ON;

3
DATA RECOVERY
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
@DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','UniDb','pubs','MoodleDb')
END
ELSE IF @backupType='D'
BEGIN

4
DATA RECOVERY
DELETE @DBs where DBNAME IN ('tempdb','UniDb','pubs','MoodleDb')
END
ELSE IF @backupType='L'
BEGIN
DELETE @DBs where DBNAME IN
('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE
BEGIN
RETURN
END
Job SQL Statement
The Job SQL Statement is provided below:
USE moodleDb;
GO
EXEC sp_add_jobstep
@job_name = N'Monthly Student Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'TSQL',

5
DATA RECOVERY
@command = N'ALTER DATABASE ENROLLMENTS SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5 ;
GO

6
DATA RECOVERY
Bibliography
Kathpal, A. and Sehgal, P., 2017. {BARNS}: Towards Building Backup and Recovery for
NoSQL Databases. In 9th {USENIX} Workshop on Hot Topics in Storage and File Systems
(HotStorage 17).
Zhou, X., 2017, May. Discussion on Backup and Recovery Technology of Computer Database.
In 2017 2nd International Conference on Materials Science, Machinery and Energy Engineering
(MSMEE 2017). Atlantis Press.
Dudjak, M., Lukić, I. and Köhler, M., 2017, May. Survey of Database Backup Management. In
27 th International Scientific and Professional Conference’’Organization and Maintenance
Technology.