Thursday, August 18, 2016

Backup tips for MS SQL with MOB & Max Backup

Hello Friends,

Backing up Microsoft SQL with either Managed Online Backup through your Max RM console, or the stand-alone Max Backup, is incredibly easy, and in this article I am going to share with you the differences between Simple and Full recovery modes, explain who and what is responsible for truncating logs, and guide you through the process of making any changes necessary to your current configuration.

The steps listed in this article apply exactly the same to both Managed Online Backup and Max Backup, as all the configuration is done in the Backup Manager itself.

Basic setup of MS SQL in the Backup Manager:

  1. Choose MS SQL databases (VSS) and click Add User-added image
  2. You can make the necessary selection for the back up and click SaveUser-added image

  • MS SQL Writer is a default part of the MS SQL server and a backup of MS SQL cannot be performed without it. Thus, please, make sure that MS SQL Writer is running correctly.
  • If the database uses the Simple Recovery mode, its logs are truncated automatically by the MS SQL server when the database reaches a transaction checkpoint.

Backing up MS SQL in Full Recovery mode: 

Backing up in Full Recovery mode is still possible, however please keep in mind that we do not truncate your backup logs at any time in simple or full recovery mode. This process is handled by SQL, so if you're in Simple Recovery mode this is done automatically. Since we're talking about Full Recovery mode, you will need to manage your logs through the SQL Manager.

Incremental and Differential Backups of SQL:

When backing up SQL databases in Full Recovery mode via the VSS plugin, we will perform a 'full' backup, and since this won't be in sync with your chain, this process can 'break the chain' when performing an incremental SQL backup. For Differential backups, the 'full' backup will simply happen sooner (as per the backup process) but may not be desirable.

As such, we've added the ability to backup databases in 'copy-only' mode:

  • Copy-Only Mode for Microsoft SQL Server Backup – It is now possible to perform a backup of Microsoft SQL Server in COPY-ONLY MODE.  This mode performs a full backup without updating the internal backup history for SQL Server. This method of backup does not act as a base backup for further differential backup operations and also does not disturb the history of the previous differential backups. DBA’s should fully understand the recovery implications of using different SQL backup modes prior to enabling this ability.  To enable add the following section and entry to the config.ini on the local Backup Manager and restart services.
  •  The config.ini file should be located at C:\Program Files\Backup Manager\config.ini

Using the above method, you can now have a safe, off-site copy of your databases while still maintaining local SQL provided backups without disturbing the history of the previous differential backups.