Backing up SQL transaction logs

Backup Exec includes two methods for backing up transaction logs: Log and Log No Truncate.

When running log backups, it is recommended that you use Backup Exec exclusively to perform log truncations if you decide to truncate the logs. After a transaction log has been truncated by something other than a log backup, you must run a full or differential backup before you run another log backup.

Use the Log No Truncate method only when the database is corrupted or database files are missing. This method backs up transactions that you may not be able to access otherwise when the database is in this state. You can then use this transaction log backup along with the last database backup and any previous transaction log backups to restore the database to the point at which it failed; however, any uncommitted transactions are rolled back. The Log No Truncate method does not remove committed transactions after the log is backed up.

To use the Log No Truncate backup to restore a database, you should also have a database backup that was created before the Log No Truncate backup. The transaction log contains only the log files used in the restore process, which alone are not sufficient to restore a complete database. You must have at least one database backup or a full set of filegroup backups and a log backup of the database to restore a database.

Caution:

Do not run a log backup using either method if the SQL 2000 or SQL 2005 database is using the simple recovery model. With the simple recovery model, you can recover data only up to the most recent full or differential backup. If you run a log backup on a database using the simple recovery completion state, the backup will fail. If using SQL 7.0, do not run a log backup using either method if the SQL database option truncate log on checkpoint is enabled. When this option is enabled, every time a checkpoint occurs in the database, the transaction log is truncated without the truncated part of the transaction log being backed up, which prevents more transaction log backups from being created. You should run a database or differential backup instead.

To check the database properties, from the Enterprise Manager on the SQL Server, right-click the database, click Properties, click the Options tab, and then view the configuration settings.

If using SQL 7.0, do not run a log backup using either method f the SQL database select into/bulkcopy option is enabled, and nonlogged operations have occurred in the database since the last database backup was created. Nonlogged operations break the sequence of transaction log backups. The restore of a database using database and transaction log backups is successful only if there is an unbroken sequence of transaction log backups after the last database or differential backup. If these conditions are present, you should run a database or differential backup and then start running log backups again in order to save any changes necessary to restore the database.

To back up SQL transaction logs

On the navigation bar, click the arrow next to Backup.
Click New Backup Job.
On the Properties pane, under Source, click Selections, and then select the data you want to back up.
To select SQL data, click the domain name icon or icons that contain the SQL installations, and then click the actual Windows computer icon that contains the SQL installation. If you are using a cluster server, make backup selections from the virtual server.

A list of shared network directories appears, along with an icon that represents the SQL installation.
To select SQL backup job properties, on the Properties pane, under Settings, click Microsoft SQL.
After selecting job options, start the backup job or select other backup options from the Properties pane, and then start the backup job.