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.

• 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.

  Symantec recommends that you run regular database consistency checks to verify the integrity of the database.

• 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 fails the differential and log backup jobs until you make a new full backup with the SQL Agent.

• Run transaction log backups if the database is configured to support transaction log backups to prevent unlimited log file growth. Backup Exec generates a success with exception job warning after 10 non-log backups in a row.

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.

• Ensure that Backup Exec has rights to the following registry keys:

  • HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server

  • HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQL

• Do not apply the credentials that you use to make SQL backup and restore selections to an actual SQL instance. The credentials that you use to make SQL backup and restore selections must be applied to the Windows computer where SQL is installed.

  Note:

  If you use SQL-generated credentials, you must apply those credentials at the SQL instance level.