DISASTER

PREVENTION AND
RECOVERY

Dr. SALAH SALEM BINDAHMAN

Introduction
 Data always gets loss because of so many
reasons like equipment failure, user errors,
data corruption (intentionally or not).
 We have all suffered from data loss that
could be prevented if the data is backed
up properly.
 It is the responsibility of the DBA to plan
the back up strategy so database is back
to service in as short time as possible with
minimal data loss.
Database Recovery Models
 Database recovery models change the
behavior of the transaction log, what back
ups can be performed, and how data is
recovered.
 Each recovery model has advantages and
disadvantages and DBA should choose
the appropriate one for the database.
 There are three recovery models
Simple, Full, and Bulk-logged models.
Database Recovery Models
 Simple recovery model
 Transactions are deleted from the transaction
log as they are committed into the database.
 Transaction log file is never grow.
 Transaction log cannot be used to restore
data.
 Useful for development/ test servers.

ALTER DATABASE db_name SET RECOVERY SIMPLE

GO
Database Recovery Models
 Full recovery model
 All transactions are fully logged.
 All transactions are retained until a
transaction log back up occurs.
 Recommended for production database.
 Not the best choice for large data import.

ALTER DATABASE db_name SET RECOVERY FULL

GO
Database Recovery Models
 Bulk-logged recovery model
 Minimally logs most bulk operations like:
 Index creation
 Index rebuilt
 Bulk insert
 Select into
 Blob operation
 ..

ALTER DATABASE db_name SET RECOVERY BuLK-LOGGED

GO
Database Recovery Models
Database Backup
 Database backup is a copy if computer
data taken and stored elsewhere so that
it maybe used to restore the original
data after it has been lost.
 General backup command

BACKUP DATABASE database_name

TO DISK = ‘ disk_name ‘
[WITH OPTION = CHOICE]
Database Backup
Backup Device
 Backup device is the location where the
backup file is saved. It could be a disk or
server location.

BACKUP DATABASE database_name

TO DISK = ‘ D:\SQLBackup\file_name.BAK ‘
WITH DISCRIPTION = ‘Full backup’

BACKUP DATABASE database_name

TO DISK = \\Server_name\SQLBackup\file_name.BAK ‘
WITH DISCRIPTION = ‘Full backup’
Backup Types
 SQL server support several backup types
that can be combined or used separately
for backup strategy. These types are:
 Full Backup.
 Deferential Backup.
 File/FileGroup Backup.
 Deferential Backup for file/filegroup.
 Transaction log Backup.
 Partial Backup.
 Copy_only backup.
Full Backup
 Full back up is simply backs up all the
data in the database.
 It can be used in any recovery models.
 Simple, easy, and most common way to
implement backup.
 However, it takes longer time that other
methods and typically results in the
same unchanged data being backed up
over and over again.
Deferential Backup
 Deferential backup used to back up the only
the data that has been changed since the
last full backup.
 It takes less time to execute than full backup.
 It can be used in any recovery model and
requires a base full backup.
 If 1 byte of the extent (64-KB) is changed,
the entire extent is backed up.
BACKUP DATABASE database_name
TO DISK = ‘D:\SQLBackup\file_name.BAK‘
WITH DEFERENTIAL , DISCRIPTION = ‘Dif. backup’
File/FileGroup Backup
 When database is divided across many
files/filegroups, these files can be backed
up individually.
 It is useful for large database.
 It can be used only in full and bulk-
logged recovery model.
 It can be used in simple recovery model
only if the file is marked as read only
File/FileGroup Backup
 It can perform deferential backup where
only the change to the file or filegroup
since the last full backup is backed up.

BACKUP DATABASE database_name

FILE = ‘File_name’
TO DISK = ‘D:\SQLBackup\file_name.BAK‘
WITH DEFFERENTIAL ,DISCRIPTION = ‘File backup’
Transaction Log Backup
 In this backup type, the log file for the
transaction is backed up.
 The main advantage is to maintain the
size of the transaction log file to
reasonable limits and to allow for recovery
data with the least amount of data loss.
 It comes in three forms:
 Pure log backup.
 Bulk log backup.
 Tail log backup.
Transaction Log Backup
 Pure log backup
 It contains only transactions and completed in full
or bulk recovery model with no bulk operation.
 Bulk log backup
 It contains transactions and physical extent
modified.
 Tail log backup
 It captures all transaction log records affected
during the backup.
BACKUP LOG SmallWorks
TO DISK = ‘D:\SQLBackups\[Link]’
WITH NO_TRUNCATE ,DESCRIPTION = ‘SmallWorks Log Backup’
Partial Backup
 Partial backup consists of primary file
group, read write file groups, and read
only file group.
 Any file group could be backed up
separately saving time and space (those
not changed).

BACKUP DATABASE SmallWorks READ_WRITE_FILEGROUPS

TO DISK = ‘D:\SQLBackups\[Link]’
WITH DESCRIPTION = ‘Partial Backup of all Read/Write
filegroups’
Copy only Backup
 It is used to create a backup without
affecting the chain of backups required to
restore a database.
 It can be used if copy of database is
needed for test and development.

BACKUP DATABASE SmallWorks

TO DISK = ‘D:\SQLData\[Link]’
WITH COPY_ONLY, DESCRIPTION = ‘Copy only backup’
Database Backup
Backup Options
 Backup stripe
 It is used if we want to save the backups across
multiple devices.
 It saves time to process the backup as multiple
physical devices being written to simultaneously.
 Loosing one file results in loosing the backup.

BACKUP DATABASE SmallWorks

TO DISK=’D:\StripedBackupsA\[Link]’
, DISK=’E:\StripedBackupsB\[Link]’
, DISK=’F:\StripedBackupsC\[Link]’
WITH DESCRIPTION = ‘Striped Backup’
Backup Options
 Mirror Backup
 It means backing the database up and then
backing up the backups.

BACKUP DATABASE SmallWorks

TO DISK=’D:\MirroredBackupsA\[Link]’
MIRROR TO DISK = ’E:\MirroredBackupsB\
[Link]’
WITH FORMAT, DESCRIPTION = ‘Mirrored Backup’
Backup Options
 Compressed Backup
 Compressed backup is faster than non-
compressed backup and also can be restored
faster.
 It consumes CPU resources.

BACKUP DATABASE SmallWorks

TO DISK=’D:\SQLBackups\[Link]’
WITH COMPRESSION, DESCRIPTION = ‘Compressed Backup’
With Options
 More information about with option in the
book page number 373.
 With password = string
 With expiredate = datetime
 With stop_on_error/continue_after_error
 With norecovery