Lesson 2: Understanding the Types of Database Backups
3 4
To develop a data restoration plan, you need to understand each of the SQL Server 2000 backup types that are available. In this lesson, you will learn about each database backup type, including what is backed up with each, how they are different, and when to use each type. In Lesson 3 you will learn how to use these different backup types together for various types of data restorations. Once you understand both components, you can develop your data restoration plan.
After this lesson, you will be able to
- Describe the differences between each of the SQL Server 2000 backup types
- Understand when to use each type of backup
Estimated lesson time: 15 minutes
Understanding Full Database Backups
A full database backup is a copy of all data files in a database, including all database activity that occurred while the full database backup was in process. All user data and all database objects, including system tables, indexes, and user-defined tables, are included. A full database backup generally takes more space and more time than any other type of backup. A full database backup is the starting point for a complete database restoration in the event data restoration is required.
You should perform a full database backup after you populate your database with data for the first time. Thereafter, you should perform additional full database backups on a regular basis and after a major population of new data. How frequently you perform a full database backup depends upon the size of your data and how frequently it changes. As a general rule, perform full database backups as frequently as once a day and as infrequently as once a week. If your database is too large to perform a full database backup regularly, you must use file and filegroup backups.
Although SQL Server 2000 backups generally have little impact on database performance, it is still a good idea to schedule full database backups at a time when the database is least busy (such as overnight). However, you must coordinate the timing of a full database backup with scheduled bulk inserts of new data (if any), which are also frequently scheduled to occur overnight.
Understanding Differential Database Backups
A differential database backup is a copy of all changes that have occurred to all data files since the last full database backup, including all database activity that occurred while the differential database backup was in process. This includes all changes to data and database objects. A differential database backup records only the most recent change to a data record if a particular data record has changed more than once since the last full database backup (unlike a transaction log backup, which records each change). A differential database backup takes less time and less space than a full database backup, and is used to reduce database restoration times.
To enhance the speed of differential database backups, SQL Server 2000 tracks all extents that have changed since the last full database backup using a Differential Changed Map (DCM) page. The differential database backup process scans each DCM page to identify (and then back up) all changed extents (each full database backup resets the DCM pages). If the bit for an extent is 0, the extent has not changed since the last full database backup. If the bit is 1, the extent has changed. Through the use of DCM pages, the length of time required to perform a differential backup is proportional to the number of extents modified, not the size of the database.
Note
Because each differential backup records all changes since the last full database backup, only the most recent differential backup is required for restoration of data.
Use differential database backups with medium to large databases in between scheduled full database backups. As the length of time required to perform a full database backup increases, performing differential database backups between each full database backup becomes more useful. Using a recent differential database backup reduces the number of transaction log backups that must be used for a data restoration. Therefore, differential database backups are particularly useful in speeding up data restoration times in medium and large databases where a subset of data changes frequently and results in large transaction log sizes.
Understanding File and Filegroup Backups
A file backup is a copy of a single data file, and a filegroup backup is a copy of each data file in a single filegroup, including all database activity that occurred while the file or filegroup backup was in process. This type of backup takes less time and space than a full database backup. It is used for VLDBs when there is not enough time to back up the entire database in a reasonable amount of time (such as in a 24-hour period). In a VLDB, you can design the database so that certain filegroups contain data that changes frequently and other filegroups contain data that changes infrequently (or perhaps is read-only data). Using this design, you can use a file or filegroup backup to perform frequent backups of the data that changes frequently and perform occasional backups of the infrequently changing data. By splitting the backup into segments, you can perform the necessary backups in the available backup window and achieve acceptable restoration times. With VLDBs, a single file or filegroup can be restored much faster than an entire database.
File and filegroup backups require careful planning so that related data and indexes are backed up (and restored) together. In addition, a full set of transaction log backups is required to restore file and file group backups to a state that is logically consistent with the rest of the database. Finally, you can perform file and filegroup backups in parallel to multiple physical devices to significantly increase backup performance. However, because of the administrative complexity (including the need for sophisticated database design), file and filegroup backups are generally used only for VLDBs.
Understanding Differential File and Differential Filegroup Backups
A differential file backup or a differential filegroup backup is a copy of all changes that have occurred to a file or a filegroup since the last file or filegroup backup, including all database activity that occurred while the differential file or filegroup backup was in process. Differential file and differential filegroup backups are conceptually identical to differential database backups. They take less time and less space than making a complete copy of a file or filegroup, and are used to speed the restore process by reducing the number of transaction log backups that must be applied.
Understanding Transaction Log Backups
A transaction log backup is a sequential record of all transactions recorded in the transaction log since the last transaction log backup. Transaction log backups enable you to recover the database to a specific point in time, such as prior to entering incorrect data. Transaction log backups are only used with the Bulk-Logged Recovery and Full Recovery models. The Simple Recovery model does not use transaction log backups for database restoration and recovery.
When the Bulk-Logged Recovery model is used for bulk-logged operations, changes made by these bulk operations to data files are tracked using a Bulk Changed Map (BCM) page. A transaction log backup scans each BCM page to identify and back up all extents modified by bulk-logged operations since the most recent transaction log backup. This allows bulk-logged operations to be quickly backed up along with the transaction log when bulk-logged recovery is used. However, only the net change of the bulk operation is recorded, not each individual operation. BCM pages are not required when the Full Recovery model is used, because with this recovery model the bulk-logged operation is fully logged in the transaction log.
The length of time required to back up the transaction log will vary significantly depending upon the rate of database transactions, the recovery model used, and the volume of bulk-logged operations. On databases with very high transaction rates and fully logged bulk operations, the size of a transaction log backup can be bigger than a full database backup and require very frequent transaction log backups to regularly truncate the inactive portion of the transaction log.
Note
Because a transaction log backup records only changes since the previous transaction log backup, all transaction log backups are required for restoration of data.
When SQL Server 2000 completes a transaction log backup (unless specified otherwise), it truncates each virtual log file (VLF) that does not contain an active portion of the transaction log. This allows these VLFs to be reused. The active portion of the transaction log includes any portion of the transaction log containing an active transaction or a transaction marked for replication that has not yet replicated. In a production database, you will always be using either the Bulk-Logged Recovery or Full Recovery model and must perform regular transaction log backups to truncate the transaction log. If the transaction log is not regularly truncated, it can fill up. If the transaction log runs out of space, SQL Server 2000 will shut down. You should truncate the transaction log file through regular transaction log backups rather than manually truncating the transaction log file, because truncating it manually breaks the log backup chain. The only time you will back up the transaction log without truncation is when a data file fails and the current active transaction log must be backed up. In this scenario, it cannot be truncated, because the data file is damaged or nonexistent.
How often you need to perform transaction log backups depends upon the rate of transactions, the size of the transaction log file, the type of fault tolerance, and the acceptable data restoration times. It could be as frequent as every 10 or 15 minutes, or it could be only once every two or three hours (or longer if few transactions are occurring). Remember, if a data file disk and a transaction log file disk both fail and no fault tolerance is employed, any data more recent than the most recent transaction log backup must be regenerated using other means, which might not be possible.
Lesson Summary
After you populate a database and before you place it in production, you should make a full database backup. Thereafter, you should perform a full database backup on a regular basis. In addition, you must perform regular transaction log backups to have a record of all changes to the database and to truncate the transaction log so that it can store new transaction log records. In larger databases, use differential database backups between regularly scheduled full database backups to reduce the number of transaction log backups (and the time) that you must use to restore a database. In VLDBs, you must use file and filegroup backups, differential file and differential filegroup backups, and transaction log backups to have an effective data restoration strategy.
EAN: N/A
Pages: 126