ApexSQL Backup application components
ApexSQL Backup is a powerful tool used for managing SQL Server database backup and restore jobs across multiple servers. The application consists of three main components:
User interface – This is the main part of the application, that allows the user to create, schedule and execute various backup and restore jobs. The user interface is also used for job monitoring. Only one user interface installation is needed for the application to function properly, but should the need arise, more can be installed on multiple machines.
Central Repository Database – This is the database that is created on specified instance of SQL Server. Its main purpose is to store the information that is necessary for communication between user interface and one or more agent services. It also saves the login credentials that are used for connecting to various SQL Server instances. Only one central repository database is needed to manage any number of servers.
ApexSQL Backup Agent Service – This is a windows service that allows auditing of SQL Server instances on the local systems. Not to be confused with SQL Server Agent. One ApexSQL Backup Agent needs to be installed for every server machine that runs any number of SQL Server instances.
All of the components must be installed properly in order for the application to work. Depending on user’s needs and environment, there could be multiple setup scenarios. The entire setup can be installed on the local machine, and used as such, but if remote servers also need to be managed, agents must be installed on them too.
Installation of ApexSQL Backup user interface
To install ApexSQL Backup user interface on a local machine, perform the following steps:
Download the installer for the latest version here
Run the downloaded exe file to start the installation process
When running the ApexSQL Backup installation, be sure to use windows user account that has full permissions in the ProgramData folder. Usually, full permissions in ProgramData folder are enabled by default for most users, but this mostly depends on system security settings. For more info, check FAQ section of the article.
Select the Install ApexSQL Backup radio button, and click Next. This step starts the installation of the ApexSQL Backup user interface.
Read the End user license agreement. If you find it acceptable, select I accept the agreement, and click Next to proceed.
Choose the installation path for ApexSQL Backup, and click Install. The installation process should be completed in less than minute.
Close the installation when done. The installation of user interface component is complete.
Installation of central repository database
To install central repository database, perform the following steps:
Running the application for the first time will automatically start the installation of the central repository database. The SQL Server instance that will host the central repository database needs to be selected. Choose the SQL Server instance, authentication type, and SQL Server login for the respective server. The login that is used for this purpose must have sysadmin privileges for the SQL Server instance.
Both Windows and SQL Server authentication can be used when establishing the central repository database connection. However, it is strongly recommended to use SQL Server authentication for this purpose. This way, it will be possible to access the central repository, and manage backup jobs even from the computers that are outside of the domain (from the home computer for example).
Click OK to finish the installation of central repository database. From now on, the entered credentials will be used automatically by the application and backup agents to connect to the central repository database.
If there is a need to change the login that is used to access central repository, click on the Central repository button in Configurations group, and enter credentials for another login (the new login must be defined on SQL Server, and must have sysadmin privileges for respective server).
Installation of the ApexSQL Backup agent
In order to be able to manage a SQL Server instance on any machine, Apex SQL Backup Agent needs to be installed first on that machine. The agent can be installed automatically through the user interface, by adding any SQL Server instance from local or remote server. If for some reason automatic installation fails, there is an option to perform manual agent installation. In this case, the ApexSQL Backup installation file is copied or downloaded to a machine, and agent is installed using the file. When the agent starts running on the server, all SQL Server instances on this server can be easily added to the server list of the application.
To start the automatic installation of ApexSQL Backup Agent:
Add the local or remote server instance that needs to be managed by the application. To do so, click on the Add button on upper left.
Select the server instance that needs to be managed, and the login that is used to access the server instance. Select the authentication type for the login, and enter username and password if SQL Server authentication is selected. Both local and network servers can be added this way. If the network server that is added belongs to the same domain as local server (that runs the application interface), you can use Windows Authentication. If this is not the case, it is recommended to use SQL Server authentication. The login that is used to add the SQL Server instance does not necessary need sysadmin privileges. It does need the privileges to backup and restore databases. In this example, windows authentication is selected to install the agent on local machine.
Upon entering the login credentials, the application checks for agent availability. Since no agent is installed yet, backup agent installer starts automatically. It is important to choose the right Windows Account type that will be used to install and run the Backup agent. Be sure that this account has full read/write permissions for the ProgramData folder. This account is also used for SQL server access, if Windows authentication is selected. For this installation, User account will be selected.
Enter the Windows account username in required format (ServerName\Username), and type the Windows logon password for this user. Click OK to proceed.
If the agent is installed on a remote server, an additional step is required. Specify the Windows user account that will be used to start the installation of the agent on remote server. Be sure to use the account that has the permissions to install new programs.
If ApexSQL Backup agent is installed successfully, the added server instance should be displayed on the server list on the left, along with its databases.
If there are multiple SQL Server instances on the same server machine, they can be added to the list as well. Just repeat the steps 1 and 2 for every server instance that needs to be added. Since the agent is already installed on the machine, there is no need to install it again. Only one agent is required per machine to manage any number of SQL Server instances.
If for some reason, automatic installation of the agent fails, there is an option to perform manual installation. In this case, the ApexSQL Backup installation file is copied or downloaded, and the agent is installed using this installation file. When agent service starts running on the server, all SQL Server instances on this machine can easily be added to the server list of the application.
To install ApexSQL Backup Agent manually:
Run the ApexSQL Backup installer on the server machine.
Select Install ApexSQL Backup Agent on local server radio button.
Accept the Terms and Conditions
Select the Windows Account that will run the ApexSQL Backup Agent. Enter the username and password for the account. Make sure that this account has full permissions over ProgramData folder.
Select the installation path: type it manually, or select file browser to specify the path.
Finish the installation, and exit the settings. There is no need to install the whole ApexSQL Backup application on the server machine, only the Agent.
Check if ApexSQL Backup Agent service is running. Go to Control Panel/Administrative Tools/Local Services, and see if the Agent is running. If not, start the service. If the ApexSQL Backup Agent Service is not on the list, try installing it under different windows account. If the service is running, the installation was successful. All SQL Server instances from this machine can now be added to the server list of the application.
There are several problems that might occur due to improper installation, or inadequate permission settings:
Problem: A red X is displayed on the icon for SQL server instance when trying to connect to a remote server.
This problem usually occurs when the communication between the components does not function properly. There are multiple solutions for this problem, depending on the circumstances.
Solution 1: The ApexSQL Backup Agent service is not running. It did not start properly after the reboot, or it stopped running for some other reason. To fix this, go to Control Panel/Administrative Tools/Local Services, and locate ApexSQL Backup Agent Service. Check the status of the service, and make sure it is set to started (click on Start the service). To prevent this from happening in the future, set the startup type of the service to Automatic. If the ApexSQL Backup Agent Service is not on the list, install it again. As soon as the agent service is running, click the refresh button, and the SQL Server instance(s) should be available again.
Solution 2: The ApexSQL Backup Agent service is running, and has the automatic startup type set, but the problem still occurs. In this case, the account that is running the ApexSQL Backup Agent Service does not have full permissions over ProgramData folder. Go to Control Panel/Administrative Tools/Local Services, locate the agent service, and check the account that is running the service. Change the permissions over ProgramData folder for this account (see FAQ for the instructions), or select another account that has full permissions over ProgramData folder. Restart the agent service to save the changes.
Solution 3: This error could also occur when trying to connect with the agents on the remote servers, and Windows authentication is used for the central repository connection on the local server. This way, agents that are running under different accounts on remote servers try to connect with the central repository using these same (remote server) Windows accounts.
The best way to solve this problem is to use SQL Server authentication type when setting the connection to central repository. Just make sure that the SQL Server login that is used for this purpose has sysadmin privileges on the SQL Server instance that is hosting the central repository database. To do this, see the step 3 in “Installation of central repository database” chapter.
Problem: Getting the error “Sysadmin credentials required” when trying to install Central repository database.
Solution: Username and password that are provided belong to the SQL Server login that does not own Sysadmin credentials. In order to install central repository database, the login with sysadmin permissions must be used. Either grant sysadmin permissions to the respective login, or choose another login that owns the required permissions.
Problem: Getting the error: “The Central repository database is currently unavailable. The requested operation cannot be completed”.
Solution: The application is trying to establish the connection with the central repository database by using the credentials belonging to login that does not own sysadmin privileges. The permissions for the login may have been changed in SQL Server in the meantime. To fix the problem, revoke the sysadmin permissions to the login. Alternatively, use another login with sysadmin permissions to connect to the central repository. In Configurations group, select Central repository button, and enter the credentials of the new login.
Q: How do I set permissions for the ProgramData folder for a certain account?
A: To check the permissions for an account to be able to access ProgramData folder, follow the steps:
Navigate to root of the system drive (C: in most cases), open Organize tab, choose Folder Options, and select view tab
Make sure to enable option “View hidden files”, and to disable option “Hide protected operating system files” as shown on the picture. ProgramData is a hidden folder by default, and cannot be edited unless these options are set.
ProgramData folder in the root of the system drive should now be visible. Right click on ProgramData folder/Properties, and open Security tab.
Make sure that the account has all listed permissions, as shown on the picture. If account is not listed under Group or user names, click Add… to add it to the list. Specify permissions for the added account.
Enter the ProgramData folder, locate ApexSQL folder, and check properties for it.
Check the permissions for the accounts, the same way as in the step 4.
When done, restore the Views options in Folder options.
Q: I installed ApexSQL Backup application and central repository database on the server in my office. I need to maintain the company’s servers frequently using the backup application, but I am often out of the office. Can I manage the backup jobs from a home computer or laptop?
A: Yes, you can. You need to install the ApexSQL Backup application to your home computer, and link it with central repository database in your office. To do so, click on Central repository button from Configurations group, choose the server that hosts the central repository, and provide the credentials of any login that has sysadmin privileges on the SQL Server in your office. Since your home and your office computer do not belong to the same domain (most likely), it would be best to use SQL Server authentication type for the login that is used to access the central repository database. However, it is not possible for two users to send the information to the agent services at the same time – only one user interface can be used at any given time.
Q: Do I have to install the central repository database on a local machine?
A: No. Central repository database can be installed on the remote server. However, the ApexSQL Backup user interface has to be connected to the central repository in order to be able to manage any of the SQL Servers.
Q: I cannot find the answer for my problem in this article. What should I do?
A: You can always contact ApexSQL tech support by sending the email on firstname.lastname@example.org, and we will solve the problem as soon as possible.