SQL Server Protection

SQL Server backups should be created using System Protection bare-metal backups, which can be used to restore SQL databases or recover the full SQL Server. You can use Cloud Backup, File Protection and File Archiving, but they do not provide an SQL Server recovery option.

SQL Server Protection is a dedicated SQL backup type in the Jobs tab. It uses the SQL Continuous add-on to backup local and remote SQL Servers. The local backups can be used for point-in-time database restores and full SQL Server restores.

This guide explains how to create an SQL Server Protection backup job.

SQL Continuous requires the SQL Continuous add-on license, which unlocks new backup and restore options in BackupAssist. The new options include three types of backups and a new restore option that is used to restore backups created using the add-on.

Basic SQL Server Backup

The feature creates a daily backup of an SQL Server or selected databases. These backups can be used to restore the SQL Server or selected databases. A Basic backup acts as a backup scheme and is selected as a Schedule type when you create the backup job.

Transactional SQL Server Backup

This feature backs up selected databases throughout the day (at 5-minute or greater intervals). A transaction backup acts as a backup scheme and is selected as a Schedule type when you create the backup job.

Remote SQL Server backups

This feature can protect any number of remote SQL servers (on the same domain) in addition to the local SQL Server, using one SQL Continuous add-on license. A remote SQL Server backup is created by entering the path of a remote SQL Server in the Selections screen. A remote SQL Server backup is then set up as a Basic or Transactional Backup schedule. Remote SQL backups are saved to the SQL Server that is running the SQL Continuous add-on.

SQL point-in-time restores

SQL point-in-time is a restore option that is enabled in BackupAssist when the SQL Continuous add-on license is activated. It is used to restore backups made by SQL Continuous add-on. To learn more, see SQL Point-in-Time restore

SQL Continuous and standard backups comparison

This table compares the SQL Continuous add-on with standard backup types: System Protection, File Protection and File Archiving.

SQL Protection SQL Continuous Standard backup types
Full Server backup Yes Yes
Selected database backup Yes No
Continuous transaction level backup Yes No
Full Server restores Yes Yes
Selected database restores Yes No
Point-in-time restores Yes No
Remote SQL Server backup and restores Yes No

Basic backup and Transactional backup comparison

This table compares Basic schedule and Transaction schedule backup jobs.

Backup type Backup process Restore options

Basic

(Daily)

A daily backup of selected SQL databases or the complete SQL Server (by selecting all of the databases) that is the run at the time specified. Any SQL databases in a backup can be restored from the time the backup was run. If all databases were backed up, the SQL Server can be restored.

Transactional

(Continuous)

The backup job creates transaction level backups throughout the day. These continuous backups can occur at intervals of 5 minutes or greater. Select a point in time, and the restore tool will display the backups closest to that point that you can restore the selected databases from.

Transaction logs

SQL uses transaction logs to record all database changes. A complete set of transaction logs are maintained, reaching back to your most recent full backup. The full backup and the subsequent transaction backups can be used to restore a database to a point in time by restoring the database and applying all the changes that took place, up to the point required.

. Transactional backup job creates a full backup and updates that backup at user-defined intervals. When the first interval backup runs, the log is backed up and cleared again. These logs grow and when they are backed up, all committed transactions are removed.

The transaction log supports the following operations: Recovery of individual transactions, Recovery of all incomplete transactions when an SQL Server is started, Rolling a restored database, file, file group or page forward to the point of failure.

Note: Transaction log backups of SQL Server databases are only possible if the databases are configured using the full or bulk Logged recovery models option. For additional in-depth information on SQL database recovery models, please refer to http://msdn.microsoft.com/en-us/library/ms175477.aspx.

SQL backup on a Hyper-V guest

The SQL Continuous add-on can be used to backup SQL Servers that are running on guests (VMs) in Hyper-V environments.

Backing up SQL from the guest

BackupAssist and the SQL Continuous Add-on should be installed on the guest VM that is running the SQL Server.

Backing up SQL from the host

It is best practice to not run applications on the Hyper-V host because any problems or performance overheads could affect all of the guests, and software updates could require a server reboot, causing an outage for all of your guests and the services they provide.

If your Hyper-V Server is running multiple SQL servers on separate guests (VMs) it is possible to install BackupAssist and the SQL Continuous add-on the Hyper-V host and back up each guest, but this is not recommended.

If you want to back up from a host that is a part of a workgroup, we suggest proceeding as follows:

  1. Create a domain user with the appropriate backup rights. You need to create a Windows domain user that has sufficient permissions to back up SQL databases from the SQL server.
  2. Configure a Backup User Identity on the Hyper-V Host. Once you have created a domain user with the appropriate permissions, create a user on the Hyper-V Host with the following properties:
    • The same username and password as the user created for the domain in Step a.
    • Be a member of both the Administrators and the Backup Operators groups.

