Back Up SQL Server Data

You can back up the data on your client computer and restore it when required.

Types of Backup

The following backup types are available through the SQL Server Agent:

Supported Backups

The SQL Server Agent supports backup of the following components:


What Is Backed Up

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 Archiving Server Store for Lync Server
SQL Server file stream databases File streams

File table databases

Full-text indexes For SQL Server 2005, traditional and SnapProtect backups

For SQL Server 2008, traditional, SnapProtect and VSS-enabled backups

SQL Analysis Services databases Source data, aggregations, or metadata.

Note: In a farm configuration, SQL databases with Unicode names should be backed up using other SQL backup tools since the SQL Server Agent supports only the standard alphanumeric characters. The SQL Server Agent does not support databases with extended ASCII or UNICODE characters in their names.

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.