Change the schema name for Jira database tables
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
There are times when an administrator might want to change the schema of their existing database tables. This could be for organizational or "good practice" purposes. Other times it may relate to bugs, for example:
Environment
This article only applies to Oracle and SQL Server database management systems.
Resolution
Backup XML Restore
Restore XML backup into a brand new database where your db user uses a different schema (e.g. jiraschema).
Backup your existing data via a backup XML: Backing Up Data
Create the new schema in your database
Define the new schema in dbconfig.xml: JIRA Home Directory
Restart JIRA
You should reach the setup wizard, at which point you can restore the Backup XML: Restoring Data
Alter existing Tables for new Schema in Microsoft SQL Server
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Shutdown JIRA
Run the following SQL query in SQL Server:
SELECT 'ALTER SCHEMA newschema TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oldschema'Replace newschema and oldschema with the actual names of your new and old schema.
This essentially provides a list of SQL queries to help automate the ALTER SCHEMA function in SQL Server
Copy the results into a new SQL script and run the queries.
Define the new schema in dbconfig.xml: JIRA Home Directory
Restart JIRA
Was this helpful?