One of my customers was migrating their databases from one SQL 2005 instance to another one last night, more powerful hardware with same SQL Server version. They didn’t recover system database, just moved the login/user/job/linked servers and user databases to new instance. Then they tried to create transactional publication in the new instance via GUI wizard but failed. The same transactional publication was created in the previous instance without error.
The error message from GUI read: "Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 159 This database is not enabled for publication." And we tried to restore the database again with KEEP_REPLICATION option like what the error message help link told us to do. It failed again. Uhh, what I did next was to use GUI wizard to generate the creation scripts first and executed the script one by one to see which step failed. The error message I got then was different from GUI one, it told me that connecting to linked server repl_distributor time out. After confirming with customer, they created this repl_distributor linked server when moving the system objects. Unluckily this one is reserved from replication and it would be created by replication initialize process. I then used sp_dropdistributor @no_checks=1, @ignore_distributor=1 to remove it. After that we could create the new publication.
What it impresses me again is how to do the t-shoot to SQL Server problem. Error message from application is not enough, (yes, GUI wizard here is application which sending T-SQL to SQL Server), we should capture what sent to back end and run them it one by one to narrow down the problem.