Skip to main content

 

Druva Documentation

Backup methods available for MS-SQL server databases

Phoenix Editions: File:/tick.png Business File:/cross.png Enterprise File:/tick.png Elite

This article provides information on backup methods such as:

Prerequisites

Before Phoenix can back up your databases, you have to configure your server for backup and restore. See, Quick steps to set up Phoenix to back up databases.

Full backups

Phoenix performs an ever incremental backup of the databases of your SQL servers. As mentioned in the prerequisites, backup and restore operations depend on VSS and SQL writer services. When a full backup is triggered based on the backup policy, Phoenix agent communicates with the SQL writer service using the VSS framework. The Phoenix agent executes a full scan of all the databases included in the backup. If your databases are getting backed up for the first time, all databases are backed up entirely. If your databases are not getting backed up for the first time, a full scan of databases is executed but only the database changes since the last backup are backed up after deduplication is applied. When the backup job is successful, Phoenix creates a snapshot (restore point) in your storage.

Understanding snapshots created using full backup

When you specify the number of days to keep all snapshots in the daily retention period, all the snapshots created within that period are retained.

For example, in your retention period, you specify keep all snapshots for 10 days. In your backup policy, the full backup is scheduled to run on Wednesday, 12:00:00 PM PST for every week.

Between February 1, 2017, and February 28, 2017, Wednesday falls on 1st, 8th, 15th, and 22nd. The full backup is triggered on February  1, 2017, and a snapshot is created at 4:00:00 PM when the job is successful.

On February 8, 2017, another full snapshot is created at 3:00:00 PM. Since all snapshots are retained for 10 days, before February 11, 2017, 11:59:59 PM, the full snapshots created on January 1, 2017, and January 8, 2017, are retained. After February 11, 2017, 11:59:59 PM, February 1, 2017, the snapshot is retained based on your weekly, monthly, and yearly retention period.

Restoring a database from a snapshot created after a full backup

For example, you are restoring your database on February 12, 2017, at 11:00:00 AM. When you trigger a restore job from the Phoenix Management Console, you see the following snapshot options to restore your data from:

  • Hot
  • Warm
  • Thawed
  • Cold

Phoenix retains a snapshot in warm storage for 90 days. If the snapshot created on February  1, 2017, is the latest snapshot in the week ending February 5, 2017, 11:59:59 PM, it is retained based on the weekly retention period. The snapshots created on February 1, 2017, and February 8, 2017, fall under warm snapshots. You can directly restore your database using a warm snapshot. To restore your database using a cold snapshot, you have to defreeze it first. After you defreeze your snapshot, it appears under thawed snapshots, and then you can use it to restore your database. A snapshot created in November, and retained based on retention policy can be considered as a cold snapshot for this example.

You can see hot snapshots if you have configured Phoenix CloudCache. To read more about Phoenix CloudCache and hot snapshots, see Phoenix CloudCache.

Workflow

The full backup contains the following steps:

  1. Phoenix checks if the agent is running. If the agent is not running, Phoenix queues the backup request. The request is executed after Phoenix agent is up and running.
  2. Before the backup operation starts, agent checks if VSS Service and SQL Writer service are running or not. If the services are not running, then the agent attempts to boot these processes automatically.
  3. During estimate phase, the agent fetches the database information from MS-SQL server using the Volume Shadow Copy API.
  4. The agent then determines the distinct file-sets which should be backed up. VSS then provides a snapshot.
  5. If you are backing up your databases for the first time, entire snapshot is backed up. If you are not backing up your databases for the first time, updated data, in terms of file-sets, is copied from the snapshot and uploaded to the server. The backup is marked as successful after data is successfully uploaded to the server.

Additional information

  • The VSS snapshot is transient and is deleted automatically by the operating system. If the hard drive is not accessible, the VSS and MS-SQL server may take some time to recognize and update their internal file information.
  • Backup may fail if it is triggered during this time interval while database files are moved to the partition containing deleted files.
  • Backup jobs can be completed successfully after VSS and MS-SQL server updates internal file information. But if some database files are moved to the deleted files partition and some other files are still available on the logical partition, the backup always fails. As a workaround, you have to clean this database manually.
  • The system database tempdb is never backed up.
  • During backup, database entities get backed up in following order:
    • Database files: MDF, LDF and NDF files
    • Agent-pecific metadata (json data)
    • VSS backup document (XML data)
    • SQL Writer metadata document (XML data)
    • Sentinel (json data)
  • On your server, if
    • DB1 has files at C:\Data
    • DB2 has files at C:\Data
    • DB3 has files at C:\Program Files\Data

The agent identifies C:\Data and C:\Program Files\Data as two filesets, and are uploaded sequentially. However, the files within a fileset are uploaded in parallel.

Differential backups

MS-SQL servers provide the differential backup functionality. When a differential backup is triggered, the agent requests information about databases that are updated and included in the backup policy. Phoenix backs up the differential data and creates a snapshot in your storage. A differential backup can be triggered if a snapshot from a full backup exists. If a snapshot from a full backup does not exist, the differential backup is automatically converted into a full backup job.

Apart from the schedule, you can manually trigger a differential backup from the Server page (Path to the server page: Phoenix Management Console > Organization > Servers > Configured > Your Server Name). On your Server page, you can click Backup Now.

