5. Database Backups & Restoration.

Topics In This Module.
Database Backups
Why we need backups
Types of Backup
Full Backup
Differential Backup
Transaction Log Backup
Copy-only, Mirrored, Split and Tail log Backups
Differences between backups
Backup Strategy
Understanding how the data moving from Log to Data File, CHECKPOINT
Monitoring the space usage of Log File and fixing
Checking the backup files VALID or CORRUPTED
Backup storage tables
Important Queries
Performing Restorations
Types of Restoration
Attach and Detach
Shrinking files

============================================================================

Backups

Why backups are required?
If any hardware or software failures occurs we can recover data or databases from backups from a copy of SQL server data.

Modes of Backup

There are two modes in which backup can be taken they are:

Hot/Online Backup.
Cold/Offline Backup.

Hot/Online Backups:

In this mode when backup is taken databases will be operational to applications.
This is a time-consuming backup.
Default backups are online.
This mode is recommended for small databases.

Cold/Offline Backups:

In this mode when backup is taken the databases will not be operational to application, simple we take the database to offline and then trigger the backup.
This is faster backup compared to Online backups because databases are not operational.
This mode is recommended for huge databases.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Backup Media

Simply said backup media is a storage device, where we are taking/saving the backup, there are two type of backup media/storage devices they are:

Disk (Hard disk/shared disk)
Tapes.

Advantages on storing the data/backup on tapes and disk they are:

Property	Tapes	Disks
Backup Process	Slow	Faster
Storage type	Permanent	Temporary
Cost	Cheaper	Expensive
Chances of failure	No	Yes

--------------------------------------------------------------------------------------------------------------------------------------------------------

Backup File

Physical location and Physical file name of a backup on media is backup file.

Extensions: the backup file extension are

.bak will be for full and differential backup and
.trn will be for transactional log backup.

Example:

A full file location of a backup file is C:\Users\charan\Desktop\LEARNING\MSSQL\NOTES\adventureworks.bak

C: is the backup media.
\Users\charan\Desktop\LEARNING\ adventureworks: backup file
.bak: is the extension.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Backup Strategy

This is how backups are taken in our client environment,

Friday evening (6pm) full backup.
Monday – Thursday(6pm) differential backup.
Log backup every one hour – 1hr
Every day at 6pm system database backup

--------------------------------------------------------------------------------------------------------------------------------------------------------

Backup Set

If we take multiple backups into a single backup file that is called a backup set,

In these types of backups, we need to mention one of these options for a backup to get appended to already existing file or to overwrite it, below are the options they are:

INIT – we use this option with backup command for overwriting the file.
NOINIT – we use this option with backup command for appending the data.

Recommended as

For Differential and Transactional Log Backups Recommended to use as append option which is a default option, without mentioning NOINIT it directly takes as append option.

For Full backups Recommended to use as overwrite option, we need to mention INIT option with backup command.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Backup Devices

Logical name which points to a physical path and backup file on a backup media.

Syntax:

sp_adddumpdevice @devtype=’tape’, @name=’temp’, @Path=’c: as\as\’

--------------------------------------------------------------------------------------------------------------------------------------------------------

Network Backup

Taking backup to any another server which goes through network path to other servers.

All backup related information will be present on MSDB database

--------------------------------------------------------------------------------------------------------------------------------------------------------

Types of backup

There are different types of backups they are:

Full backup
Differential backup
Transactional Log backup
File/file groups backup.
Partial file/filegroup backups
Copy-Only backup
Mirror backup
Spilt/Stripped backup.

------------------------------------------

Full back up

This backup will back up the data from data file and as well as log file of a database, the backup is covered from starting of database creation to the point where backup is triggered.

Complete data will be available till point in this backup.
Backup file takes more space and backup is time taken.
Recommended to overwrite, when a backup is taken to backup sets instead of appending.
Every database needs to have a full back up without this, one cannot take any other backups (differential or tlog or tail log)

Syntax:

BACKUP DATABASE <db_name> TO DISK = ‘path\filename’ WITH INIT or NOINIT

INIT – for overwriting backup file its used.
NOINIT – for Appending the backup file, need not to be mentioned as its by default if its not mention it will be appended.

------------------------------------------

Differential backup:

This backup will back up the data form data file and as well as log file of a database, this backup covers data that is changed for last full back up to the point where backup is triggered.

Data that’s is changed from last full backup to till point is present.
Backup file takes less space compared to full backup and time taken is also less compared to others.
Recommended to Append, when a backup is taken to backup sets instead of appending.
This backup can be taken only when a database full backup is taken.
This is a cumulative backup.

Syntax:

BACKUP DATABASE <db_name> TO DISK = ‘path\filename’ WITH DIFFERENTAIL

------------------------------------------

File and File groups backup:

To take specific filegroups backups into a backup media we use this.

Syntax:

BACKUP DATABASE <db_name> FILEGROUP = ‘logical name’ TO DISK = ‘path\’

------------------------------------------

Transactional Backup

This backup will backup only log files transactions changes made from last backup may be (Differential, log, or full)

This is a type of incremental backup.

There are two types of transactional backup they are:

Ordinary transactional log backup
Tail log backup

