The following questions are intended to
There are no prerequisites for this chapter.
Data loss and data corruption are major concerns for any database administrator. SQL Server provides a sophisticated backup mechanism that makes it possible to minimize and even eliminate data loss and data corruption. This lesson introduces the SQL Server backup process and the types of backup plans that can be implemented. You should
After this lesson, you will be able to
- Describe the SQL Server online backup mechanism
- Determine the appropriate times to perform
Estimated lesson time: 45 minutes
The need to prevent data loss is one of the most critical issues that system administrators encounter. You can minimize data loss by having a backup strategy and by performing regular backups.
You must have a backup strategy to minimize data loss and recover lost data. You can lose data as a result of hardware or software failures or due to any of the following mishaps:
If you have an appropriate backup strategy, you can restore data with minimal cost to production time and minimize the chance of permanent data loss. Think of a backup strategy as an insurance policy. Your backup strategy should put your system back to where it was before a problem occurred. As with an insurance policy, ask yourself, "How much am I willing to pay, and how much loss is acceptable to me?"
The costs associated with a backup strategy include the amount of time spent designing, implementing, automating, and testing the backup procedure. Although you cannot prevent data loss completely, you should design your backup strategy to minimize the extent of the damage. When you plan your backup strategy, consider the acceptable amount of time that the system can be down, as well as the acceptable amount of data loss (if any) in case of a system failure.
How frequently you back up your database depends on the amount of data that you are willing to lose and the volume of database activity. When you back up
Backing up and restoring databases is useful for other purposes, such as moving or copying a database from one server to another. By backing up a database on one computer and restoring it to another, you can quickly and easily copy a database.
SQL Server allows you to perform a number of different types of backups. This section describes the types of backups you can perform and gives an overview of the backup process.
Previous versions of SQL Server used the terms dump for backup and load for restore. You will still see these terms in some documentation and
Transact-SQL statements—for example, sp_addumpdevice.
When you perform a complete backup of a database, SQL Server backs up
The portion of the transaction log that is
A complete database backup records all active data pages from the database. Unused pages are not backed up, so the backup will usually be smaller than the database. SQL Server records the specifications of the original database files. This type of backup is used to re-create all files of a database in their original locations, complete with objects and data, when you restore a database.
In a transaction log backup, only the transaction log is backed up. Transaction log backups record the transactions that have modified a database since the last complete database, differential database, or transaction log backup.
After the transaction log is backed up, the inactive portion of the transaction log is truncated (removed); this
Differential database backups record the data pages that have changed since the last complete database backup, making a differential backup smaller than a database backup. Differential backups allow you to make less-frequent database backups. They cannot be restored without a previous complete database backup. If you need to restore a database, you must restore both the most recent complete database backup and the most recent differential database backup.
To understand the differences among complete, transaction log, and differential backups, think of a manual in a three-ring binder. You can store a copy of the manual or changes to the manual in a number of ways:
File or filegroup backups are a specialized form of database backup in which only certain individual files or filegroups from a database are backed up. This is usually done when there is not enough time to perform a database backup. To make use of file and filegroup backups, transaction log backups must be created as well.
You can back up databases by executing Transact-SQL statements or by using SQL Server Enterprise Manager. When planning a backup strategy, assign someone the responsibility of performing the backups and checking that the backup process is completing correctly. Also consider where your backups will be stored.
Members of the following roles have permission to back up a database:
Additional roles can be created and granted permission to back up one or more databases.
SQL Server can back up to hard disk files, tapes, or named pipe devices. To determine which method of storing backups is right for you, consider the following:
If you want to back up to a network disk file, you must use the Transact-SQL BACKUP command. You cannot perform the backup using SQL Server Enterprise Manager.
When SQL Server backs up an online database (one that is actively being utilized by
You can back up a database while the database is online and active. However, a few operations, listed here, cannot take place during the backup process:
Automatic database growth cannot occur during a backup operation.
If you attempt to start a backup operation when one of these operations is in progress, the backup operation aborts. If a backup operation is already in progress and one of these operations is attempted, the operation fails and the backup operation continues.
If you do not perform regular transaction log backups, the transaction log will eventually become full; either it will grow to its MAXSIZE or the disk will run out of space, preventing further automatic growth. When this happens, SQL Server will prevent further database modifications until you clear the transaction log. If you do not plan to use transaction log backups as part of your backup strategy, do one of the following:
If you use the WITH TRUNCATE_ONLY option with the BACKUP LOG statement, you cannot restore from the backup, as the backup is not written to a backup device.
When you use this option, the transaction log is truncated automatically whenever a checkpoint occurs. The transaction log does not contain the changes that were made to the database since the last database backup.
If you set the trunc. log on chkpt. option to true, you cannot use transaction log backups as part of your backup strategy, since the log will not contain all of the transactions.
Your decision as to when and how often you back up your database depends on your particular business environment. There are also times when you may need to perform unscheduled backups. For instance, after loading data or performing database maintenance, you may need to back up a specific user database or the system databases.
System databases store important data about SQL Server and all user databases. Therefore, you should back up system databases regularly, as well as before performing actions that modify them.
The master database contains system information and high-level information about all databases on a SQL Server. If the master database becomes damaged, SQL Server may fail to start, and user databases may be unavailable. In this case, the master database has to be restored from a backup before user databases can be restored or referenced.
Without a current backup of the master database, you must completely rebuild all of the system databases with the Rebuild Master (
rebuildm) utility. This utility program rebuilds all system databases as a unit.
When you execute certain statements or system stored procedures, SQL Server modifies the master database. Therefore, back up the master database after using any of the following:
You should also back up the master database after using SQL Server Enterprise Manager to perform any of the operations just listed.
It is recommended that user objects not be created in the master database. Otherwise, it needs to be backed up more frequently. Additionally, user objects
competewith the system objects for space.
Transaction log backups and differential backups cannot be performed on the master database. The master database needs to be restored in a single operation, so only complete database backups of this database are allowed.
The msdb Database
Back up the msdb database after modifying information about jobs, alerts, and operators that are used by SQL Server Agent. If you do not have a current backup of the msdb database, you must rebuild all of the system databases if a system failure occurs and then re-create each job, alert, and operator.
It is recommended that user objects not be created in the msdb database. Otherwise, it needs to be backed up more frequently. Additionally, user objects compete with the system objects for space.
The model Database
Back up the model database if you modify it to include the default configuration for all new user databases. If the master or msdb databases are rebuilt, the model database is also rebuilt, and therefore changes are lost. You can restore a backup of your customized model database in case of a system failure.
User objects created in the model database are added to every new database. Therefore, you should not add user objects to the model database unless you intend for them to be created in every new database.
The tempdb Database
SQL Server does not allow the tempdb database to be backed up, as it contains only temporary data that will never need to be restored.
You should plan to back up user databases regularly. You should also perform a backup after a database or index is created and when certain nonlogged operations are executed.
After Creating Databases
Back up a database after it has been created or loaded with data. Without a complete database backup, you cannot restore transaction log or differential database backups, because you must have a baseline for these backups.
After Creating Indexes
The transaction log records only the fact that an index was created, not the actual data page modifications. Therefore, although you are not required to do so, you should perform a backup of the database after creating indexes on large tables.
Backing up a database after an index is created ensures that the database backup device contains the data and the index structures. This will save you time during the restore process if a database is lost.
If you back up only the transaction log after an index is created, SQL Server must rebuild the index when you restore that transaction log. For large tables, the amount of time required to do this may be longer than the time it takes to restore a database backup.
After Clearing the Transaction Log
You should perform a complete backup of a database after clearing the transaction log with the BACKUP LOG WITH TRUNCATE_ONLY statement. After this statement executes, the transaction log no longer contains a record of database activity and cannot be used to recover changes to the database.
After Performing Nonlogged Operations
Operations that are not recorded to the transaction log are called nonlogged operations; they are usually used to prevent the transaction log from filling
You cannot recover changes made by the following nonlogged operations:
You should make a backup of a database after performing a nonlogged operation because the transaction log has no record of the data that has been added to or modified in the database.
An appropriate backup strategy will allow you to both restore data with minimal cost to production time and minimize the chance of permanent data loss. SQL Server backups can be performed by using Transact-SQL or the SQL Server Enterprise Manager.
There are three types of backups; complete, differential, and transaction log backups. When deciding which to use and how often to use them, consider your business environment. At times you will need to perform unscheduled backups because of activities that modify the system databases or nonlogged operations.