How to change the database from MSSQL to PostgreSQL
The steps mentioned below are applicable only if you migrate the DB from MSSQL to PgSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to PgSQL DB from the existing MSSL DB.
For builds 11303 and below:
Step 1: Stop ManageEngine ServiceDesk Plus service.
Step 2: Take a backup of the existing data present in MSSQL database using the backupData.bat batch file.
From the command prompt, go to [ServiceDesk Plus-Home]\bin directory and execute the backUpData.bat command to start the data backup.
cmd> backUpData.bat --trimmed
where, ServiceDesk Plus Home --> Drive:\ManageEngine\ServiceDesk. This backup will be stored under the trimmedbackup folder in ServiceDesk Plus Home directory.
Step 3: Invoke ChangeDBServer.bat under [Service Desk-Home]\bin folder.
cmd> ChangeDBServer.bat
Step 4: Provide the details of the PgSQL server (i.e.) Hostname, username, and password and click Save.
Hostname: localhost
Port: 65432
Database Name: servicedesk
Username: sdpaadmin
Password: sdp@123
You can ignore any errors you see (like the one below) and click OK in the pop-up.
After clicking on Ok, you should see the below pop-up for which you can click on OK again.
Step 5: Once your are back in the command prompt, type in run and hit enter.
This will load all the modules and will start the application. Once all the modules are loaded, you should see a message "Server started" in the command prompt.
After you see this message, open up a browser and try to access the localhost url that is displayed in the command prompt. If you see the login page, try to login with the default administrator credentials by clicking on the credential listed below.
Step 6: If login action is successful, close the browser and come back to the command prompt and hit CTRL+C which would terminate the batch job that's running. You should see the modules being stopped and destroyed. At the end, when you are prompted for a confirmation for terminating the process, press 'Y' and hit enter.
Step 7: Now invoke restoreData.bat under [Service Desk-Home]\bin folder as indicated below. This would bring up a GUI to choose the backup file. During this point, you will need to choose the trimmed backup that you had taken in Step 2.
cmd> restoreData.bat > 1.txt
You need to choose only the part1.data file while initiating a restore. The system would automatically pick up the other parts in the backup (if exists). You need not manually select part2.data or perform any other action.
Step 8: You will be prompted to enter the backup password while performing the restore. The default backup password is SDP123!. If this doesn't work, it probably means, the password was changed. The application administrator would have received an email when the password was updated from where the password could be fetched.
If you are not aware of the backup password, then you will have to perform step 3 and 4 and change the DB back to MSSQL, start the application, check the Backup Password set under Admin --> Backup Scheduling. Once you have the password, follow steps 3 and 4 and connect the application back to PgSQL DB and try the restore again.
After you put in the password, you will be asked if the restore is performed on the Production Environment. Please choose 'Yes' in the prompt and proceed with the restore.
Step 9: After you see the message "Data Restored Successfully", start ManageEngine ServiceDesk Plus service once. As the application is starting as a service, it would usually take a couple of minutes to load. Give it a 3 to 5 minutes and then try to access the application.
The restore process is marked as successful only if the restore window says 'Data restored successfully'. If you do not see this message and if the restore window disappears, it means that the restore has failed. In situations like this, please navigate to [Service Desk-Home]\bin in file explorer and you should see a 1.txt file. Please send the file along with the zip of the logs folder to support for analysis.
For builds 11304 and above:
Step 1: Stop ManageEngine ServiceDesk Plus service.
Step 2: Take a backup of the existing data present in MSSQL database using the backupDataOld.bat batch file.
From the command prompt, go to [ServiceDesk Plus-Home]\bin directory and execute the backUpDataOld.bat command to start the data backup.
cmd> backUpDataOld.bat --trimmed
where, ServiceDesk Plus Home --> Drive:\ManageEngine\ServiceDesk. This backup will be stored under the trimmedbackup folder in ServiceDesk Plus Home directory.
Step 3: Invoke ChangeDBServer.bat under [Service Desk-Home]\bin folder.
cmd> ChangeDBServer.bat
Step 4: Provide the details of the PgSQL server (i.e.) Hostname, username, and password and click Save.
Hostname: localhost
Port: 65432
Database Name: servicedesk
Username: sdpaadmin
Password: sdp@123
You can ignore any errors you see (like the one below) and click OK in the pop-up.
After clicking on Ok, you should see the below pop-up for which you can click on OK again.
Step 5: Once your are back in the command prompt, type in run and hit enter.
This will load all the modules and will start the application. Once all the modules are loaded, you should see a message "Server started" in the command prompt.
After you see this message, open up a browser and try to access the localhost url that is displayed in the command prompt. If you see the login page, try to login with the default administrator credentials by clicking on the credential listed below.
Step 6: If login action is successful, close the browser and come back to the command prompt and hit CTRL+C which would terminate the batch job that's running. You should see the modules being stopped and destroyed. At the end, when you are prompted for a confirmation for terminating the process, press 'Y' and hit enter.
Step 7: Now invoke restoreData.bat under [Service Desk-Home]\bin folder as indicated below. This would bring up a GUI to choose the backup file. During this point, you will need to choose the trimmed backup that you had taken in Step 2.
cmd> restoreData.bat --dbmigration > 1.txt
You need to choose only the part1.data file while initiating a restore. The system would automatically pick up the other parts in the backup (if exists). You need not manually select part2.data or perform any other action.
Step 8: You will be prompted to enter the backup password while performing the restore. The default backup password is SDP123!. If this doesn't work, it probably means, the password was changed. The application administrator would have received an email when the password was updated from where the password could be fetched.
If you are not aware of the backup password, then you will have to perform step 3 and 4 and change the DB back to MSSQL, start the application, check the Backup Password set under Admin --> Backup Scheduling. Once you have the password, follow steps 3 and 4 and connect the application back to PGSQL DB and try the restore again.
After you put in the password, you will be asked if the restore is performed on the Production Environment. Please choose 'Yes' in the prompt and proceed with the restore.
Step 9: After you see the message "Data Restored Successfully", start ManageEngine ServiceDesk Plus service once. As the application is starting as a service, it would usually take a couple of minutes to load. Give it a 3 to 5 minutes and then try to access the application.
The restore process is marked as successful only if the restore window says 'Data restored successfully'. If you do not see this message and if the restore window disappears, it means that the restore has failed. In situations like this, please navigate to [Service Desk-Home]\bin in file explorer and you should see a 1.txt file. Please send the file to support for analysis.
New to ADSelfService Plus?
Related Articles
How to change the database from MySQL/PostgreSQL to MSSQL
The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. For ...How do I convert the database from PGSQL to MSSQL?
1. Stop ManageEngine ServiceDeskPlus – MSP service. 2. Take a backup of the existing data and configuration under PGSQL database by following the below steps. Open CMD as an administrator and execute command to start the data backup. ...How to connect to the ServiceDesk database?
Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...How to resolve Connectivity issues with Postgresql Database from our product?
Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...MSSQL error 'Invalid Object name'
When you get a similar error as mentioned below java.sql.BatchUpdateException: Invalid object name 'TABLE NAME' please change the COLLATION in the MSSQL server for the database 'servicedesk' as CASE-INSENSITIVE. You can get this error at the time of ...