The most common way to maintain duplicate copies of data is by using the backup capabilities built into SQL server, so at failure you have ability to recover from and get your database online again so let’s start review the different types of allowed backup in SQL Server 2005. ·

Full Backups

o Full back up is to capture all the data that is stored in the database.

o Always available, regardless of the recovery model you configure for a database.

o When you initiate a backup, the backup engine writes pages to the backup device without regard to the order of pages. Because the backup is not concerned with the precise ordering of pages, SQL server can open multiple threads to write data as fast as it can be accepted by the media

1. Lock the database, blocking all transactions.

2. Place a mark in the transaction log

3. Release the database lock

4. Back up all pages in the database

5. Lock the database, blocking all transactions

6. Place a mark in the transaction log

7. Release the database lock

8. Extract all transactions between the two log marks and append to the backup

o INIT parameter which is the most common, tells SQL server to overwrite anything in the backup device that might already exist before starting the backup operation.

· Differential Backups

o Captures all the extents that have changed since the last full backup.

o Reduce the number of transaction log backup that need to be restored.

o You can use differential backup along with a full backup. If a full backup does not exist, you can’t create differential backups.

o No matter what recovery model is specified for the database.

o An incremental backup captures any changes since the previous incremental backup. Therefore, restoring an incremental backup requires all other incremental backups. A differential backup always captures every extent that has changed since the last full backup. So each differential backup contains everything that any previous differential backup taken after a full backup contains.

Transaction Log Backups

o Perform transaction log backups only for databases you have configured to use the full or bulk-logged recovery model.

o Allowed only after a full backup has been performed.

o Contains only a subset of data and requires that you also have at least a full backup to recover the database. o It starts at the log sequence Number (LSN) at which the previous log backup completed. SQL server then backs up all subsequent transactions until the backup encounters an open transaction. After SQL server encounters an open transaction, the log backup completes. Any LSN that are backed up are then allowed to be removed from the transaction log, which enables the system to reuse log space. Note that a full backup is required before you can perform either a differential or a transaction log backup.