Backup schemes should be designed with recovery in mind.

SQL Server 2008 provides for four different types of backup to construct your Backup Scheme:

1. Full
2. Differential
3. Transaction Log
4. Filegroup

Full Backup

A Full Backup occurs in the following sequence:

1. Database is locked
2. Mark is placed in the transaction log
3. Database lock released
4. All pages from data files written to backup device
5. Database is locked again
6. New mark placed in the transaction log
7. Database lock released
8. Extract portion of log between the two marks from 3 and 5, and append them to the backup

The simplest backup command is:

BACKUP DATABASE database_name TO DISK = 'filename'

The TO clause can alternatively be used to specify TAPE or a logical device. With no directory specified in the filename specifier, the file will be created in the default directory defined for the instance. By specifying multiple devices, the backup will be striped across the devices. Striping does not provide redundancy or fault tolerance, but can improve the speed of the backup. All members of the stripe set will be required to restore the backup.

The MIRROR TO clause allows you to create up to four copies of a backup in a single operation:

BACKUP DATABASE Robots
TO DISK = 'c:\data\Robots_1.bak', DISK = 'd:\data\Robots_2.bak'
MIRROR TO DISK = 'e:\Robots_1_copy.bak', DISK = 'e:\Robots_2_copy.bak'
WITH FORMAT

Each mirror device specified must be the same type as the primary backup device, and must contain the same number of devices.

COMPRESSION options means the less data needs to be written to disk for the backup operation, but involves a higher processing overhead

The INIT/NOINIT option determines whether the backup appends to an existing file (NOINIT) or overwrites the file (INIT). However, it is safer to store each full backup in seperate files, preferably with a datatime value as part of the filename

The CHECKSUM option forces SQL Server to verify each page checksum before writing the page to the backup. Additionally a checksum will be generated for the entire backup, which can be used during a restore to verify that the backup has not become corrupted

STOP_ON_ERROR is the default setting for a BACKUP DATABASE command, and will cause the backup to halt if an error is encountered. CONTINUE_PAST_ERROR will keep the backup running even when errors are encountered allowing as many pages as possible to written to the backup.

BACKUP DATABASE Robots
TO DISK = 'Robots_1.bak'
MIRROR TO DISK = 'Robots_1.bak'
WITH COMPRESSION, INIT, FORMAT,
  CHECKSUM, STOP_ON_ERROR

Transaction Log Backup

Every change made to a database has an entry made in the transaction log. Each entry is assigned a Log Sequence Number (LSN). LSNs are integer values starting at one and incremented by one for each new entry. LSNs are never reused on a single database. The Transaction Log consists of entries that are either active (not yet committed) or inactive (committed).

A Transaction Log backup starts at the lowest LSN in the log and writes each succesive transaction to the backup until the first active transaction is encountered. The portion of the transaction log that has been backed up is cleared, leaving just the active entries in the log. Subsequent transaction log backups begin where the previous transaction log backup left of - at the first LSN in the transaction log. Transaction log backups thus produce an incremental backup - to restore a database, a full backup will need to be restored followed by each transaction log backup in turn.

Any gaps in the LSN sequence for a set of transaction log backups, will prevent the backups being restored beyond that point. If an LSN gap occurs, then a full backup will need to be performed, before transaction log backups can be resumed.

Transaction log backups are created as follows:

BACKUP LOG Robots
TO DISK = 'Robots_1.trn'
WITH COMPRESSION, INIT, CHECKSUM
  STOP_ON_ERROR

Differential Backups

SQL Server tracks each extent that has changed since the last full backup using a special page in the header of the database called the Differential Change Map (DCM). A differential backup will always start at the DCM, which does not change until the next full backup is performed. To restore a database from differential backups just requires a full backup and just one diffential backup

Differential backups are created as follows:

BACKUP DATABASE Robots
TO DISK = 'Robots_1.dif'
MIRROR TO DISK = 'Robots_1.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM
  STOP_ON_ERROR

Filegroup Backups

Filegroup backups allow you to backup specific files or filegroups. Because SQL Server will spread data across all the files in a filegroup, it is better to backup by filegroups rather than files, to get a consistent restore.

Page Verification

Hardware failures can lead to corrupt pages in the database due to incomplete writes. Corrupt pages can result in the whole database going off-line. With SQL Server 2008, corrupt pages can be quarantined by enabling page checksum verification:

ALTER DATABASE Robots
SET PAGE_VERIFY CHECKSUM

With page checksum verification, corrupt pages get logged in the msdb database. With mirrored databases, corrupted pages can be retrieved and restored automatically from the mirror

Recovery Models

The Recovery Model of a SQL Server database, controls the level of transaction logging that occurs. There are three recovery models:

Full

All changes made are logged to the transaction log, allowing the database to be recovered to any particular point in time.

Bulk Logged

Implements minimal logging for certain bulk operations: BCP, BULK INSERT, SELECT...INTO, CREATE INDEX, ALTER INDEX...REBUILD. For these operations, the extents are logged to the transaction log, rather than the individual rows changed. This recovery model thus saves considerable space in the transaction logs, but loses the ability to recover to any single point in time

Simple

All transactions are logged, but inactive transactions are discarded at regular intervals by checkpoint processes. With the simple recovery model, transaction log backups are not possible

To alter the recovery model use:

ALTER DATABASE Robots
SET RECOVER [ FULL | BULK LOGGED | SIMPLE ]

Database Restores

Backups are restored with the RESTORE DATABASE command. The database is taken off-line during the restore process. Specifying the RECOVERY option will bring the database back on-line immeadiately after the RESTORE DATABASE command completes. The NORECOVERY option leaves the database off-line, allowing incremental or differential backups to be applied before users can access the database. The STANDBY option allows SELECT statements to be issued against the database during the restore.

When a RESTORE command is issued, SQL Server will create the database, and then re-create each file in the database sized to match their size at the time the backup was made. Once the files are created, SQL Server will start restoring each page from the backup. Because re-creating each file in the database can take considerable time, it is often quicker to restore over an existing copy of the database.

The first step in any recovery process is to take a final transaction log backup - a process commonly referred to as 'backing up the tail of the log'. Prior to executing a restore, you will also need to ensure that all users are disconnected from the database

Backup Tail of Log

BACKUP LOG Robots
TO DISK = 'Robots_Tail.trn'
WITH COMPRESSION, INIT, NO_TRUNCATE

Restore Full Backup

RESTORE DATABASE Robots
FROM DISK = 'Robots_1.bak'
WITH STANDBY = 'C:\PATH\TO\STANDBY\FILE.stn'

Restore Differential

RESTORE DATABASE Robots
FROM DISK = 'Robots_2.dif'
WITH STANDBY = 'C:\PATH\TO\STANDBY\FILE.stn'

Restore Transaction Log

RESTORE LOG 
FROM DISK = 'Robots_1.trn'
WITH STANDBY = 'C:\PATH\TO\FILE.stn'

Recovering from transaction logs allows you to specify the point in time that you wish to recover to using one of:

1. STOPAT = 'datetime'

2. STOPATMARK = ['mark_name' | lsn = 'lsn_number']

3. STOPBEFOREMARK = ['mark_name' | lsn = 'lsn_number']

Recover Database

--bring database on-line after a restore
RESTORE DATABASE 
WITH RECOVERY