You first need to stop the services to change your Pyramid repository location.
Then back up your Database and restore it to the destined server.

For each Pyramid server in your deployment edit the config.ini file. Update "Dbhost" to point to the new repository server (see picture).

By default it can be found here c:\Program Files\Pyramid\config.ini and on Linux  /opt/Pyramid/config.ini

If the username and password  will stay the same, there is no need to do anything else. Otherwise, you will need to update the config.ini file with the new user and encrypted password(this applies if only one of them will change).
To accomplish it, follow this article: How to update the Pyramid repository credentials.

1. Download the Java JDBC driver for the data source you want to connect to.
   (Click here for a complete list of the different data sources that CData supports) Run the installer to extract the JAR file.
   
2. To see the list of connection properties you can use, open the CData jar using JAVA.
   To do this:
   Open a command prompt on the Pyramid server and point to the location of the extracted JAR file.

   C:\Program Files\CData\CData JDBC Driver for Microsoft Dynamics CRM 2022\lib

   And where JAVA is installed

   "C:\Program Files\Pyramid\java\bin\java.exe" -jar cdata.jdbc.dynamicscrm.jar

3. Fill in the connection properties that you want to use.
   E.g.

   

   Once all details have been filled in, click on the "Test connection" button to make sure it works.
   Then click on "Copy to Clipboard" to copy the connection string.
    
4. Go to custom connectors in Pyramid (admin>data>custom connectors)
   and add a new one and upload the downloaded driver.
    
5. Paste in the connection string from the CData you just copied

   

   CData RTK is a license key.
   You'll need to contact CData and ask them for a trial license.
    
6. Change all parameters to a relevant name wrapped in curly brackets {} as shown below:
   
   
   The best practice is to use these name conventions as your parameters:
   database, user, password, port, ip
   This is not mandatory but preferred.
    
7. Click on apply and add the custom connector.
    
8. In Add Data Source, add the new data source like any other by filling out all the relevant connection details.
   (admin>data source)

   

For more information about setting up the connectors, please see our Help.

For more information about the CData drivers and their documentation you can refer to this link, choose the driver you need, and then click docs as shown below:

The affected connectors are:

• Apache Drill
• Apache Hive
• MongoDb BI 
• Netezza
• Neo4J BI 

During the upgrade, there will be no error or any indication of connectors being removed, but during normal operation, users querying these data sources or processing any related models will get a connection error, unless they re-add the connectors. (See example images below)

Customers who use these connectors (or plan to use them) will need to download them and add them as custom connectors. Pyramid's connector marketplace offers quick access to the downloads. 

Help resources

• Data Source Setups
• Data Sources and Custom Data Connectors
• Data Sources and Targets Reference
• Custom Connectors

Examples of errors from missing connectors

There is another way to do it via the windows command line, as described in this article: How to Backup and Restore a PostgreSQL database.

Backup:
First, go to your DBeaver application, and then-

Restore:
First, create a new DB so you can restore to it the backup DB

And then-

Please follow these steps in order to create the app:

1. Open on the settings->Setup:
    
2. On the left menu, click on the Apps->App Manager:
    
3. Create a New Connected App:
    
4. Fill in the application details, enable the OAuth Settings, and enable for Device Flow. In the OAuth Scopes select the “Full access(full)” scope:
   
    
5.  Once the application has been created, in the view app panel, you will be able to see your Consumer Key and Consumer Secret:
   
6. Go to the Pyramid Admin Console->Data->Data Sources, and add a new SF data source:
    T
7. To generate the security token, right-click on your user icon on the top right corner, then go to settings. On the left menu - reset security token:
    

Postgres

When using Postgres as the Pyramid repository, to create a copy of an existing environment, you first need to install Pyramid using the same version of the environment you are copying from, then replace the newly created repository with the backup taken from the existing environment.

The first step is to confirm the Pyramid version on the production environment.
You can get to it by clicking on your initials on the tab at the top right-hand side of the screen, as shown below. 

Here, we can see it's Pyramid version 2020.11.030.
In this case, we will run the installer version 2020.11.030 in our test environment.

When running the installer, we will choose a new internal repository or new remote repository > PostgresSQL:

After completing the installation, go back to the production environment and back up the repository.

To create the backup, run the following command in your production server:

"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_dump.exe" -U pyramid -h localhost -p 12130 -F t -f C:\backup\pyramidDbBackup.tar pyramidg2

The default password for the DB is: pyramid

From version 2020.15 and above, the password for the internal PostgreSQL instance will be the name of the DB with ! after the word “pyramid”
example: DB name:pyramid123 password: pyramid!123

The way to change the Postgres password via CMD is with the following commands, which need to run on the server running the Pyramid Postgres service.

1. cd C:\Program Files\Pyramid\postgres\pgsql\bin
2. set PGPASSWORD=CurrentPassowrd
3. psql -h localhost -d DB name -U pyramid -p 12130 -c "ALTER USER pyramid WITH PASSWORD 'NewPassword';"

To get your DB name, go to your pyramid folder and open the config.ini file.
There, under data, you will see the database, and this is the name of your DB.
NOTE: The path to your pyramid folder in Windows is C:\Program Files\Pyramid
and in Linux, it's - /opt/pyramid.

If you are using a version lower than 2020.20 and want to update the password in the config, you need to use the tool and follow the instructions from this link:
How to update the Pyramid repository credentials.

To change the password in Pyramid version 2020.20 or higher, a maintenance tool named "run" can be used. It's located at C:\Program Files\Pyramid\core\maintenance

Now copy the file to your Test environment and run the following command in the CMD to restore the database:

"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_restore.exe" --clean -h localhost -p 12130 -U pyramid -d pyramidg2 C:\backup\pyramidDbBackup.tar

The following article describes How to back up and restore a PostgreSQL database via DBeaver.

After restoring has been completed, you'll need to truncate the server table by running the file you can download here or by connecting to the database via a 3rd party app.

After truncating the tables, restart all Pyramid services, wait a few minutes, and try to log in.

NOTE: you will need to change the data sources so they won't point to production data sources.

If you are using IMDB, you must copy the IMDATA folder from the production server.
The IMDATA folder is located in C:\Program Files\Pyramid\repository\imdata

Ensure everything is working fine and all the reports can be opened without errors.

Now, if you restored the backup to a new instance, the following command should be run on cmd :

1. cd C:\Program Files\Pyramid\postgres\pgsql\bin
2. psql -h localhost -d DB name -U pyramid -p 12130 -c "CREATE EXTENSION IF NOT EXISTS ""uuid-ossp"";"

Or you can use a 3rd party app, like Dbeaver, to run it directly on the database:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"

The above is needed because the Pyramid installer uses this extension when running scripts to update the Pyramid database.
Note: The above command should NOT be run on a local Pyramid Postgres instance.

MS SQL Server and Oracle

When using these DB's as a repository for Pyramid, the process is first to create a backup and then restore it in the destination, then install using the copy of the repository.

You will need the same Pyramid or higher Pyramid versions for SQL and Oracle databases.
For example, if you have Pyramid 2020.05.30, you can run installer build 2020.11.030.

The first step is to create a repository backup from the production environment and restore it on your SQL or Oracle test environment.

After you have restored the repository into your SQL or Oracle test environment, you can run the Pyramid installer and choose "Reuse Pre-existing Repository" > Microsoft SQL Server Or Oracle.

Point Pyramid to your SQL database:

Or Point Pyramid to your Oracle database:

And finish the installation.

Log in to your test environment and make sure you can open the reports.

Make sure to stop all the models and stop all publications.

Once the test environment is working, you need to deactivate its license (admin console -> licensing -> deactivate instance), get the machine key(after reloading the page -> new deployment -> generate machine key), and send it to your account manager to get a test pyramid license.

Each pyramid environment must have its license and not use the same one as another environment.

It can help the Pyramid development team debug cases where the query fails to get the cube metadata.
 

Steps:

In Microsoft SQL Server Management Studio, connect to the analysis server for the relevant cube/model.

Run the below two XMLA queries, 

Send the two queries results to the Pyramid support team for further analysis.
(Below is an example image)

This is the first query to run:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

       <RequestType>DISCOVER_XML_METADATA</RequestType>

       <Restrictions>

              <RestrictionList>

                     <ObjectExpansion>ExpandObject</ObjectExpansion>

              </RestrictionList>

       </Restrictions>

       <Properties>

              <PropertyList>

                     <CONTENT>Data</CONTENT>

              </PropertyList>

       </Properties>

</Discover>

Copy the first result in a notepad, to send it to us.
Now, In the result, please look for this segment:
 

<Database>

              <Name>Pyramid Demo 2013</Name>

              <ID>Pyramid Demo 2013</ID>

              <CreatedTimestamp>2010-12-26T07:04:30</CreatedTimestamp>

              <LastSchemaUpdate>2013-12-03T07:46:23.46</LastSchemaUpdate>

              <LastProcessed>2015-09-01T11:12:36.416667</LastProcessed>

              <State>Processed</State>

              <LastUpdate>2019-03-31T15:34:31.616667</LastUpdate>

</Database>

The <Name> tag contains the name of the model you're trying to open.
The <ID> tag holds a value you'll need for the next query.
Take the value and use it in the <DatabaseID> tag of this next query and run it.
 

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

       <RequestType>DISCOVER_XML_METADATA</RequestType>

       <Restrictions>

              <RestrictionList>

                     <DatabaseID>Pyramid Demo 2013</DatabaseID>

              </RestrictionList>

       </Restrictions>

       <Properties>

              <PropertyList>

                     <CONTENT>Data</CONTENT>

              </PropertyList>

       </Properties>

</Discover>

Copy the second result in the notepad as well and send both results back to the Pyramid support team.

The error indicates that the msolap130.dll file is not registered in the Pyramid environment.

To correct this, open the command prompt as an administrator on each of the Pyramid Windows Connector servers and run the below command:

regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\130\msolap130.dll"

or 

regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\140\msolap.dll"

*You should use the newest one out of the two above.

*This folder path is created by the Pyramid installer, however, the path might be slightly different on your server so before running the command, confirm that the dll file exists and correct the path in the command if needed. 

In case this does not resolve the issue, please contact Pyramid support

1. The database repository.
   If you don't know where it is, browse to HTTP://<Your Pyramid Site>/diagnostic
   • If it's an SQL database, then use SQL Management studio to back it up.
   • If you are using the local Postgres option for your repository,
     see this article here on how to back it up.
   • If you are using Oracle, then set up a backup job to do this.
2. The general folder(used to store publications and printed content),
   Found by default here: C:\Program Files\Pyramid\repository\general *
   If you have more than one Pyramid server in your deployment, then back it up on all Pyramid servers.
    
3. The upload folder (used for ETL's, the Yellow app) that are based on Excel spreadsheets.
   Found by default here: C:\Program Files\Pyramid\repository\upload *
   If you have more than one Pyramid server in your deployment, then back it up on all Pyramid servers.
    
4. If you’re using IMDB(In Memory database), then you should back up the created databases.
   They can be found on the server running IMDB,
   in the folder located here: C:\Program Files\Pyramid\repository\imdata

* If any of the folder locations have been changed from within the config.ini file (C:\Program Files\Pyramid/config.ini), then these are the locations that should be backed up.

1) Login to SQL Management studio and connect to your SQL instance.

Expand Security>Server Roles> and double click on the "Public" role

2) Expand Servers and select your SQL instance. Then tick the box Deny for "View any database"Please note that there are other ways of doing this, or by just setting a deny view permission on specific databases. Lots more information on this topic can be found on the internet. 

Backup

To backup the database, the following command should be run from an elevated command prompt on the server where Pyramid has been installed. 
When opening command prompt, choose "run as administrator"

**In many cases the pyramid DB name will not be pyramidg2. To check what it is, look at the config.ini file that can be found in the Pyramid install directory. If the Pyramid database is not called pyramidg2, then see this article for what the password will be**

{installdir}\postgres\pgsql\bin\pg_dump.exe -U {uid} -h {host_server} -p {port} -F t  -f  "{windows_folder_desktopdirectory}\pyramidDbBackup.tar"  {dbname}

uid is  “pyramid”
port "12130"
dbname is  “pyramidg2”
password  “pyramid”

Example Backup

Assume that the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive for Windows.

On Windows

"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_dump.exe" -U pyramid  -h localhost -p 12130 -F t  -f  "C:\backup\pyramidDbBackup.tar" pyramidg2

On Linux

/opt/Pyramid/postgres/pgsql/bin/pg_dump -U pyramid -h localhost -p 12130 -F t -f /home/master/pyramidDBBackup.tar pyramidg2

If you need to revert back to a previous version of Pyramid, first uninstall* the upgrade version and re-install the previous version. Then delete the database created and restore the backed up one.

To delete the database (stop all Pyramid services first):

{installdir}\postgres\pgsql\bin\psql psql -h {host_server} -d postgres -U {uid} -p {port} -c "drop database {dbname} --force;"

Example Database Delete

Assume the Pyramid server is called "MyBIServer" and Pyramid was installed on the C drive.

On Windows

"C:\Program Files\Pyramid\postgres\pgsql\bin\psql" -h localhost -d postgres -U pyramid -p 12130 -c "drop database pyramidg2 --force;"

On Linux

/opt/Pyramid/postgres/pgsql/bin/psql -h localhost -d postgres -U pyramid -p 12130 -c "drop database pyramidg2 --force;"

Before the database is restored, the database must first be created. To do this use the following example command where the server that Pyramid is installed on is called "MyBIServer"

"C:\Program Files\Pyramid\postgres\pgsql\bin\psql" -h localhost -d postgres -U pyramid -p 12130 -c "create database pyramidg2;"

Restore

{installdir}\postgres\pgsql\bin\pg_restore.exe -h {host_server} -p {port} -U {uid} -d {dbname}  "{windows_folder_desktopdirectory}\pyramidDbBackup.tar"  

Example command to restore the database where the Pyramid server is called "MyBIServer", Pyramid was installed on the C drive and the back is called "pyramidDbBackup.tar"

On Windows

"C:\Program Files\Pyramid\postgres\pgsql\bin\pg_restore.exe" -h localhost -p 12130 -U pyramid -d pyramidg2 C:\backup\pyramidDbBackup.tar

On Linux

/opt/Pyramid/postgres/pgsql/bin/pg_restore -h localhost -p 12130 -U pyramid -d pyramidg2 /home/master/pyramidDBBackup.tar

*When uninstalling a single install of Pyramid, make sure to backup the IMDB databases, whose default location is here: "C:\Program Files\Pyramid\repository\imdata"  

You may also want to backup the publication folder whose default location is here:

"C:\Program Files\Pyramid\repository\general"

Alternative Approach with UI

For those wanting to backup and restore the DB using Dbeaver, the article below describe how to do that: https://community.pyramidanalytics.com/moderate/t/h7hk07w

Check to see if the size of the model. if you were expecting a large file size and the file size is very small, it is usually an indication that the build has failed.

The file default location:
C:\Program Files\Pyramid 2018\repository\imdata     <Your Database Name.irdb>

To resolve, you'll need to upgrade your Pyramid version to the latest and try to create the model again.

If you still encounter issues, please open a support case via our support system.