Table of Contents

Overview

In this chapter we will discuss backup and recovery procedures.

Recovery Modes

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:

Simple

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

Full

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

Bulk

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

Backup

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:

As for data content type there are two:

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

Performing a database backup