SQL Server Protection

BackupAssist’s SQL Add-on enables the SQL Protection backup. This backup technology can be used to create two types of backup jobs. A Basic (daily) backup job of the SQL Server or a Transaction backup job that will run throughout the day. These backup types are selected as scheduling options, and therefore referred to as the Basic schedule and Transaction schedule.

Backup destinations

SQL Protection backups are stored in a folder on the local SQL Server. You will not be asked to define a destination media, but you will be asked to provide a directory path.

The default path is C:\ProgramData\BackupAssist <version>\temp\sql\

This location is used for both Basic and Transaction schedule 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.

Transaction logs

SQL uses transaction logs to record all database changes. These logs grow and when they are backed up, all committed transactions are removed. A Transactional backup job will create a full backup then update that backup at user-defined intervals. New information is added to the logs as the databases change. When the first interval backup runs, the log is backed up and cleared again.

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.

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

Hyper-V backups

BackupAssist with 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 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.
• 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 1.
2. Be a member of both the Administrators and the Backup Operators groups.

Warning, if you back SQL Server up from host that is part of a workgroup you may encounter errors such as this error. If you do have problems backing up from the host then you will be required to install an instance of BackupAssist on the guest where you can activate the SQL Add-on along with BackupAssist license.

If you want to back up from a host that is a part of same domain, then there would be no permission related issues.

SQL database restores

Most SQL restores will be for SQL databases used by your business, but an SQL restore can also be used to restore an SQL Server. It is important to understand the difference.

An SQL database restore, is when you use the SQL Restore tool to restore business databases that are used to manage your information. These database restores can be for local or remote SQL servers. To perform a database restore, you tick Selected database in the Select restore type step in the SQL Restore tool.

An SQL Server restore, is when you restore the master database to a local SQL Server. The master database is composed of important system tables and catalogues that are required to rebuild your SQL Server. If you are restoring after a disaster or performing a new installation, you should restore the master database before restoring any other databases. To perform a database restore, you tick Master database recovery in the Select restore type step in the SQL Restore tool.

A full SQL Server restore can only be performed on the local server, and the backup must contain the master and system databases. If you need to restore the master database to a remote SQL Server, you should install BackupAssist on the remote server. The installation does not need to be licensed if you run SQLRestore.exe from the BackupAssist installation directory.

For information on what databases are system databases, see the System Databases section.

Best Practice backups

These suggestions will help you select the best set of BackupAssist technologies to protect your SQL Server. Your decisions should reflect your backup, restore and recovery requirements.

The SQL Add-on creates two types of backups: a database backup at the end of each day (Basic schedule) and a transactional backup throughout the day. At a minimum, you should have a daily database backup. Transactional backups should be used if you have databases that are often changing and critical to your business.

If your Windows Server’s operating system will not start, or the physical server is lost or damaged, you can rebuilt the server and its operating system, and then restore the SQL Server, but best practice is to have an image backup so you can perform a bare-metal recovery. For this reason, you should include a System Protection backup of the Window Server that SQL Server runs on, as part of your backup plan. 

The SQL Add-on saves the backup on the local SQL Server. It is best practice to have archive backups in a secure location that are retained for extended periods. For example, weekly and monthly backups using remote or removable media. This can be done using File Protection for File Archiving.

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, where you are planning to create a Basic or Transactional backup job. 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:

The BackupAssist SQL 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. Go to Start > All Programs > SQL Server.

2. Select the SQL Server Configuration Manager.

3. Enable TPC/IP and Named Pipes for both Client protocols and Protocols for MSSQLSERVER.

The below screenshot shows the enabled settings:

Server Configuration manager - SQL settings

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

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

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

The below screenshot shows the enabled settings:

SQL Server Management Studio

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

SQL Server Protection will back up selected SQL databases on local and remote SQL servers. If you want to be able to perform a full SQL Server restore, you will need to include the master database and the system databases in the backup job.

To create an SQL Server backup job:

1. Select the Backup tab, and click Create a new backup Job

2. Select SQL Server Protection

If this is the first time you have created a backup job, you will be asked to provide a Backup user identity if one has not been defined. See the section above, BackupAssist settings , for guidance.

