Change the schema name for Jira database tables
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:
This article only applies to Oracle and SQL Server database management systems.
Backup XML Restore
Restore XML backup into a brand new database where your db user uses a different schema (e.g. jiraschema).
Alter existing Tables for new Schema in Microsoft SQL Server
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
- 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