Warning: If you back up SQL Server from host that is part of a workgroup you may encounter errors. If you do have problems you will need to install an instance of BackupAssist on the guest where you can activate the SQL Add-on along with BackupAssist . If you want to back up from a host that is a part of same domain, there will be no permission related issues.

SQL Databases

When you create a backup job, you will be asked to select the SQL databases that you want to back up. This initial step is the same for both Basic and Transactional backup jobs. This section explains the types of databases in SQL, and any requirements or limitations that apply to the backup and restoration of each database type.

User databases

User databases contain the information that you have used SQL to store and manage. This is the business data that is backed up to protect your company’s information. This information should be backed up on a regular basis so that it can be recovered in the event of data loss or corruption.

System databases

SQL system databases are the databases used by the SQL Server itself. These need to be backed up in addition to the user databases, so that the entire SQL Server can be rebuilt in the event of system or database failure. The system databases vary depending on the version of SQL you are running, but generally include the following:

Master Records all the system-level information for an SQL Server, and is composed of system tables and catalogs, and is critical to your SQL Server. If you are restoring your SQL Server after a system failure or new installation, it is advised that you restore the master database before restoring any other database. The BackupAssist SQL Restore tool can be used to restore all other databases remotely but not the master database. If you need to restore the master database to a remote SQL Server, you must install BackupAssist on the server running SQL. You can only perform full backups of the master database. Transactional log, differential or file group backups of the master database are not allowed.
Msdb Used by the SQL Server agent for scheduling alerts and jobs. Used to store data, including scheduled job information, backup and restore history information and DTS packages (data transmission services). Usually the msdb database is rather small, therefore full database backups provide a fast alternative to transaction log backups of the database.
Model The model database is a template used by Microsoft SQL Server when creating other databases, such as tempdb or user databases. When a new database is created, the entire contents of the model database is copied to the new database. If the master or msdb databases are rebuilt, the model database is also rebuilt and any changes are lost.
Resource A read-only database containing system objects included with SQL Server 2005. Makes upgrading to a new version of SQL Server an easier and faster. Because the resource database file contains all system objects, an upgrade can be accomplished by copying the single resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the resource database with the older version.
Tempdb A workspace for holding temporary objects or intermediate results sets. It is important to have regular backups of these system databases, however, it is not necessary to back up the tempdb system database because it is rebuilt each time SQL Server is started. When SQL Server is shut down, any data in tempdb is deleted permanently. For this reason, do not store any application specific data in the tempdb database. Leave it exclusively for the SQL Server to use.

How to set up a remote SQL backup

The BackupAssist SQL Continuous add-on can back up both local and remote SQL servers that are on the same domain. When the backup job is created, the first step is to select the local and remote SQL servers whose databases you want to include in that backup job. Before you can select a remote SQL Server, you need to make sure that the SQL Server accepts remote TCP/IP connections.

These steps explain how to enable remote TCP/IP connections on a remote SQL Server:

  1. Open the SQL Server Configuration Manager.

    2. SQL Server Configuration Manager

  2. Under SQL Native Client, select Client Protocols to display the protocols. Enable Named Pipes and TCP/IP by right clicking the protocol and selecting Enable.
  3. Under SQL Server Network Configuration, select Protocols for < name > to display the protocols. Enable Named Pipes and TCP/IP by right clicking the protocol and selecting Enable.

    4. You will now need to enable remote connections on your SQL Server.

  4. Open your SQL Server’s Enterprise Manager or SQL Server Management Studio.

    5. SQL Server’s Enterprise Manager

  5. Select Server Properties > Connections, enable Allow remote connections to this server.

After completing these steps, your Windows SQL Server Protocols and Connections settings, will be ready to receive remote connections from BackupAssist.

How to create an SQL Protection backup job

