Loading...

SQL Backup Operations

You can back up online or offline SQL databases, log files, or SQL datafiles and tablespaces. If the database must be accessible and you have a small backup window, run a series of online backups for different database portions.

You can run backups immediately or configure a schedule for the subclient.

Full Backups

Backups for any client start with a full backup. For Agents that support non-full backups, the full backup becomes a baseline to which subsequent backup types are applied. For example, a full backup must be performed before a differential backup can be initiated.

Differential Backups

A differential backup contains only the data that is new or has changed since the last full backup.

Transaction Log

A transaction log backup operation backs up the transaction logs that contain records of all the committed and uncommitted transactions. Dirty pages are excluded during a transaction log backup operation. This backup is consistent and contains only the new changes since the last full backup or since the backup of another transaction log.

After a database failure, you can run the transaction log backup to recover data to the point of failure. You cannot perform a point-in-time restore from a full or a differential backup alone.

During a transaction log restore operation, if dirty pages are included, the completed transaction rolls forward. Any incomplete transaction that occurs during the time of the transaction log backup rolls back during the transaction log restore operation.

Supported Backups

The following backups are supported.

Component

The backup includes

SQL Server system databases

Databases such as master, msdb and model

SQL Server user databases

 

Data files:

  • Transaction logs such as log records and log marks
  • Replication settings
  • CDC changes

SQL Server file stream databases

File streams

File table databases

Unsupported Backups

The SQL Server Agent does not support VSS-enabled backups of full-text indexes (FTI) for SQL Server 2005. Instead, use the Windows File System Agent to back up the full-text indexes.

The tempdb database is not backed up because it is recreated by the SQL Server every time the server is started.

Last modified: 10/31/2018 7:35:55 PM