To clear the space is in log file we normally use transactional log backup, when this backup is taken all the inactive transaction are truncated from virtual logs and all active transactions are kept in same.

When transactions are truncated the space in log files get free up,

The main difference between tlog and tail log backup Is, in tail log backup the transactions are not truncated, this is done when a database is corrupted.

For Point in Time Recovery we use this transactional log backups.

To reduce the data loss, we use full recovery mode and tlog backups.

Before starting the restoring process when database is corrupted we need to take a tail log backup, this can be performed even db/data file is corrupted.

Recommended option is appending for this backup when taken to backup set.

Syntax:

BACKUP LOG <db_name> TO DISK = ‘path\filename’;

BACKUP LOG <db_name> TO DISK = ‘path\filename’ WITH NO_TRUNCATE;

Options:

Truncate_only -> This is default, if it's not mentions it’s taken this, which is ordinary tlog backup

No_Truncate -> when this option is selected it is a tail_log backup.

------------------------------------------

Split/Stripped backup

To Spilt a backup file into two or more (four or SIX or Eight) files into different disks, This is used when disk space is less or low.

Example:

A backup file is of 80Gb, but Disk1 has 60Gb and Disk2 has 40Gb free, then this backup file is split into two files as 40Gb one file and another file as 40Gb.

Syntax:

BACKUP DATABASE <Db Name> TO DISK = “path1”, DISK = “path2”, DISK = “path3”;

Just replace backup with Restore and to with from so that we can restore them in the same way.

Advantage: This backup operation is fast, and Disk utilization properly can be done.

Dis-Advantage: But to restore the database completely we need all the backup files which have been taken.

All these backups can be protected or encrypted with a password the usage syntax is below.

BACKUP/RESTORE DATABASE <Db> TO/FROM DISK = “path” with password = “xxx”

------------------------------------------

Copy-Only Backup

This backup is taken without Disturbing the existing backup policy, this backup is applied only for full and Tlog backups

Syntax:

BACKUP DATABASE <Db> TO DISK = “Path” WITH COPY-ONLY;

------------------------------------------

Mirror Backup

If We want to take a backup as multiple copies at a time then we use this type of backup, the main advantage of this backup is if one is corrupted another will be there to restore in emergency.

Syntax:

BACKUP DATABASE <Db> TO DISK = “path” MIRROR, TO DISK = “path” MIRROR WITH FORMAT;

------------------------------------------

Partial file/filegroup Backup

Not Complete backup is taken only read_write file groups are only backed up and all read_only file groups are ignored.

Syntax:

BACKUP DATABASE <Db> READ_WRITE_FILEGROUPS TO DISK = “path”;

--------------------------------------------------------------------------------------------------------------------------------------------------------

Compression option is not present in OLD versions of SQL server, but in present version compression of a backup file is a default option.

Note: All the backups related information is stored in MSDB database.

==================================================================================

Restore

There are four types of restore they are:

Current state restoring.
Point in Time Restoring
Page Restoring.
Pease mill Restoring.

Some options used while restoring:

RESTORE FILELISTONLY: List the files present in the backup file
RESTORE VERIFYONLY: Will verify weather the file is corrupted or valid.
RESTORE HEADERONLY: This will say the version of the backup file, from which SQL server version the backup is taken

Syntax:

RESTORE <Above Options> FROM DISK = “path”

-------------------------------------------------------------------------------------------------------------------------------------------------------

Current State Restoring

This type of restoring is used when a database is corrupted or cannot bring database online/recovery mode, restoring the database up to its last minute so its called current stage restoring

To restore follow the below steps:

If the database is accessible by users then bring it to single user mode
Gather all the available backup file and verify them for any corruption or encryption.
Perform tail_log backup as a before step of restoring.

This backup log backups without truncating them,
It will also change the databases to restoring mode, with no_recovery option,
Even the database is damaged or corrupted this will run, as it runs of transactional logs.

Syntax:

BACKUP LOG <Db> TO DISK = “Path”, WITH NO_TRUNCATE, NO_RECOVERY;

Preform the restoration (apply a full, latest differential, and all tlogs till the point and the tail log backup which is taken just before.)
Bring the database to recovery mode at the end.
Bring the database to Multi_user mode.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Point in Time Recovering

This is an Adhoc restoration, the process of restoring the database up to specific date and time is PITR, this is performed on demand, use to restore unexpected /unwanted changes applied.

PITR requires the log backup as mandatory to perform the restore.

we do have the data loss in this type of restoring, as the database will go to the point at the past the data from that point will be lost.

According to the backup strategy we can do this restore and rest of the steps are same.

To restore follow the below steps:

Set the database to single user mode.
Gather the backup and verify the backups.
Tail log is not used here usually but we take it for safer side, so that the database can also be taken to restoring state.
According to the backup strategy apply the restoring. Apply the full backup, and if any differential applies it and then all log backups and at the last log use STOP at option.

Syntax:

RESTORE LOG <Db> FROM DISK = “Path”, WITH RECOVERY, STOP AT = “DATE AND TIME”;

-------------------------------

For Page restoring we use the below

RESTORE DATABASE <Db> PAGE = “File number: Page number” FROM DISK = “Path”;

Microsoft SQL Server Database Administration.