You are here: Using BackupAssist > Backup > SQL Protection

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 backup 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.

Show : Overview of the SQL Continuous add-on

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 select 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.

Show : Hyper-V Environments

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:

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.
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.

Show : Database types

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 create an SQL Protection backup job

Launch BackupAssist and follow the steps outlined below:

Select the Backup tab
Select Create a New Backup Job
Select SQL Server Protection
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
Selections
The selections screen is used to locate your SQL servers, authenticate them and select the databases to be backed up. When you BackupAssist will scan your local machine for SQL servers. Any servers found will be selectable from the drop-down list.

To make your server selection:

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.

Select Connect.
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.

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.

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

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.

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.
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.

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.

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.
Show more
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.
Prepare media
SQL Server Protection backups use fixed media only. Media preparation applies to removable media only, therefore this step will be skipped.
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