Types of Backups

SQL Server 2000 supports four main types of backups:

• Full Database Backup

• Differential Database Backup

• File and Filegroup Backup

• Transaction Log Backup

Full Database Backup

Full Database Backup creates a consistent image of the database up to the point that the database backup finished. SQL Server 2000 does this by noting the Log Sequence Number (LSN) at the start of a backup. An LSN is a number assigned to each record written to the log, to keep track of changes. It then copies the extents that make up the database (an extent is a block of eight pages?for more information on extents and database storage structures, see Chapter 33, "SQL Server Internals"). As this is a dynamic backup it allows changes to the data while the backup is running. How then would you get a consistent image of the data? Simple. When the extents are all backed up, SQL Server again notes the LSN. It now also backs up the part of the log between the first LSN and the last LSN it recorded. It then appends this "log piece" to the backup. When the backup is restored, the extents are restored, and this log piece is applied, essentially playing back all changes that occurred during the backup. Of course the more changes there are during the backup, the larger this log piece will be. This will affect both backup and restore times, so it is still prudent to schedule backups for periods of low activity.

Differential Database Backup

Differential Database Backup was introduced in Version 7.0. A differential backup backs up all extents that have changed since the last full backup. As only changed extents are backed up, the differential backup is usually substantially smaller than a full backup. This is a major factor in allowing SQL Server to scale to Very Large Databases (VLDB). A full backup must be restored before a differential backup is restored, but only the last differential needs to be restored. Consider a scenario where a full backup is done Saturday night, and a differential is done each subsequent night. If the database fails Friday, you would restore the full backup, and the differential from Thursday night. Apply any log backups done after the differential backup and you're back in business.

File and Filegroup Backup

File and Filegroup Backup is another feature designed with the VLDB in mind. Databases can be backed up file-by-file, or alternatively, filegroup-by-filegroup. Therefore, a 500GB database consisting of five 100GB files could have file1 backed up Monday, file2 Tuesday, and so on. This allows backups to be done in a much smaller window. The restrictions on file and filegroup backups are that if tables and their indexes are stored on separate files or filegroups (this is sometimes done for performance reasons) these must be backed up and restored together, and you must be doing transaction log backups. In both full and differential restore scenarios, the full database had to be restored first. In a file restore, only the damaged file needs to be restored. This can be a huge time saver in a large database. Why restore the entire database when only one file is damaged? Of course the restored file will now be out of sync with the rest of the database. This is why you must have the log backups, including a backup of the log that was active at the time of failure. You restore the file, and then apply all transaction logs taken after the file backup. This brings that file into sync with the rest of the database.

Transaction Log Backup

Transaction Log Backup copies the transactions in the transaction log and then deletes all but the active portion of the log to free up space. As the transaction log is a serial record of all transactions since the last log backup, the log backups can be applied during the restore process to bring the database forward to the point of failure. When you are performing log backups, you can also restore from the log backups to a specific point in time. When a transaction log has been backed up, it is then truncated. This clears the inactive transactions from the log, allowing room for additional transactions. This truncation keeps the log from filling up, or from growing too large if the log is set to automatically grow the file.

Recovery Models

New to SQL Server 2000 are recovery models. You can select a recovery model for each database in SQL Server 2000 to determine how your data is backed up and what your exposure to data loss is. The three types of recovery models available are as follows:

• Full

• Bulk_logged

• Simple

Full Recovery

Full recovery provides the least risk of losing data due to media failure or user or application error. It also provides the most flexibility in restore operations. Full recovery is implemented with a combination of full database backups and transaction log backups. Optionally, differential and file level backups can be performed. Full recovery permits the restoration of a database to a specific point in time. To allow this, all operations are fully logged, including SELECT INTO, BULK INSERT, bcp, and CREATE INDEX. The downside to the full recovery model is that this additional logging can cause the transaction log to be much larger.

Bulk_logged Recovery

Bulk_logged recovery also allows for complete restoration of a database and provides improved performance for bulk operations while consuming less log space. In bulk_logged recovery, bulk operations are only minimally logged, providing better performance during the bulk operation. However, point in time recovery is not supported if a transaction log file contains bulk operations. The entire log must be recovered. As mentioned before, the lower logging level might result in less log space being consumed, but if a bulk operation has occurred since the last full or differential backup, the log backup can be quite large. This is due to the fact that in the log only creation of extents by bulk operations is recorded. When the log is backed up, the actual extents are appended to the log backup to ensure recoverability. The good news is that you can switch back and forth from full to bulk_logged mode. This means you can run in full recovery mode, and switch to bulk_logged to speed up bulk operations.

Simple Recovery

Simple recovery presents the most basic backup and recovery model. In simple recovery the transaction log is regularly truncated, and therefore not available for backup. When in simple recovery mode the backup plan is restricted to full database and differential backups. Recovery to the point of failure, and point in time recovery are not supported. As all data input after the last full or differential backup must be recovered manually, the simple recovery model is not suitable for most production databases. Exceptions are databases that have few updates, and databases where the data is bulk loaded on a scheduled basis. Many decision-support databases fall into this category. If a full backup can be done after each data load, or if the source data remains available, allowing the load to be repeated, simple recovery mode might suffice.