Deployment Prepare Commit Phase after restore of CM and Broker databases

Question

After restoring the CM and Broker databases from our production environment any publish action fails with the following:

Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query, Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query

Stack trace from cd_deployer log:

2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-125412-66560 2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-125412-66560 com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query at com.tridion.deployer.phases.PreCommitPhase.handleFailure(PreCommitPhase.java:120) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:101) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:186) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:97) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:61) [cd_deployer.jar:na] at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:80) [cd_deployer.jar:na] at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:176) [cd_deployer.jar:na] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_37] at java.lang.Thread.run(Unknown Source) [na:1.6.0_37] 2014-01-08 11:53:08,143 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-125412-66560

Stack trace from cd_core log:

2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Final attempt in Phase: Deployment Prepare Commit Phase failed for transaction: tcm:0-125412-66560 2014-01-08 11:53:08,112 ERROR DeployPipelineExecutor - Original stacktrace for transaction: tcm:0-125412-66560 com.tridion.deployer.ProcessingException: Unable to prepare transaction: tcm:0-125412-66560, org.hibernate.exception.SQLGrammarException: could not execute query, org.hibernate.exception.SQLGrammarException: could not execute query at com.tridion.deployer.phases.PreCommitPhase.handleFailure(PreCommitPhase.java:120) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.PreCommitPhase.execute(PreCommitPhase.java:101) ~[cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.runMainExecutePhase(DeployPipelineExecutor.java:186) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.doExecute(DeployPipelineExecutor.java:97) [cd_deployer.jar:na] at com.tridion.deployer.phases.DeployPipelineExecutor.execute(DeployPipelineExecutor.java:61) [cd_deployer.jar:na] at com.tridion.deployer.TransactionManager.handleDeployPackage(TransactionManager.java:80) [cd_deployer.jar:na] at com.tridion.deployer.queue.QueueLocationHandler$1.run(QueueLocationHandler.java:176) [cd_deployer.jar:na] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) [na:1.6.0_37] at java.util.concurrent.FutureTask.run(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) [na:1.6.0_37] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.6.0_37] at java.lang.Thread.run(Unknown Source) [na:1.6.0_37] 2014-01-08 11:53:08,143 ERROR DeployPipelineExecutor - Unable to start processing deployment package with transactionId: tcm:0-125412-66560

Nothing has changed in the config files (storage, deployer etc) and I've checked that the broker database username/password are correct and the db can be accessed from the deployer server.

The version of Tridion is the same in both cases - anyone seen this before?

UPDATE

Some details on the database servers:

• Both are SQL Server 2008 R2 (10.50.2500.0).
• Both have collation set to Latin1_General_CI_AS.
• Both have matching character sets: ntext:UNICODE, nvarchar:UNICODE, varchar:iso_1

I restored my backup copy of the broker db to a new db, changed cd_storage and republished and got Success! When I switched cd_storage back to my restored copy, I get Failed (with the SQLGrammarException).

Answer 1

Turned out to be my database user - it was missing db_datareader & db_datawriter roles from its Database role membership so was being denied the SELECT permission.

I discovered this by setting logging to DEBUG in logback.xml and the following errors appeared in my cd_core log:

2014-01-08 13:42:07,143 DEBUG JDBCExceptionReporter - could not execute update query [delete from DYNAMIC_LINKS where SRC_PUB_ID=? and SRC_ITEM_ID=? and SRC_ITEM_TYPE=?]
com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object 'DYNAMIC_LINKS', database 'Tridion_Broker', schema 'dbo'.