Backing up SQL databases

Backup Exec includes three methods for backing up databases: Full, Differential, and for SQL 2005 or later, Full Copy-only. The full method backs up the entire database including all system tables and filegroups. The differential method backs up only the changes made to the database since the last full backup. The copy method works in the same manner as the full method, except that it does not affect future differential or log backups.

A differential backup is smaller and faster than a full backup, so differential backups can be run more often than full backups. Because differential backups allow the restore of a system only to the point that the differential backup was created, you should also create multiple log backups between the differential backups. Using transaction log backups allows you to recover the database to the exact point of failure.

Consider using differential backups when only a relatively small amount of data changes between full backups, or if the same data changes often. Differential backups may also work well in your environment if you are using the simple recovery model and need backups more often, but cannot spare the time to do frequent full backups. If you are using the full or bulk-logged recovery models, you can use differential backups to decrease the time it takes to roll forward log backups when restoring a database.

If you want to run database backups only, instead of a mix of database and log backups, and you are not running SQL 7.0, use the simple recovery model for the database so that the transaction log is automatically truncated when a checkpoint occurs in the database. This helps prevent transaction logs from becoming full since with other recovery models the logs are not cleared after a database backup.

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

If you want to run database backups only, and are using SQL 7.0, use SQL Enterprise Manager to enable the database option truncate log on checkpoint for each database you are backing up. Enabling this option causes the transaction log to be automatically truncated whenever a checkpoint occurs in the database.

If you do not run transaction log backups, you can recover the database to the point of the last backup, but you cannot restore the database to the point of failure or to a specific point in time.

The master database can only be backed up with the full method; you cannot use the log or differential methods to back up the master database.

Note:

You cannot back up databases to devices that are attached to a computer on which the Remote Media Agent for Linux Servers is installed.

Note:

The SQL Agent supports a mirrored SQL database configuration, although Microsoft places limitations on the mirroring of SQL databases. These limitations include the following:

To back up SQL databases

  1. On the navigation bar, click the arrow next to Backup.

  2. Click New Backup Job.

  3. On the Properties pane, under Source, click Selections, and then select the data you want to back up.

    See About using Backup Exec logon accounts for SQL resources.

  4. To select SQL data from local or remote selections, 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 all databases in SQL, click the check box preceding the SQL icon, or you can select specific databases by clicking the SQL icon, and then selecting individual databases.

    Whether you make SQL database selections using the Windows domain, Active Directory, DNS names, or IP addresses, you must use the same method when making full, differential, and incremental backups of your SQL databases. For example, do not make full backup selections of your SQL databases using the Windows domain, and then make incremental or differential selections using an IP address.

  5. To select the SQL backup job properties, on the Properties pane, under Settings, click Microsoft SQL.

  6. Select options for the backup job.

    See Setting backup options for SQL.

  7. Start the backup job or select other backup options from the Properties pane, and then start the backup job.

More Information

Creating a backup job by setting job properties

Backing up SQL transaction logs

Setting backup options for SQL