Skip to end of metadata
Go to start of metadata

Backup a Microsoft SQL Server

Microsoft SQL Server is a database application. It can stand alone or be part of another application.

Overview

Microsoft SQL Server backups have the following properties:

  • Microsoft SQL Servers are automatically discovered at backup set creation.
  • Individual databases can be selected for backup.
  • Only databases are backed up. 
    • Other MSSQL files, such as installation, files are not backed up. Include these files as part of a File System backup to protect them.
  • Transaction logs are truncated during any full or incremental (log) backup. Transaction logs are not truncated during differential backups.
  • Only databases that are in the Mounted state will be backed up.
  • System databases are only backed up during full backups. They are skipped during differential and incremental (log) backups. 
  • Read-only databases are only backed up during full backups. They are skipped during differential and incremental (log) backups. 
  • Only local databases can be backed up. Backups of databases on another system are not supported.
  • New databases will be automatically detected and backed up if all databases are selected selected for backup. 
  • Databases that are removed from a Microsoft SQL Server will be automatically removed from the backup if all databases are selected for backup. 
  • Databases are not automatically added or removed from the backup if individual databases are selected. 

Recovery Models

Databases configured with the SIMPLE recovery model, FULL recovery model, and BULK recovery model are supported.

Simple Recovery Model

  • The full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image.
  • The differential backup will contain .LDF files.
  • Incremental (Log) backups will be skipped for databases using the Simple Recovery Model.

Full Recovery Model

  • The full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image.
  • The differential backup will contain the changed blocks of the .MDF database file.
  • Incremental (Log) backups will contain .TRN files (transaction logs flushed to the disk). These are transactions that have changed since the last backup of any level, be it Full, Differential, or Incremental.

Bulk Recovery Model

  • The full backup will contain .MDF, .LDF and .NDF (in case of filegroups) files in the backup image.
  • The differential backup will contain the changed blocks of the .MDF database file.
  • Incremental (Log) backups will contain .TRN files (transaction logs flushed to the disk). These are transactions that have changed since the last backup of any level, be it Full, Differential, or Incremental.

Backup Levels

Microsoft SQL Server backups support Full, Differential, and Incremental (log) backups.

Special Options

The following special options are available for Microsoft SQL Server backups. 

Allow Access

The amandabackup user must have access to the databases. The Allow Access button, located beneath the database tree, attempts to automatically add amandabackup to the proper roles. There are two possible results:

  • Access Granted: CSB was able to add amandabackup to the proper roles for all instances.
  • Access Denied: CSB was not able to add amandabackup to the proper roles for one or more instance. The amandabackup user must be added manually. Please see Allowing access to Microsoft SQL Server
    • Adding amandabackup manually does not change this result to Access Granted. The Allow Access button's only function is to try to add the user. It does not validate if amandabackup is present or not.

Requirements

The following requirements must be met for Microsoft SQL Server backups to function. Unmet requirements are the most common cause of MSSQL backup failures.

  • The amandabackup user must be added as a SQL server user with sufficient privileges. Please see Allowing access to Microsoft SQL Server.
  • The Volume Shadowcopy Service must be started. 
  • Microsoft SQL Server and Windows System State backups should not be performed simultaneously. 
  • The SQL VSS Writer Service must be running at the time of backup. CSB will attempt to start the service if it is disabled.
  • TCP/IP must be enabled for all MSSQL instances to be backed up. TCP/IP settings are controlled in the SQL Server Configuration Manager tool.
  • Transaction log-based databases such as Microsoft SQL Server, Exchange, or Sharepoint are not intended to be backed up by multiple backup applications. 

 

Icon

ZCB can perform differential backups of SQL Server 2014 databases with memory-optimized tables. However, preview builds of SQL 2014 are not optimized for differential backups.

In CTP2 (Community Technology Preview 2) or earlier, the differential backup will include all data and delta files as if it were a full backup. There will be no reduction in size; a differential is essentially the same thing as a full backup.

SQL 2014 RTM (release-to-manufacturing) is optimized so that differential backups are smaller than full backups in the expected manner.

Please see this page for more information: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/07/differential-database-backup-with-memory-optimized-tables.aspx

Icon

Microsoft Sharepoint databases can be backed up as if they were Microsoft SQL Server databases. If possible, use the Sharepoint backup type to back up Sharepoint databases.

Do not back up Microsoft Sharepoint databases in more than one backup set. The result is the same as if multiple backup applications were used. See Info: How do databases handle backups from multiple different backup applications? for more information.

 

 

 

  • No labels