3. Selections

The selections screen is used to locate your SQL servers, authenticate them and select the databases to be backed up.

a. Server selection

BackupAssist will scan your local machine for SQL servers. Any servers found will be selectable from the drop-down list. Select connect.

To select an SQL Server on your network, enter the name of the SQL Server or its IP address into the Server field. Select connect .

SQL Server connection

b. Server authentication

When you select Connect, you will be prompted to provide login credentials using the BA user identity, another user account (such as a Windows account), or an SQL Server user account.

SQL Server authentication credentials

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.

c. Database selection

A list of databases will be displayed for each SQL Server. The databases displayed will be both 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. Select the databases you wish to backup. In the example below, user databases are circled in purple.

System databases are used by the SQL Server to operate the database. If they are included in a backup, that backup can be used to perform a master database restore. All SQL backup plans should include a backup cycle that includes system databases so that your SQL Server can be recovered

For a list of system databases, refer to the section, System databases.

Select the databases to back up, and click Next.

In the screen shot , the three system databases are outlined in blue, and the three user databases are outlined in red.

SQL Server backup - Server and database selection screen

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

5. Schedule

There are two schedules available for SQL Server Protection, Basic and Transactional.

• Basic runs a backup of the selected SQL databases. A backup is run once per day at the time set in this screen.
• Transactional 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 recover data. Each day's database backup is saved in the same location along with each transaction log.

This provides the functionality of an incremental backup because SQL can use the transaction log to recover data. Each day's database backup is saved in the same location along with each transaction log.

The schedule can be changed after the backup has been created, using the Manage > Schedule menu on the Backup tab.

Select an appropriate schedule. Click Next.

This screen shot shows a Transactional schedule. For a Basic schedule, there is only a start time.

SQL Server Protection - schedule selection

6. Set up destination.

A local path will be provided. You can change this path to another location on the SQL Server’s local drive. 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 Backup tab.

Mail Server: If you have not configured an SMTP mail server for BackupAssist, you will be prompted to provide those details after the backup destination step has been completed. See the BackupAssist settings section for guidance.

7. Notifications

Once a backup job has completed, BackupAssist can send an email to inform selected recipients of the result. This email notification can be enabled during the creation of a backup job, if the mail server has been configured.

To enable email notifications:

a. Select, Add an email report notification.

b. Enter recipients into the Send reports to this email address field.

c. Enter recipients into the Also send reports to this email address field. You can then select the condition under which the email should be sent, using the drop-down box.

SQL Server Protection - backup job Notifications

After a backup job has been created, you can modify the notifications by adding and removing recipients, setting additional notification conditions and including print and file notification types.

To learn more about notification options, see the BackupAssist Backup Tab Whitepaper.

8. Prepare media: SQL Server Protection backups use fixed media only. Media preparation applies to removable media only, therefore this step will be skipped.

9. Name your backup: Provide a name for your backup job, and click Finish.

Your SQL Server Protection backup job has now been created.

Important: Once a backup job has been created, it should be reviewed and run using the Manage menu. This menu provides additional options to configure your backup. See the section, SQL Server Protection backup management , for more information.

Important : Once a backup job has been run and a backup created, a MANUAL test restore should be performed to ensure the backup is working as intended. To perform a test restore, refer to the section , Restoring an SQL Server Protection backup.

BackupAssist provides an inbuilt SQL restore tool that can be used to restore SQL databases to both local and remote SQL servers. If the backup used a transactional schedule, the databases can be restored to a specific point in time.

Before you begin a restore, it is important to know what kind of restore you want to perform. A restore of your databases or a restore of the SQL Server. The option is presented in the Select Type step in the SQL Restore tool.

Only a backup that includes all of the system databases, including the master database, can be used to restore the full SQL Server, also known as a Master database restore. The master database is composed of system tables and catalogues required to rebuild an SQL Server. You must select these system databases from the database list when your backup is made. This can only be performed on the local SQL Server

To restore data from an SQL Server Protection backup, start BackupAssist and follow these steps:

1. Select the Restore tab