Launch BackupAssist and follow the steps outlined below:

  1. Select the Jobs tab.
  2. Select Home.
  3. Select Create a New Backup Job.
  4. Select SQL Server Protection.

    5. If this is the first time you have created an SQL Server Protection job or if your current backup user identify is the local system logon, you will be asked to provide a Backup User Identity. To learn more, see Backup user identity

  5. Selections.
    The selections screen is used to locate your SQL servers, authenticate them and select the databases to be backed up. BackupAssist will scan your local machine for SQL servers, and any servers found will be selectable from a drop-down list.

    6. To make your server selection:

    1. Select the server that you want to connect to from the drop down list, or enter the name of the SQL Server or its IP address, into the Server field.

      2. Selections

    2. Select Connect.
    3. An authentication dialog will open. Select Use a BA user identity if this account has access to the SQL Server you are connecting to. Select Specify user identity if you need to use another Windows or SQL account to connect to the server, and provide that account's user name and password.

      4. The authentication account, BA user identity account (if different to the account used for this backup) and the SQL Server’s service account, ALL need access to the backup destination.

    4. When the connection is made, the SQL Server's databases will appear in the Databases field.

      5. Selections : Database selection

      The databases displayed will be system databases and user databases. It is important to understand the difference, and to select the correct databases for your backup. User databases contain your business and user data. System databases are used by the SQL Server to operate the database.

      If system databases are included in a backup, that backup can be used to perform a master database restore. All SQL backup rotation schedules should include a backup of the system databases. To learn more, see SQL Server Protection

      Note: If you want to be able to perform a full SQL Server restore, you will need a backup that includes the master database and all of the system databases.

      Select the databases to back up, and click Next.

  6. Destination media.
    You will not be prompted to provide a destination. This step will be skipped. SQL Server Protection backups are saved to a local drive on the SQL Server. The path used will be set to a default, and can be updated at the, Set up Destination step.
  7. Schedule.
    There are two schedules available for SQL Server Protection: Basic, which provides a daily backup of the SQL Server, and Transactional, which runs transaction backups through the day for point-in-time restores.

    8. Basic backup .

    This scheme creates a backup of the selected SQL databases. The backup job is run once per day at the time set in this screen.

    Schedule : Basic backup

    To create a basic backups:

    1. Enter the start time for each day's backup.
    2. Select Next.

    Transactional backup .

    This scheme creates a full daily backup at the start of the day, and then runs transaction log backups at pre-defined intervals (minimum of every 5 min) throughout the day. This provides an incremental backup because SQL can use the transaction log to restore data. Each day’s database backup is saved in the same location along with each transaction log.

    Schedule : Transactional backup

    To create a transactional backup:

    1. Use the Protect between fields to enter a time range during which the transactional backups are to run.
    2. Use the Run every field to select the frequency of the transactional backups.
    3. Select Next.
  8. Set up destination.
    SQL Server Protection backs up to the local drive, and a default local path will be provided for this step. You can change this path to another location on the SQL Server’s local drive.

    9. Set up destination

    This location is used for both Basic and Transaction backups of the local SQL Server, as well as any remote SQL servers that were selected. The files are saved locally because the scheduling is tied to the Basic and Transactional backup types, rather than different destinations and media rotations. However, you can create a File Archiving or File Protection backup job to move the SQL backup to another destination. This also allows you to use additional backup schemes to create archive backups that can be moved to secure location, using whatever destination is supported by the backup type.

    The SQL backups use .bak files (one for each database). The file name is given a suffix, indicating the day it was created on.

    Once a backup has been created, you can configure how long backups are kept for, how many are kept and how much space to allocate using the Manage > Destination menu on the Jobs tab.

    Configure your backup destination, and click Next.

  9. Notifications
    Once a backup job has completed, BackupAssist can send an email to inform selected recipients of the result. This email notification is enabled during the creation of the backup job, but you must also provide your mail server settings so that the notifications can be sent. To learn how to configure your mail server for BackupAssist, see Email server settings.

    Notifications

    To enable email notifications:

    1. Select Add an email report notification.
    2. Enter recipients into the Send reports to this email address field.
    3. Enter recipients into the Also send reports to this email address field. You can then use the drop down list to select the condition under which the email is sent.
    4. If you want to know when a backup media has been changed, select Add an email reminder notification and enter the email addresses to send the notifications to.
    5. Click Next.

    To learn more, see Manage : Notifications

    Note: After the backup job has been created, you can modify the notification's recipients, set additional notification conditions and include print and file notification types. To learn more, see Email address list

  10. Prepare media.
    SQL Server Protection backups use fixed media only. Media preparation applies to removable media only, therefore this step will be skipped.
  11. Name your backup.
    Provide a name for your backup job, and click Finish.

Your SQL Server Protection backup job has now been created.

Note: BackupAssist's reports can be used to review the results of each backup job. Backups should also so be checked by performing a test restore. Regular test restores should be part of your backup solution.

Warning: If a server or operating system is damaged, you may want to perform a recovery. A system recovery requires a bare-metal image backup. For this reason, you should consider scheduling an System Protection backup of your SQL Server as part of your backup plan. For example, you run a Transactional backup though the day and a System Protection backup each night.

What's next