Steps

  • Take a backup of the database (.bak) with MS SQL Server Management Studio (SSMS)

  • Transfer backup file to Profiz SQL dev server

  • Restore backup to MS SQL Server with MS SQL Server Management Studio

  • Use Procurement client to initialize an empty SQL database to MariaDB

  • Migrate customer’s Procurement data from MS SQL server to MariaDB with DBeaver

  • Make a dump file of MariaDB database either via command line (mysqldump) or DBeaver

  • Transfer dump file to Procurement server at Databalance

  • Create an empty database to MariaDB running at Databalance with HeidiSQL

  • Restore database to MariaDB server running at Databalance server via command line (mysql)

Creating a backup in SSMS

  • Open SSMS on the customer server

  • Expand the server tree

  • Expand Databases

  • Right-click the desired database, select Tasks Back Up...

  • Make sure that the following are selected:

    • Database: the desired database

    • Backup type: Full

    • Backup component: Database

    • Back up to: Disk

  • Click Add, select the destination on disk (and enter a name for the backup)

  • Click OK

Find the file on disk and copy+paste to Profiz SQL dev server at C:\DbMigrations

Restoring the database on Profiz SQL dev server

  • Open SSMS on the Profiz SQL dev server

  • Expand the server tree

  • Right-click Databases > Restore Database…

  • Select Device and click [...]

  • Select Backup media type: File and click Add

  • Select the backup file on disk and click OK

  • Click OK

Initializing a new Procurement database with Procurement client

  • Start Procurement (version for cloud)

  • Initialize an empty Procurement MariaDB database by going to Connection Settings > Tools Initialize Procurement Database

  • Note that the new database is named after the selected SAP Company database (e.g. in the screenshot the new database is named as PFZ_PFZFinal). If the desired company database is not available on the server, create a new database for e.g. SBODEMOFI and rename the database in DBeaver.

Migrating data from MS SQL database to MariaDB

  • Open DBeaver

  • Expand the SQL Server tree

  • Expand the desired database Schemas dbo Tables

  • All tables cannot be exported at the same time. The tables that have foreign key constrains need to be exported separately. First export tables that do not have foreign key constrains. Select all tables except ApprStage, InvoiceLine and StageUsers, right-click and select Export Data.

  • Select the database just created with Procurement as the target container

  • Click Auto assign to map the source fields automatically with the target fields

  • Do another export for ApprStageInvoiceLine and StageUsers.

Exporting the MariaDB database from Profiz dev server to Databalance server

  • Create a dump of the MariaDB via the command line (replace dbname with the database name, e.g. pfz_pfzfinal; place dump in e.g. C:\DbMigrations):

mysqldump -u root -p dbname > dbname_dump.sql

 

  • Enter password when prompted.

  • If you get the following error:

'mysql' is not recognized as an internal or external command, operable program or batch file.

 

  • Run the following:

set path=C:\Program Files\MariaDB 10.4\bin

 

  • Navigate to the created sql file in the file system and copy it.

  • Open connection to the Databalance server.

  • Paste the sql file to C:\Db dumps

  • Before importing the dump file, create an empty database with HeidiSQL.

  • Open HeidiSQL, right-click on the MariaDB server (“Unnamed”) and choose Create new > Database:

  • Enter database name and click OK:

  • Import the database from the dump file via the command line:

cd C:\Db dumps

mysql -u root -p dbname < dbname_dump.sql

 

  • Enter password when prompted.

  • If you get an error:

'mysql' is not recognised as an internal or external command, operable program or batch file.


 

  • Run the following:

set path=C:\Program Files\MariaDB 10.4\bin

 

  • The database is now on the MariaDB server and accessible by Procurement.

  • If the database is very old, run the Database Migration tool in Procurement (Connection Settings > Tools > Database Migration).

Migrating attachments from the old environment

  • The customer is responsible for uploading the old archive to Profiz Drive and the archive zip file should be located at External collaboration/B1CO - B1 Client Operations/_Customer database uploads/DriveUploader.

  • Move the zip archive from Drive to the Procurement server.

  • Unzip the file to location \\cloudloginportal.com\SAPB1\SharedFolders\<Company db name>

  • Define the Attachments path in the database as \\cloudloginportal.com\SAPB1\SharedFolders\<Company db name>\Procurement

  • The first time Procurement is run, it will reorganize the archive according to the new structure.

After this resume from step titled “Create a dedicated MariaDB user for the database” in B1RD-Setting up a new Procurement instance at Databalance-v03-20200415

Resources

https://john-dugan.com/dump-and-restore-mysql-databases-in-windows/