Topics | How To | Related Topics
Plan your backup jobs for this agent by reviewing the following information:
The Microsoft SQL Server iDataAgent supports the following backup types:
This agent has the following unique functionality. The Microsoft SQL Server iDataAgent backs up very specific sets of data depending upon what type of operation you choose. Review the following information to understand exactly what will be backed up when you perform a backup job at the indicated level.
Through the creation of subclients, you have a flexible way to manage how databases, data files, and file groups get backed up by the Microsoft SQL Server iDataAgent. Subclient backups support the following:
The job that is performed when you perform a full, transaction log, or differential backup for a subclient depends on the type of subclient you are backing up:
When a full backup job is performed for the default subclient, the system performs a discover on the instance and executes a full backup of:
When a transaction log or differential backup job is performed for the default subclient, the system performs a discover of the instance, and executes:
When a full backup job is performed for a user-defined subclient, the system executes a full backup of:
When a transaction log or differential backup job is performed for a user-defined subclient:
Through the creation of instances, you have a flexible way to manage how databases, data files, and file groups get backed up by the Microsoft SQL Server iDataAgent. Instance backups support the following:
When a full backup job is performed for an instance, the following actions take place:
When a transaction log or differential backup job is performed for an instance, the following actions take place:
| When a backup is performed at the instance level, File/File Group subclients are not backed up. |
See Instances - Microsoft SQL Server for more information.
Through the creation of additional subclients, the SQL Server iDataAgent allows individual files or file groups to be backed up. For especially large databases, this functionality can be critically important.
Whereas a Full database backup captures all files of a given database, file and file group backups allow you to back up selected portions of a database individually. As with database backups, the system provides the option of performing full, differential, and transaction log backups of file and file groups. Note that file and file group backups only support full and differential, and when running a transaction log backup for a File/File Group subclient, the database log is backed up.
A file and a file group backup are technically the same. If the subclient content is defined as a file group, the backup command issued will be to back up the group. All files defined in the group are backed up. If the subclient content is defined as one or more files, the backup command issued will be to back up only the files listed in the contents. SQL Server permits only one file or file group backup to run at a time.
If you choose to use file and file group backups, keep in mind the requirement that you must also run transaction log backups in order to restore files and file groups. You have the option of running a transaction log backup automatically upon successful completion of a Full or Differential backup. See Start Log Backup after Successful Backup for more information.
While backups can proceed while a database is online and being accessed, there are a few jobs that SQL Server restricts during a backup. If one of these jobs is initiated while a backup is already in progress, the job terminates. If a backup is initiated while one of these jobs is in progress, the backup terminates. These jobs are:
When using SQL on Windows Server 2003 or later, you can enable VSS for backup jobs, see VSS for SQL Server iDataAgents for more information.
A transaction log backup is a backup of the transaction log of the database. This backup captures the transaction log which contains a record of all transactions whether the transaction was committed or not. Transaction log backups are consistent to the start time of the backup.
The use of transaction log backups make point in time recovery possible. You can restore to any point in time within the transaction log. If you use only full and differential backups, you will be able to restore to the time of the backup, but not to a point in time between backups.
A transaction log backup is similar to a traditional incremental backup you might perform on a file system because the transaction log backup contains only the new changes since the full or another transaction log backup.
Each time a transaction log is backed up it is truncated to the exact time of the backup. No checkpoint is issued at this time, therefore dirty pages are not written to disk before or after a transaction log backup. If there are dirty pages, any completed transactions will need to be rolled forward if a transaction log restore is performed. Any transactions that are not completed at the time a transaction log backup is performed are rolled back during a restore involving a transaction log backup.
In the scenario of a database failure where it is unacceptable to lose any data, you will want to restore to the point of failure. To do so means you will need to capture all transaction log events that occurred after the last backup job was run.
Tail-log Backup (database is left in restoring state)
This advanced backup option is only available if the Transaction Log option was selected in the Backup Option dialog box. Specifies to back up the tail of the log and to leave the database in the RESTORING state.
By configuring the nLogThreshHoldValue registry key, a minor event will be generated to remind users to run a full backup after the specified number of transaction log backups have run. By default, 24 log backups are run and then a full backup is taken. Specify a number with the registry key if you want to run a different number of log backups other than the default. The event can be viewed with the Event Viewer. This is useful since log backups are typically scheduled, but scheduling full backups may be inadvertently missed. Full backups also reduce the chance of data loss if log backups become corrupted.
You can configure the SQL Server iDataAgent to run log backups only without having to run full backups in the software. To do this:
Full and log backup operations can run simultaneously for the same subclient. However, a full backup needs to exist prior to running them together. After a full has been run, subsequent parallel backups will work since the risk of breaking the chain will be lessened. Note that running full and differential backups at the same time is not supported.
| When full and log backup operations are run simultaneously, the conversion rules will not be honored. See Backup Conversion Rules for more information. |
Before performing any backup procedures for this agent, review the following information:
When File/File Group subclient jobs are scheduled and the instance property Use VSS is selected, backups for File and File Group subclients run successfully as scheduled.
Multiple streams are supported for data protection and recovery operations with this agent; however, before utilizing multiple streams, you should understand how their use affects operations. For more information, see Streams.
To successfully backup a SharePoint Database in a Windows SharePoint Services (WSS) environment, where the instance name of the Windows Internal Database resembles something like MachineName\Microsoft##SSEE, you need to:
The default time allocated for backup and restore operations of SQL databases is 0 (infinite). If a backup or restore operation fails due to a timeout being reached, you can configure the nSqlQueryTimeout registry key to increase the amount of allocated time for backup or restore operations.
Example:
|