Best practices for Backup Exec 2014 Agent for Microsoft SQL Server

Best practices include tips and recommendations to help you use Symantec Backup Exec Agent for Microsoft SQL Server (SQL Agent) effectively. For more information about the SQL Agent, see the Backup Exec 2014 Administrator's Guide.

The following best practices help you use the SQL Agent effectively:

Back up the entire Microsoft SQL Server.

Include the following in the backup job:

Full SQL database backups

Windows System State

System drive backups of the hard drive or drives where Microsoft SQL resides

System drive backups of the hard drive or drives where the Microsoft SQL databases reside

Exclude all database files from an anti-virus scan.

Use database, differential, and log backups to maximize your backup window. Combine these backup methods with backup strategies that address the following issues:

How much data loss can you accept if a failure happens between the time of the last backup and the time the loss occurred?

How many transactions are processed each day?

What are your users' expectations when a recovery is required? For example, do they expect a full recovery to the point at the time when the data loss occurred?

Use only the SQL Agent to perform SQL full, differential, and log backups. If you use a third-party application, Backup Exec makes a new full backup with the SQL Agent.

Run transaction log backups if the database is configured for the full recovery model to prevent unlimited log file growth. Backup Exec generates a success with exception job warning based on the current size of the log file.

With the simple recovery model, copies of the transactions are not stored in the log file, which prevents transaction log backups from being run.

Use snapshot technology with the backup jobs that use deduplication devices.

If you restore a large database that use deduplication devices, ensure that you use the SQL Management Studio to reduce the amount of memory that the SQL instance uses.

Symantec recommends that you run regular database consistency checks to verify the integrity of the database. Run a consistency check either before or after a SQL backup and after a SQL restore. If you back up a database, transaction log, or file group that contains errors, these errors still exist when the backup is restored. In some cases, these errors can prevent a successful restore. Backup Exec lets you check the logical and physical consistency of the data before and after a backup. SQL reports any consistency check failures in the Backup Exec job log.

You should specify the following SQL backup and restore options for the consistency check:

Consistency check before backup - Physical check only

Continue with backup if consistency check fails

Consistency check after restore - Physical check only

To ensure recovery after a disaster, run periodic test restore jobs and ensure that they are included in your disaster preparation plan.

To ensure recovery from a deduplication device after a disaster, restore the backup set that was created after the baseline deduplication backup set.

The following best practices help you with security and database access with the SQL Agent:

Ensure that the Windows user account that you use to back up the SQL instances has System Administrator privileges.