SQL restore data from bak file

[Petr Humpolíček]

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-]

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?

[Petr Humpolíček]

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.<init>(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.<init>(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-]

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?