Home > UltraBac Administrator Guide > UltraBac Agents > 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 and later.
|
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:
|
Before backup, the SQL server should be registered in the SQL server list:
From the Manage tab, click "Agents"/"SQL Agent."
Fig. 1 - SQL Agent global default options.
Click "Edit Server List."
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.
Fig. 2 - Editing the SQL server list.
Click "Add."
Repeat steps 3 and 4, as needed, for each server to be backed up.
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. |
After registering all SQL servers to be backed up, create a backup set:
Launch the Backup Wizard by selecting the Backup tab and clicking "New."
Click "SQL Agent" to highlight the option and click "Next."
Highlight the server to be backed up and click "Next."
If the SQL server required isn’t displayed (it references the SQL server list), either type in the name of the SQL server\instance or click the "Discover" button to automatically detect the SQL servers in your network.
Fig. 3 - Select MS SQL server/instance to be backed up.
Set the Backup Type from the drop-down box.
Check the Security Options and click "Next."
Fig. 4 - SQL Agent Backup set options.
The Local SQL optimization settings appear as "Block size 64" and "Transfer multiple 24" by default. These are the recommended settings.
Select a radio button under Other options. "Use VDI" is automatically selected by default.
Use VDI – MS SQL 2000 and newer.
Use Volume Shadow Copy – MS SQL 2008 and newer.
Use NamedPipe – MS SQL 2000 only.
|
NOTE: If you will require the restore be to a .BAK file and not directly back to the database, then you will need to use VDI.. |
Click "Next."
Type a description for the set as it will appear in the backup log and click "Next."
Click "Finish" to load the SQL set into the file viewer.
Fig. 5 - SQL backup set loaded in the File Viewer.
These options modify the way UltraBac handles SQL data during backup.
Specifies the method used to search for and enumerate SQL servers:
Local Search Only – Displays only the servers listed in the local "Client Network Utility."
Network Search Only – Queries and displays all SQL servers on the LAN.
Local and Network Search – Displays all servers listed in the local "Client Network Utility" and all servers queried on the LAN.
Backup Type – Specifies the type of backup to be performed:
Full – Backs up all selected databases and transaction logs.
Incremental – Backs up and truncates the transaction logs.
Differential – Backs up the transaction logs, but does not truncate.
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:
Windows security – Uses the accounts specified in the UltraBac accounts under "Manage"/"General"/"Authentication"/"Accounts" during the SQL backup.
SQL security – Uses the specified SQL security account during the SQL backup.
Other Options:
Use VDI – Forces UltraBac to use the "VDI" protocol during backup.
Use Volume Shadow Copy – Forces UltraBac to use the "Volume Shadow copy" protocol during backup.
Use Named Pipe – Forces UltraBac to use the "Named Pipes" protocol during backup.
Truncate Log – Truncates the transaction logs after the selected backup operation is complete. If this preference is not checked, the transaction logs will NOT be truncated.
|
NOTE: If you will require the restore be to a .BAK file and not directly back to the database, then you will need to use VDI.. |
To restore an SQL database:
Launch the Restore Wizard by selecting the Restore tab, and selecting the index source.
Select the objects for restore.
Click "Action"/"Restore this Backup."
To restore the SQL database to the original location/server, click "Next" at the "SQL Agent Restore Options" screen.
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.
Fig. 6 - SQL Agent Restore Options.
Restore Destination – Drop-down menu lists all servers registered in the local Client Network Utility.
Security Options:
Use Windows security – Uses the current login account for restore.
Use SQL security – Uses the specified credentials for restore.
SQL Options:
NORECOVERY – Equivalent to the NORECOVERY option in Microsoft's SQL Server.
REPLACE – Used when restoring a database to a remote SQL server, when the same database name exists on the target machine.
STANDBY – Equivalent to the STANDBY option in Microsoft's SQL Server.
SQL MOVE Parameters – Use to move or rename files when restoring an SQL database.
Custom Restore Parameters – Custom restore parameters can be specified to perform a "point-in-time" restore, or any custom restore available to SQL.
Pre-Version 9.0 Decryption – Prior to UltraBac Version 9.0, the software used Blowfish encryption. Selecting this option will allow you to restore any SQL backups that were made with versions of UltraBac prior to 9.0 that used this type of encryption.
Restore as SQL .bak files – This option will restore the UltraBac SQL backup to a native Windows SQL Backup format.
|
NOTE: For a detailed description of the "SQL Move Parameters," please see the UltraBac Knowledge Base:
|