As with all of the backup operations, backups can be performed while users are active in the database. This is accomplished by SQL Server providing a "snapshot" of the database. The first thing that is included in the backup is the small part of the transaction log that has not be written to disk yet to ensure consistency. This way the database is restored to the state it was in at the moment the backup started.

SQL Server then starts at the first page in the database an begins backing up the database. It will detect when a transaction will write to a data page. When these actions are detected, the backup process will skip ahead and backup the page or pages affected before resuming its normal course. This ensures that transactions can still be processed, but they won’t effect the integrity of the backup.

Backup Types

SQL Server enables three different types of backups which are appropriate for differing situations. These types are full, differential, and transaction.

Full backups do exactly what the name implies. They provide you with a complete copy of the database. During a full backup, enough of the transaction log is also captured to ensure a stable and consistent state when retoring the database. Full database backups are normally taken once per day or even once per week.

Transaction log backups provide a copy of the transaction log. These will normally be taken on amore frequent basis due to size and time constraints. This will allow you to restore a database to a more recent state that a full backup normally allows.

Transaction log backups will also remove the inactive portion of the transaction log once the backup operation is complete. This is the only way you should be managing the transaction logs in a production environment. If the truncate on checkpoint option is enabled in a database, transaction log backups are not possible because the inactive portion of the transaction log is removed during every checkpoint operation. The transaction log backups are sequential. This means that they must be applied in order in an unbroken chain. If you have taken 4 transaction log backups since the last full backup, in order to completely restore the database, you must restore the full database backup and then each of the transaction logs in order. If one of the transaction logs is corrupt or lost and can not be restored, you have lost any of the transactions in the remaining log backups.

The new type of backup allowed is a differential backup. This will capture the data that has been modified since the last full database backup. This enables a much more robust backup system that is capable to providing much more current database states than you would normally get in previous versions of SQL Server. This is an excellent method to use if you want too perform nonlogged transactions in yoru production databases such as select into and bulk copy. When these operations are performed, they invalidate the transaction log since all of the data effected during the operation is not written to the log. In previous versions of SQL Server, you would have to perform a full database backup before you could resume your transaction log backup schedule. This can be prohibitive in large databases or ones that process heavy volumes of transactions.

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32