Note: When you click Backup Now, differential backup is triggered. However, if your server is getting backed up for the first time, full backup is triggered. A manual backup is not restricted by bandwidth you specify, it uses maximum bandwidth available.

Understanding snapshots created using differential backup

A snapshot created using a differential backup works similar to a full backup job in the way that it creates a snapshot that you can use to restore your database. Since a differential backup job can be faster in execution in comparison to a full backup job, you can schedule multiple differential backups in between full backups.

Continuing from the previous example, let us assume that you have specified daily differential backups at 9:00:00 PM. Between February 1, 2017, and February 10, 2017, 10 differential snapshots are created. When you try to restore your database using snapshots on February 11, 2017, at 5:00:00 AM, you can see 12 snapshots (2 snapshots from the full backup and 10 snapshots from the differential backups) under warm snapshots. Differential snapshots are also retained based on the daily, weekly, monthly, and yearly retention policies.

The advantage of differential backup is the backup job speed. A differential backup job is faster than full backup since the agent directly gets information about the updated database from the MS-SQL server.

Restoring from a snapshot created using differential backup

If you are restoring your database on February  13, 2017, at 11:00:00 AM,

  • The snapshots after differential backups on February 1, 2017, and February 2, 2017, are purged.
  • In the warm snapshots, you can see snapshots after differential backups created starting February 3, 2017, through February 12, 2017.
  • You can see snapshots created after full backup on February 1, 2017, and February  8, 2017.

Since all the snapshots above fall under warm snapshots, you can select any snapshot as a restore point, and use it to restore your database. However, when you select a snapshot created after a differential backup, Phoenix uses a full backup snapshot created right before it. Typically, the full backup snapshot created on February 1, 2017, is purged based on 10 day retention period. But when you select a differential snapshot created before February 8, 2017, for restore, Phoenix requires the full backup snapshot created on February 1, 2017. Since there are differential backup snapshots that are retained and are tied to the February 1, 2017, snapshot, the February 1, 2017, snapshot is retained.

Workflow

The differential backup contains the following steps:

  1. Phoenix checks if the agent is running. If the agent is not running, Phoenix queues the backup request. The request is executed after Phoenix agent is up and running.
  2. Before the backup operation starts, agent checks if VSS Service and SQL Writer service are running or not. If the services are not running, then the agent attempts to boot these processes automatically.
  3. During estimate phase, the agent fetches the database information from MS-SQL Server using the Volume Shadow Copy API.
  4. For differential backup, SQL server provides updates to databases since the last backup. Phoenix uploads these changes to the server.  Backup is marked successful when the files are successfully uploaded to the server.

Additional information

Phoenix treats differential backups as full backups, if: 

  • A differential backup of a database for which a full backup is not complete is attempted. 
  • A new database file is added to or deleted from a database since the last backup.
  • A database file is renamed or moved since the last backup.
  • A database was renamed.
  • Inconsistencies between the uploaded database files and its metadata are detected. 
  • The master database was included in the last full backup, and the path or name of the master database files changed since the last full backup.
  • Phoenix cannot obtain details about data change from the Volume Shadow Copy Service.
  • A full backup fails, then the next differential backup will be converted to a full backup.
  • If a full backup is executed by Phoenix MS-SQL agent and the second full backup is been executed by a third-party backup tool or MS-SQL server. Then Phoenix agent can convert next differential backup to full backup. 
  • If a database is encountered as shrunk since last the full/differential backup, Phoenix converts the subsequent differential backups to full backups. 

Transaction log backup

Transaction log in MS-SQL servers records all the updates to the servers. Phoenix frequently backs up the transaction log based on the intervals you provide in the backup policy and are triggered after a full or differential backup is complete. When Phoenix backs up transaction logs, transaction logs are truncated on the SQL server. If a full backup is not complete for a server even once, the transaction log backups are not triggered. Transaction logs provide a tighter restore point objective and are dependent on snapshots created using the full or differential backup. Log backups are not applicable for databases in simple recovery mode. 

Understanding log backup schedules

Backed up transaction logs are retained based on daily retention policy. Weekly, monthly, and yearly retention policies are not applicable for log backups.

For example, you specify an interval 30 minutes for log backups. In the example above, the full backup is triggered on February 1, 2017, at 12:00:00 PM, and completes at February 1, 2017, 4:00:00 PM. At 4:30 PM, a log backup is triggered. Subsequently, the transaction log is backed up every 30 minutes until 9:00:00 PM when differential backup kicks in.

Restoring from a transaction log

When you restore your database using a transaction log, you can:

  • Choose a point-in-time when a transaction occurred
  • Choose a marked transaction in the transaction log and restore your database to that point.
  • Choose a point-in-time when a transaction occurred and leave the database in standby mode

When you choose a point-in-time or a marked transaction, Phoenix restores your database using a snapshot. After restoring your database using a snapshot, it applies transaction logs to your database up to the selected transaction. This lets you restore your database to a time between two snapshots, allowing a tighter restore point objective.

Workflow 

The following flowchart depicts the transaction log workflow:

Next step

After your databases are backed up, you can restore them if a recovery is required. For more information on restore jobs, see: