> Software > Databases > SQL Server

Backups for SQL Server

Backups for SQL Server

The SQL Server backup plan protects databases and transaction logs to support operational recovery, cyber resilience, and compliance requirements.

You can protect SQL Server workloads by using scheduled backups through a backup plan or by running on-demand backups.

Backup types

SQL Server supports the following backup types.

Backup type Description
Full Backs up all selected database data.
Differential Backs up changes since the last full backup.
Transaction log Backs up transaction log records to support point-in-time restores.
Block-level Backs up changed blocks after the initial backup.
IntelliSnap full Creates a storage-array snapshot of SQL Server data.
IntelliSnap backup copy Copies snapshot data to secondary storage.

What is protected

SQL Server backup plans can protect the following components.

Component Protected data
System databases Databases such as master, model, and msdb
User databases Data files, transaction logs, replication settings, and CDC changes
FILESTREAM databases FILESTREAM data
FileTable databases FileTable data
Azure SQL databases Database data
SQL Server Analysis Services databases Source data, aggregations, and metadata
Full-text indexes Full-text index data where supported

Supported configurations

SQL Server protection supports:

  • SQL Server databases hosted on SMB shares for streaming backups.

  • SQL Server databases hosted on Cluster Shared Volumes (CSV) for supported SQL Server versions.

  • NTFS and ReFS file systems on Windows.

  • EXT4 and XFS file systems on Linux.

  • Databases that use Transparent Data Encryption (TDE).

  • Databases that use Always Encrypted.

Encryption considerations

If TDE or Always Encrypted is enabled:

  • Backup operations are supported without additional configuration.

  • Deduplication efficiency might be reduced.

  • For restores to a different SQL Server instance or host, the required encryption keys must be available on the destination system.

Backup behavior

Transaction log backups

By default:

  • Databases that use the Simple recovery model are skipped during transaction log backups.

  • Read-only databases are skipped during transaction log backups.

  • Databases that are not online are excluded from backup operations.

Automatic conversion of transaction log backups

In some situations, a transaction log backup is automatically converted to a full backup.

When this occurs:

  • Transaction log backups continue for eligible databases.

  • Databases that require a full backup are processed in a separate backup job.

  • Converted backup jobs appear as Converted Full jobs.

For more information, see see Automatic Conversion of Non-Full Backups into Full Backups.

Limitations

General limitations

  • The tempdb database is not backed up because SQL Server recreates it when the service starts.

  • The SQL Server instance collation and SQL Server system database collation must match.

VSS limitations

The following VSS-enabled backup operations are not supported:

  • Full-text index backups for SQL Server 2005.

  • SQL Server Analysis Services backups.

SQL Server on Linux limitations

The following backup methods are not supported:

  • Application-aware backups

  • Block-level backups

  • IntelliSnap backups

  • VSS-enabled backups

Technical reference

Block-level backup considerations

When you run a block-level backup:

  • The backup operates as a full VSS backup.

  • The restore destination must have available free space equal to at least twice the database size.

×

Loading...