Introduction

The GetDBApp (ESI.Octopus.GetDBApp.exe) program allows to download the Octopus database from a database hosted on our servers. The program can be automated using the Windows Scheduled Tasks to download the database everyday. Note that the Octopus database is in SQL format.

This article explains how to use the GetDBApp program.

Prerequisites

Before looking at the technical aspect, make sure you have access to the option. 

From Octopus, go to Tools > Get database and see if the option is greyed out.  

If the option is greyed out, make the request to activate the option to the Octopus Service Desk. Once the option is available, continue with the next steps. 
* The permission "Execute the GetDatabase program" permission is required to download the file.
 

Before using GetDBApp, you need to have :

• A server running SQL Server 2014 Standart SP1 as a minimum version.

  • Enable this feature: Full-Text and Semantic Extractions for Search.

• A user that is authorized to delete and create databases on the SQL server.

• A shared directory on the SQL server to which GetDBApp will download the backup file from the Octopus server. Ex: (\\MySqlServer\OctopusBackups\).

• A computer which will execute GetDBApp (included with the Octopus installation).

• For versions 4.2.190 and later :

  • ​Install the latest available version of Microsoft SqlPackage: Download and install SqlPackage.

  • ​Add the installation folder (Corresponding the the Windows version)  in the Windows system variable  "PATH" in the system variables.

    • ​Example of installation folders:  "C:\Program Files\Microsoft SQL Server\150\DAC\bin" or "C :\Program Files (x86)\Microsoft SQL Server\150\DAC\bin".​

    • ​Right click on the Computer icon of your desktop (or in the left panel of the file explorer) and in the contextual menu, goto Properties.

    • In the opened windows, click on the link Advanced system parameters on the left.

    • In the new window, click on the Environnement Variables button.

How does it work?

When GetDBApp program is executed, the following steps are performed:

1. Connect to the Octopus server
2. Download the Octopus DB backup to a local temporary folder
3. Copy of the downloaded file to the the shared directory on the SQL server
4. Disconnect from the Octopus server
5. Decompress the downloaded file to the shared directory identified in the prerequisites
6. Connect to the SQL server
7. Remove the existing SQL database
8. Creation of the Octopus database from the downloaded file in the default SQL data folder
9. Disconnect from the SQL server
10. Delete the downloaded and decompressed files in your shared folder(an option exists in the command line to avoid erasing the file, /KeepDownloadedBackup )

How to use GetDBApp

GetDBApp is a command line type of program (DOS).  Is is designed to be easily automated (the section for Use GetDBApp from a command line describes how to automate the GetDBApp execution). 

• The complete name of the GetDBApp program is : ESI.Octopus.GetDBApp.exe
• It is located in the local Octopus directory of your computer (C:\Program Files\Octopus) or in (\%homepath%\AppData\Local\Octopus).
• Here are some example of where you can find the Octopus folder : C:\Users\slevesque\AppData\Local\Octopus.

Parameters

There are 7 available parameters, 4 of which are mandatory to execute GetDBApp :

Mandatory Parameters

• /Login
  • Octopus username
• /Password
  • Octopus password
• /ConfigFilePath
  • Path (including filename) of the configuration file

• /Team

  • The number of the team to be updated. By default, the IT team is 1

The next section explains how to prepare the configuration file.

Optional Parameters

• /KeepDownloadedBackup
  • Allows you to keep the downloaded file (backup) in your shared folder after it was restored on your SQL server. Without this parameter, GetDBApp will delete it.  Depending on the datacenter hosting your database, your backup file could be missing a timestamp. If this is the case, you will have to rename the file as to not prevent the next execution of GetDB – which would fail if the file already existed.
• /LogFilePath
  • Will move the log file. By default, this file named GetDBApp_AAAAMMJJ_HHMMSS.log  will be located in the Octopus folder, where you find the GetDBApp executable. If you move this file from the Octopus folder, make sure the folder exist (the system cannot create the folder). Also, the path needs to contain the name of the file.
• /NoRestore
  • Allows to download the file without restoring it.
• /PurgeLogs
  • Can automatically erase old log files
  • The parameter is used to determine how many days of log files that you want to keep.
  • For more information, see the Octopus Tools | Maintenance of log files.

Operation Log Files

File generated by the execution of the GetDBApp program:

• GetDBApp_AAAAMMJJ_HHMMSS.log 
  • Log file generated automatically each time the program runs

Configuration File (XML)

The configuration file is a text file which contains the information about the SQL server, the shared folder and other parameters required by the program. This file uses the XML format.

• Open the following template in Notepad : GetDBAppConfiguration.txt
• You need to modify the section highlighted in yellow ONLY. The tags are described in the next section.

Tag Description

• <serverBackupLocationUNC>\\MySqlServer\OctopusBackups</serverBackupLocationUNC>
  • Backup server name
    • Replace \\MySqlServer\OctopusBackups with the name of the shared folder where the backup of the database will be saved
• <serverName>MySqlServer</serverName>
  • SQL.server name
    • Replace MySqlServer by the name of the server where the database will be restored
• <databaseName>OctopusDatabase</databaseName>
  • Database name
    • Replace OctopusDatabase by the database name where the Octopus DB will be restored
• <authenticationMode>sql</authenticationMode>
  • Authentication mode
    • The authenticationMode tag can be integrated. In this case, the username and password tags will not be used. This mode can only be used if it is supported in the SQL server configuration. If this is not the case, then use SQL and make sure that you have the username and password of the database
• <username>sa</username>
  • SQL user name of the database owner (DB_Owner)
• <password>password</password>
  • SQL user password.

Execute GetDBApp from a command line

To run GetDBApp, you need to open a DOS prompt and place yourself in the Octopus folder. Then you need to enter the following command line:

ESI.Octopus.GetDBApp.exe /login:system /password:AbCd123 /ConfigFilePath:C:\Program Files\Octopus\GetDBAppConfiguration.xml /team:1

Automate GetDBApp daily downloads

To automate, refer to the Windows Scheduled Tasks article..

* Every night a new backup copy is generated. This copy contains all the data present as of the previous day 23:59. This copy is made available from 4:00 AM (UTC -5:00).

Troubleshooting

This section describes the ways to help you troubleshoot issues.

• Before anything else...
  • Make sure you are currently running the latest version of SqlPackage. If this is not the case, first install the latest version before submitting the job again.
• RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
  • ​This message is part of the normal process of restoring your database.
• Device dropped.
  • ​This message is part of the normal process of restoring your database.