SQL Agent

Using the UltraBac SQL Agent, Microsoft SQL can be backed up online, without stopping the services prior to backup. If the SQL services are not running, the backup will not be successful. The SQL Agent must be installed on each of the SQL servers you are planning to back up.

 

UltraBac supports SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012, and SQL 2014. UltraBac no longer supports SQL 2000.

 

NOTE:  The backup account must have either DB ownership or Administrative rights on each of the databases to be backed up.

 

The databases must have the recovery model set to "Full" or "Bulk Logged." Information on changing the recovery type can be found at:

 

http://msdn.microsoft.com/en-us/library/ms189275.aspx

 

NOTE:  For information on configuring deduplication server, please see the UltraBac Knowledge Base:

 

UBQ000278:  Configuring UltraBac Deduplication Server

 

Setup and Configuration

Before backup, the SQL server should be registered in the SQL server list:

 

  1. From the Manage tab, click "Agents"/"SQL Agent."

 

ub_agents_mssql_options.jpg

Fig. 1 - SQL Agent global default options.

 

  1. Click "Edit Server List."

  2. Type the SQL server name into the "Server instance" field, or wait for UltraBac to detect the SQL servers in the network and then highlight the desired server by clicking on it.

 

ub_agents_mssql_editserverlist.jpg

Fig. 2 - Editing the SQL server list.

 

  1. Click "Add."

  2. Repeat steps 3 and 4, as needed, for each server to be backed up.

  3. When all servers have been added, click "OK."

 

NOTE:  SQL database names cannot contain "(" or ")" characters. If a database has those characters in the name, it will create an enumeration error.

Creating a SQL Agent Set

After registering all SQL servers to be backed up, create a backup set:

 

  1. Launch the Backup Wizard by selecting the Backup tab and clicking "New."

  2. Click "SQL Agent" to highlight the option and click "Next."

  3. Highlight the server to be backed up and click "Next."

 

ub_agents_mssql_serverlist.jpg

Fig. 3 - Select MS SQL server/instance to be backed up.

 

  1. Set the Backup Type from the drop-down box.

  1. Check the Security Options and click "Next."

 

ub_agents_mssql_backup_options.jpg

Fig. 4 - SQL Agent Backup set options.

 

  1. The Local SQL optimization settings appear as "Block size 64" and "Transfer multiple 24" by default. These are the recommended settings.

  2. Select a radio button under Other options. "Use VDI" is automatically selected by default.

 

  1. Click "Next."

  2. Type a description for the set as it will appear in the backup log and click "Next."

  3. Click "Finish" to load the SQL set into the file viewer.

 

ub_agents_mssql_databasebrowser.jpg

Fig. 5 - SQL backup set loaded in the File Viewer.

Backup Set Options

These options modify the way UltraBac handles SQL data during backup.

Enumeration Mode Options

Specifies the method used to search for and enumerate SQL servers:

 

UltraBac SQL Agent Backup Options

Backup Type – Specifies the type of backup to be performed:

 

 

Local SQL Optimization – Defines the block size to be used during backup. The default is 64KB. The transfer multiple is 24. In most cases, these preference should not be changed.

 

Security Options – Specifies the account information to be used during the SQL backup:

 

 

Other Options:

 

Restoring a SQL Backup

To restore an SQL database:

 

  1. Launch the Restore Wizard by selecting the Restore tab, and selecting the index source.

  2. Select the objects for restore.

  3. Click "Action"/"Restore this Backup."

  4. To restore the SQL database to the original location/server, click "Next" at the "SQL Agent Restore Options" screen.

  5. At the "Restore Options" screen, click "Restore."

 

NOTE:  When restoring incremental or differential SQL backups, the full backup must be restored first, then all incremental or differential backups must be restored in the order they were backed up.

 

UltraBac also has the ability to restore a database to an alternate server, or the original server, with alternate database and file names.

SQL Agent Restore Options

ub_agents_mssql_restore_options.jpg

Fig. 6 - SQL Agent Restore Options.

 

 

 

 

NOTE:  For a detailed description of the "SQL Move Parameters," please see the UltraBac Knowledge Base:

 

UBQ000205:  SQL Move Parameters