- Shopping Bag ( 0 items )
The target audience of this title is SQL Server administrators who install,configure, and support SQL server in an enterprise network. Business owners, contractors, and database administrators will also find all...
Ships from: Chatham, NJ
Usually ships in 1-2 business days
Ships from: acton, MA
Usually ships in 1-2 business days
The target audience of this title is SQL Server administrators who install,configure, and support SQL server in an enterprise network. Business owners, contractors, and database administrators will also find all they need to know about Microsoft SQL Server.
After this lesson, you will be able to
Estimated lesson time: 45 minutes
- Describe the SQL Server online backup mechanism
- Determine the appropriate times to perform backups
Have a Backup Strategy
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 followingmishaps:
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.
Back Up Regularly
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 user databases, consider the following facts and guidelines:
Note 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.
Complete Database Backups
When you perform a complete backup of a database, SQL Server backs up
The portion of the transaction log that is backed up contains database activities occurring since the start of the backup process.
Transaction Log Backups
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 frees up space in the transaction log so that it does not become full. Transaction log backups cannot be used if nonlogged transactions are performed on the database (that is, if the trunc. log on chkpt. database option is true). Transaction log backups cannot be restored without a previous database backup.
Differential Database Backups
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.
Understanding Complete, Transaction Log, and Differential Backups
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.
Who Performs Backups
Members of the following roles have permission to back up a database:
Where to Store Backups
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:
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:
Note 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 the Transaction Log Becomes Full
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:
Backing Up 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.
Note 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:
Tip 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 compete with the system objects for space.
Note 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.
Tip 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.
Note 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.
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 rapidly and to enhance performance during large operations.
You cannot recover changes made by the following nonlogged operations:
There are three types of backups: complete, differential, and transaction log. 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. ...