MSP360 News
Articles, guides, whitepapers and other useful content for MSPs and IT Professionals
BLOG MENU
SQL Server Database Backup

SQL Server Database Backup with MSP360 (CloudBerry)

SQL Server Database Backup with MSP360 (CloudBerry)

To keep your Microsoft SQL Server databases safe, you need to back them up. Today, it is considered a best practice to have at least two backup copies of all of your data, one on-site and one off-site.

In this article, we will demonstrate how CloudBerry Backup can provide both on-site and off-site backups of MS SQL Server databases by backing up data to both local storage and cloud storage.

Table of Contents

    SQL Server Database Backup and Restore with CloudBerry Backup

    CloudBerry Backup integrates with Microsoft SQL Server’s built-in backup and restore mechanism. CloudBerry Backup also features encryption, flexible scheduling and a range of other options.

    You need CloudBerry Backup for MS SQL Server or CloudBerry Backup Ultimate editions in order to be able to perform SQL Server database backup and restore.

    In this article, we’ll describe how to back up Microsoft SQL Server databases using CloudBerry Backup. We’ll also explain some important details that you need to know and take into account when performing backups of your databases.

    For instructions on the SQL Server database restore process, please check out our article “SQL Server Database Restore.”

    For the purposes of this article, we assume that you’re already familiar with how SQL Server functions. We’ll focus below on how our backup solution interacts with the SQL Server.

    How to Back Up a Database in Microsoft SQL Server

    1Launch the Backup Wizard by clicking MS SQL Server button on the main toolbar or pressing Ctrl+B.
    Launch SQL Server database backup in CloudBerry Backup

    2In the Backup Wizard, specify whether you want to perform backup to a single location (Local or Cloud Backup) or whether you'd like to back up to a local storage first and then to a cloud storage (Hybrid Backup).
    In this guide, we will choose the Hybrid Backup option, which allows us to follow the 3-2-1 backup rule.
    Selecting database backup type for SQL Server database backup

    3Select a local storage for your backup and click Next.
    Selecting local backup destination for SQL Server database backup

    4Select a cloud storage location for your backup and click Next.
    Selecting off-site backup destination for SQL Server database backup

    5Enter a plan name and proceed to the next step.
    Enter the backup plan name for SQL Server database backup

    6Select the desired SQL Server instance from the drop-down menu and enter your login authentication type/credentials. Enable the option Check if the specified account has necessary permissions to perform backup in order to make the wizard automatically check whether the specified user account has all necessary permissions for backing up the database. When you’re done, click Next.
    Selecting SQL Server instance for SQL Server database backup

    7On the database selection step you have 3 options:
    - Back up all databases.
    - Back up all user databases only.
    - Back up selected databases.
    Select the option you desire and click Next.
    Select SQL Server databases for backup

    8Configure compression and encryption options.
    Microsoft uses a proprietary backup compression algorithm to compress data for SQL Server in certain versions. Here are the versions that support this feature:
    - SQL Server 2008: Enterprise, Developer.
    - SQL Server 2012 and higher: Enterprise, Business Intelligence, Standard, Developer.
    CloudBerry Backup will use native SQL Server compression if supported by your SQL Server version. Otherwise, CloudBerry software will use a custom compression algorithm.
    As for security, you can choose whether you’d like to use 256-bit encryption offered by Cloudberry Backup.
    Compression and encryption for MS SQL Server database backup

    9Specify the retention policy.
    You can save only the necessary number of copies. This helps to prevent you from running out of space or paying more than necessary when using cloud storage.
    Retention policy for SQL Server database backup

    10On the scheduling step you have the following options to choose from:
    - No schedule or Specific date. If you choose this option, your backup will run as a one-time job; in other words, a full-backup will be performed with no differential or transaction log backup taking place.
    - Recurring (predefined templates) or Recurring (advanced schedule). These two options enable you to configure the frequency of full, differential, and transaction log backups.
    Here are the definitions of these SQL Server database backup types:
    Full backup — a database backup that contains all the needed information in a database to perform a full restore.
    Differential SQL Server backup  — a database backup that contains only the data that has changed since the last full backup.
    Transaction log (T-log) backup — a backup of current database transactions that includes all log records that were not backed up in a previous log backup.
    Scheduling of sql database backups
    Note that SQL Server database backup and restore operations occur within the context of the recovery model of the database. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. If you want to perform transaction log backup, ensure that your database recovery model property is set to Full or Bulk; any other value will result in an error.

    11Having selected the Recurring (predefined templates) option on the previous step, you’ll be able to choose from one of our recommended templates.
    Scheduling of SQL Server database backups: predefined optionsScheduling of SQL Server database backups: select and apply

    12Alternatively, if you use the Recurring (advanced schedule) option, you can set the required frequency by minutes/hours/days, schedule bi-weekly backups, and so on.
    Advanced Scheduling of SQL Server database backups Advanced Scheduling of SQL Server database backups part 2

    13Set up pre- & post- actions: specify scripts to be executed prior to and right after the backup plan execution. You can also create a backup chain.
    Backup chain for SQL Server database backup

    14Complete the backup wizard and wait for the next scheduled execution or, if you wish, execute the backup plan immediately.
    SQL Server database backup report

    15Once complete, the backups will be displayed in the backup storage tab.
    Stored SQL Server database backups overview

    Backing Up SQL Server Databases Using More Than One Product

    We don't recommend using multiple backup methods for a single SQL Server. You should instead use a single backup software or scripts in order to keep your toolset and process simple, and avoid data conflicts.

    If you want to back up your SQL Server databases to two different locations, take advantage of the Hybrid Backup feature offered by CloudBerry Backup. (This option was chosen in step 2 of our guide above.)

    If you have two products running backups on the same set of databases, you may end up with differential and transaction log backups that cannot be restored.

    As an example, let’s say you use two backup products and do the following procedure:

    1. Back up your customer database using CloudBerry Backup (CustomerFull1).
    2. Perform the first differential backup for that database using CloudBerry Backup (CustomerDiff1).
    3. Back up the Customer database using another product (CustomerFull2).
    4. Perform the second differential backup for the master database using CloudBerry Backup (CustomerDiff2).

    You would expect that CustomerDiff2 could be restored after CustomerFull1, but in reality, CustomerDiff2 is based on the CustomerFull2 database, which was created by a different product. You may not be able to perform the restore in the way you expect.

    This case only applies to individual databases. If you perform a backup of two different databases using two different backup plans or backup products, there should be no issues.