SQL restore data from bak file

Question

Dear Experts,

due tu technical problems, we dont have any backup from JIRA database. We have only backup from MS SQL 2012 (bak) file. Is it possible somehow restore data? It would be the best to have xml export for JIRA.

We tried to do new installation with new sql databse, then we changed dbconfig.xml file but we could not start JIRA again.

Nic Brough -Adaptavist- · Answer

Yes, that looks like you've not quite got the database settings right, although good enough to get in, to find there's no valid data there.

Could you set up a new, empty Jira with a different database next, same version as you had before.  Prove it works by going through setup and config, logging into it and checking you can create and update a test issue.

Then stop it, amend the dbconfig.xml only (nothing else) to point it at the restored-from-BAK database and see what it does after restarting?

Petr Humpolíček · Answer

Thank you for answer. To complete log (\Application Data\JIRA\log\atlassian-jira.log) file is here. ftp://aris1.idsa.cz/incoming/JIRA/atlassian-jira.zip .. Database configuration OK ___ Database Configuration ________________ Loading entityengine.xml from : file:/C:/Program%20Files/Atlassian/JIRA/atlassian-jira/WEB-INF/classes/entityengine.xml Entity model field type name : mssql Entity model schema name : dbo Database Version : Microsoft SQL Server - 11.00.5343 Database Driver : Microsoft JDBC Driver 6.2 for SQL Server - 6.2.1.0 Database URL : jdbc:sqlserver://localhost:1433;jaasConfigurationName=SQLJDBCDriver;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=PROD_JIRA;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; Database JDBC config : mssql jdbc:sqlserver://;serverName=localhost;portNumber=1433;databaseName=PROD_JIRA 2019-07-03 12:10:59,779 JIRA-Bootstrap ERROR [c.a.jira.appconsistency.JiraSetupConfig] Unable to load values for CacheKey[entityName=jira.properties,entityId=1] com.opensymphony.module.propertyset.PropertyImplementationException: Unable to load values for CacheKey[entityName=jira.properties,entityId=1] at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.propEx(CachingOfBizPropertyEntryStore.java:332) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.resolve(CachingOfBizPropertyEntryStore.java:105) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.getEntry(CachingOfBizPropertyEntryStore.java:123) at com.atlassian.jira.propertyset.CachingOfBizPropertySet.get(CachingOfBizPropertySet.java:189) at com.opensymphony.module.propertyset.AbstractPropertySet.getString(AbstractPropertySet.java:305) at com.atlassian.jira.config.properties.ApplicationPropertiesStore.getStringFromDb(ApplicationPropertiesStore.java:234) at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.getString(ApplicationPropertiesImpl.java:54) at com.atlassian.jira.appconsistency.JiraSetupConfig.isSetup(JiraSetupConfig.java:29) at com.atlassian.jira.instance.InstanceStateProvider.isInstalling(InstanceStateProvider.java:88) at com.atlassian.jira.instance.InstanceStateProvider.currentState(InstanceStateProvider.java:64) at com.atlassian.jira.startup.mode.DefaultStartupModeReference.determineStartupMode(DefaultStartupModeReference.java:33) at com.atlassian.jira.startup.mode.DefaultStartupModeReference.initialise(DefaultStartupModeReference.java:26) at com.atlassian.jira.startup.DefaultJiraLauncher.recordStartupMode(DefaultJiraLauncher.java:153) at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:140) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:304) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:199) at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135) at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102) at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31) at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100) at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154) at java.lang.Thread.run(Thread.java:748) Caused by: com.atlassian.cache.CacheException: com.querydsl.core.QueryException: Caught SQLServerException for select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from dbo.propertyentry O_S_PROPERTY_ENTRY where O_S_PROPERTY_ENTRY.entity_name = ? and O_S_PROPERTY_ENTRY.entity_id = ? order by O_S_PROPERTY_ENTRY.id desc at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:211) at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:162) ... 20 more Caused by: com.querydsl.core.QueryException: Caught SQLServerException for select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from dbo.propertyentry O_S_PROPERTY_ENTRY where O_S_PROPERTY_ENTRY.entity_name = ? and O_S_PROPERTY_ENTRY.entity_id = ? order by O_S_PROPERTY_ENTRY.id desc at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.lambda$loadPropertyKeysAndTypes$2(CachingOfBizPropertyEntryStore.java:472) at com.atlassian.jira.database.DefaultQueryDslAccessor$1.lambda$executeQuery$0(DefaultQueryDslAccessor.java:85) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:68) at com.atlassian.jira.database.DefaultQueryDslAccessor$1.executeQuery(DefaultQueryDslAccessor.java:84) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.query(CachingOfBizPropertyEntryStore.java:284) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.access$200(CachingOfBizPropertyEntryStore.java:64) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.loadPropertyKeysAndTypes(CachingOfBizPropertyEntryStore.java:466) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.(CachingOfBizPropertyEntryStore.java:426) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.loadPropertySetData(CachingOfBizPropertyEntryStore.java:270) at com.atlassian.cache.memory.MemoryCacheManager$3$1.load(MemoryCacheManager.java:207) at com.atlassian.cache.memory.DelegatingCache.lambda$get$0(DelegatingCache.java:162) at com.atlassian.cache.memory.DelegatingCache.lambda$get$1(DelegatingCache.java:190) at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4742) at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527) at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2319) at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2282) at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2197) at com.google.common.cache.LocalCache.get(LocalCache.java:3937) at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4739) at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:177) ... 22 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'entity_name'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446) ... 42 more 2019-07-03 12:10:59,981 JIRA-Bootstrap ERROR [c.a.jira.startup.LauncherContextListener] Unable to start JIRA. com.opensymphony.module.propertyset.PropertyImplementationException: Unable to load values for CacheKey[entityName=jira.properties,entityId=1] at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.propEx(CachingOfBizPropertyEntryStore.java:332) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.resolve(CachingOfBizPropertyEntryStore.java:105) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.getType(CachingOfBizPropertyEntryStore.java:133) at com.atlassian.jira.propertyset.CachingOfBizPropertySet.exists(CachingOfBizPropertySet.java:139) at com.atlassian.jira.config.properties.ApplicationPropertiesStore.getOption(ApplicationPropertiesStore.java:158) at com.atlassian.jira.config.properties.ApplicationPropertiesImpl.getOption(ApplicationPropertiesImpl.java:88) at com.atlassian.jira.index.ha.DisasterRecoveryLauncher.getRecoveryMode(DisasterRecoveryLauncher.java:53) at com.atlassian.jira.index.ha.DisasterRecoveryLauncher.earlyStart(DisasterRecoveryLauncher.java:97) at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:141) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:304) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:199) at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135) at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102) at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31) at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100) at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154) at java.lang.Thread.run(Thread.java:748) Caused by: com.atlassian.cache.CacheException: com.querydsl.core.QueryException: Caught SQLServerException for select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from dbo.propertyentry O_S_PROPERTY_ENTRY where O_S_PROPERTY_ENTRY.entity_name = ? and O_S_PROPERTY_ENTRY.entity_id = ? order by O_S_PROPERTY_ENTRY.id desc at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:211) at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:162) ... 15 more Caused by: com.querydsl.core.QueryException: Caught SQLServerException for select O_S_PROPERTY_ENTRY.property_key, O_S_PROPERTY_ENTRY.propertytype from dbo.propertyentry O_S_PROPERTY_ENTRY where O_S_PROPERTY_ENTRY.entity_name = ? and O_S_PROPERTY_ENTRY.entity_id = ? order by O_S_PROPERTY_ENTRY.id desc at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.lambda$loadPropertyKeysAndTypes$2(CachingOfBizPropertyEntryStore.java:472) at com.atlassian.jira.database.DefaultQueryDslAccessor$1.lambda$executeQuery$0(DefaultQueryDslAccessor.java:85) at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:68) at com.atlassian.jira.database.DefaultQueryDslAccessor$1.executeQuery(DefaultQueryDslAccessor.java:84) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.query(CachingOfBizPropertyEntryStore.java:284) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.access$200(CachingOfBizPropertyEntryStore.java:64) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.loadPropertyKeysAndTypes(CachingOfBizPropertyEntryStore.java:466) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore$PropertySetData.(CachingOfBizPropertyEntryStore.java:426) at com.atlassian.jira.propertyset.CachingOfBizPropertyEntryStore.loadPropertySetData(CachingOfBizPropertyEntryStore.java:270) at com.atlassian.cache.memory.MemoryCacheManager$3$1.load(MemoryCacheManager.java:207) at com.atlassian.cache.memory.DelegatingCache.lambda$get$0(DelegatingCache.java:162) at com.atlassian.cache.memory.DelegatingCache.lambda$get$1(DelegatingCache.java:190) at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4742) at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527) at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2319) at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2282) at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2197) at com.google.common.cache.LocalCache.get(LocalCache.java:3937) at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4739) at com.atlassian.cache.memory.DelegatingCache.get(DelegatingCache.java:177) ... 17 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'entity_name'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446) ... 37 more 2019-07-03 12:11:00,092 JIRA-Bootstrap INFO [c.a.jira.startup.LauncherContextListener] Memory Usage: --------------------------------------------------------------------------------- Heap memory : Used: 90 MiB. Committed: 490 MiB. Max: 746 MiB Non-heap memory : Used: 69 MiB. Committed: 71 MiB. Max: 1264 MiB --------------------------------------------------------------------------------- TOTAL : Used: 159 MiB. Committed: 560 MiB. Max: 2010 MiB ---------------------------------------------------------------------------------

Nic Brough -Adaptavist- · Answer

You should use whatever tool that created the .bak file to create a new copy of the database, then hook up another Jira to it.

I'm afraid "could not start Jira again" does not explain what the problems are.  What does the log file say the failure is?