The Restore tab has a Home page and a Tools menu. The Home page is the default screen and the recommended starting point for performing a restore.

2. From the Home page, select SQL.

3. The Home page will now display all backups that contain SQL data.

• If you have SQL VSS backups created using File Protection or File Archiving, they will appear with the Restore Console.
• Your SQL Server Protection backup will be associated with the SQL Restore tool. These backups are represented by the title Local and Remote via Add-on.

Restore Home page - results screen

4. Select SQL Restore.

This will open the SQL Restore tool. Most SQL restores will be for SQL databases used by your business, but an SQL restore can also be used to recover an SQL Server. It is important to understand the difference.

a. An SQL database restoration: The databases restored are the business related databases that are used to information.

b. A master database recovery: BackupAssist can restore a master database to a local SQL Server. The master database is composed of system tables and catalogues required to rebuild an SQL Server. You must select these system databases from the database list when your backup is made. It is advised that you restore the master database before any other databases.

5. Select Server.

This screen is used to locate and authenticate the SQL Server that you will restore to.

The databases you wish to restore must already exist on the SQL Server. If they do not, you can choose to either restore the master database first, which will result in recreating all databases on the server, or to manually recreate each database you wish to restore. If you are restoring your SQL Server after a disaster or new installation, it is advised that you restore the master database before restoring any other databases.

a. Server selection: Select the SQL Server to restore to.

Restore a local Server: The console will display a list of the SQL servers on your local machine. If an SQL Server is present but not running, you can click Start . Any servers that cannot be started may indicate a fault with the server itself. You can only restore to SQL servers that are running.

Restore a remote server: Any remote SQL servers detected will appear in the drop down list. If the server is not shown, select the Restore a remote server button, and enter the name of the SQL Server or its IP address.

Select the SQL Server to restore to and click Next.

SQL Restore tool - server selection

b. Server authentication

When you select Next, and you will be prompted to provide logon credentials.

• Use integrated authentication : Use the BackupAssist user identity.
• Specify user identity : Either the BackupAssist user identity, another user account (such as a Windows account) or an SQL Server user account.

The authentication account, the BA account (if different to the account used for this backup) and the SQL Server's service account, all need access to the backup destination. Some SQL servers are configured to accept only SQL authentication.

6. Select Type

This screen is used to identify the type of restore you want to perform.

• Master Database recovery: Only select this if your SQL database is damage, missing or needs to be replaced / recreated. This option will restore the system databases that the SQL Server uses to operate. This is not for user or business databases; it is only for SQL recovery scenarios.

It is advised that you restore the master database before any other databases.

To restore the master database you need to have BackupAssist installed on the server itself.

• Selected Databases: Select this option to restore your user / business databases to any point in time for which you have backups.

Only select both if you wish to recover your SQL database, as well as restore your user databases.

7. Select backup file

If you selected Master database recovery in step 6, you will be asked to select a master database to restore. A list of backups containing masters databases will be listed for selection.

8. Master restore

If you selected Master database recovery in step 6, you will now be asked to start the master database restore. After the master database recovery has finished, you can continue with the SQL database restore steps.

9. Find backups

This screen is used to enter the location of your SQL database backup files. This is typically the SQL directory used in your SQL backup job.

Browse to the backups' location or type in the path and press Add. If the location contains SQL databases, a confirmation tick and message will appear below the list.

Add the location of your SQL Server Protection backups, and click next.

SQL Restore - SQL backup location selection

10. Restore setup

You will be presented with a list of databases that can be restored using the backups identified in step 9. The screen is used to select what databases to restore and what time to restore them from.

Databases can be restored to a specific point in time if the Transactional scheme was selected (during the Schedule step) when the backup job was created.

SQL Restore - backup selection

a. Select the time that you want to restore your databases from using:

• The Restore time field: Edit the data and time shown or select a date from the drop-down calendar, and select Search. This point in time will apply to all databases listed.

The backups that are closest to the selected time will appear next to each database, and show the time that the backup was made.

• The Set restore time button: This button will select a restore point for only that database.

Select a date from the Calendar. Dates that have backups will be indicated in bold.

