Overview

In this chapter we will discuss backup and recovery procedures.

1. Restore from Disk
2. Restore from Tape (Data Protector)

The backup in SQL server depends the recovery mode of the database. In this context we have couple modes in the database. Based on this, we are able to either done Online(HOT) backup or not. So let's see each of them:

No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server).

Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode:

-Log shipping

-Always On or Database mirroring

-Media recovery without data loss

-Point-in-time restores

Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error). For information about database backups under the full recovery model,

To perform a full backup use the following command:

BACKUP DATABASE [%{database_name}] 
TO DISK= N'LOCATION\NAME{database_name}.bak' 
WITH COMPRESSION

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For information about operations that can be minimally logged

Let's for simplicity assume that we are always in a FULL recovery mode. This will allows us to perform online backups via the log backup. So let's get going :) As any other database, we have the following concept:

• Online: Fully online. Selects and DML are possible.
• Warm: Kind of a online. Users are able to do selects, but no DML.
• Cold: Offline. The entire structure, which we are backing up, is down.

As for data content type there are two:

• Incremental: Backup only the changed data (Differential - Since the last backup, Cumulative - Since the last Full backup)
• Full: Backup up everything

And as a data access type (the way we access the data for the backup) there are again two:

• Physical: Backup of the physical data files.
• Logical: Backup of the data content, requires running database in order to be backed up AND restored. A lot of limitations regarding object related objects (views, foreign keys and others)

Performing a database backup