Select a time range, will show a list of time ranges available for that date. You can restore the database to any point in time between any of the time ranges listed. Select a time range from the list.

Select a time from within that range using the field at the bottom of the window.

b. Select the databases that you want to restore using the tick-box next to each database, or the Select all option.

• If the restore time specified is not available from the supplied backup files, a warning icon will appear to the right of the database/ The closest available time to that specified (that the database can be restored to) will be indicated.
• If the restore time selected is available, a success icon will appear to the right of the database.

c. Click Next.

11. Perform restore

Review the restore job settings.

Select Start restore to the right of the window to restore all databases selected.

Click Next once the restore process has completed.

SQL Restore - Start restore screen

12. Perform full backup

After restoring an SQL database, future transaction log backups cannot be used to restore a database to a specific point in time until a full backup runs. It is therefore recommended that you now run a full backup of the databases that were restored.

Click Start full backup.

Full backups of these databases will be stored in the same folder as the backup files you just restored from.

If your backup schedule already includes a full backup as the next SQL backup and you are certain you will not need to restore these databases to a specific point in time between the restoration and the next full backup, you can check Skip full backup and click Finish.

Your SQL Server Protection restore has now been completed.

Once you have created a backup job, you can modify the settings and access advanced configuration options using the management screen.

To access the backup management screen:

1. Select the BackupAssist, Backup tab.

2. Select Manage from the top menu. A list of all backup jobs will be displayed.

3. Select the backup job you want to modify, and select Edit.

4. Select the required configuration item on the left. Key configurations are described below.

To learn more about the backup management options, see the Backup tab whitepaper.

Manually running a backup job

All new and modified backup jobs should be manually run to ensure they work as intended.

1. Select the backup job, and select Run.

2. You will be prompted to Rerun a past backup or to Run a future backup now.

3. When the backup job starts, the screen will change to the Monitor view.

4. Once the backup has been completed, select the Report button and review the results.

SQL Servers

Use this screen to change the databases that have been selected for backup, and to remove and add SQL servers.

Scheduling

Selecting Scheduling will display your Scheduling options. You can use this screen to change the default time and days of your scheme's daily backups. If you are running a transactional scheme, you can also modify the Time between backups. The default time is every 15 minutes.

Select a new Schedule: This will display the Basic and Transactional backup schemes that you chose from during the creation of your backup job. You can change the current scheme using this option.

• Basic: Runs a full daily backup of your SQL databases. A backup is run once per day at the time set in this screen.
• Transactional: 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 the functionality of an incremental backup because SQL can use the transaction log to recover data. Each day's database backup is saved in the same location along with each transaction log.

For additional information on the Scheduling screen, please refer to the Backup tab whitepaper.

Concurent backup

This feature allows two backup jobs to run at the same time.

Concurrent backup combinations:

• Two File Archiving backup jobs can run at the same time if both have Enable concurrent backupselected.
• An SQL Protection or Mailbox Protection backup job, with Enable concurrent backup selected, can run concurrently with a System Protection, File Protection or File Archiving backup job. The File Archiving backup job does not need to have Enable concurrent backup selected. (System Protection and File Protection do not have an Enable concurrent backup option).
• An SQL Protection and a Mailbox Protection backup job can run at the same time, in any combination, if both have Enable concurrent backup selected.
• In all cases, only two backup jobs can run concurrently.

Concurrent backup considerations:

• If two concurrent backups are scheduled to start at the same time, one backup will start first and begin preparing the job. Once the preparation phase has completed, the second backup will start
• If a third scheduled backup job has 'Enable concurrent backup' selected, it will be queued and run once one of the two existing concurrent backup jobs has finished.

Concurrent backup limitations:

• Only two backup jobs can run concurrently.
• Concurrent backups cannot write to the same destination device (e.g. local drive, NAS, RDX etc.).
• If another backup job is already running when the concurrent backups are scheduled to start, then one of the concurrent backups will start if it meets the criteria defined in the concurrent combinations section.
• A backup job cannot run concurrently if it is backing up a Hyper-V environment or an Exchange server using VSS (VSS enabled).