Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2003-07-22 : 13:31:31
|
| I just refreshed some development databases by restoring the databases with a copy of production data. After the restore I re-ran the Log Reader agent and received the following error."The process could not execute 'sp_repldone/sp_replcounters' on 'DEVELOP'."The database is not published"I am running SQL Server 2000 with Service Pack 3. My restore statements were as follows:Restore Database DB1 from DB1_from_Productionwith replace,move 'DB1_data' to 'H:\sql_data\DEVELOP\DB1_data.mdf',move 'DB1_log' to 'G:\sql_log\DEVELOP\DB1_log.I did not realize that a restore from a different server would break replication. I assumed the replication information was contained in the Distribution database.Should I have used "KEEP_REPLICATION" as a restore option or should I have done something else?Why did this occur?Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 13:36:32
|
| Just script out replication from the production server. Then run these scripts on the development server. You will need to modify some of the parameters though such as server name.I have never used the KEEP REPLICATION option.Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-07-22 : 14:38:17
|
| Tara,I just started to do something similar to what you suggested. The replication exists on the development server so I need to run the development server's replication scripts. From what I have read on BOL it looks like when you restore a database using a backup from a different server replication is removed. I'm not sure why. Anyhow, my replication scripts are not up to date so I'm going to try and restore the development server with a copy of last nights database backup. Here is what I plan on doing.(1) Restore development from last night's development db backup (My hope is that replication will be restored)(2) Create my replication scripts (3) Restore the development server with a copy of production(4) Run the replication scripts on development. Does this sound like it will work?Thanks, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 14:40:15
|
quote:
(1) Restore development from last night's development db backup (My hope is that replication will be restored)(2) Create my replication scripts (3) Restore the development server with a copy of production(4) Run the replication scripts on development. Does this sound like it will work?
No, I don't think that it will work since replication is not stored in the user database. Just create the replication scripts from production. Then copy the scripts to your development server. Then modify the scripts (it is very obvious what to modify so don't get worried about this part). Then run the scripts on the development server.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-07-22 : 16:40:11
|
| Tara,I just tried running my replication scripts and still encountered problems. Since replication previously existed on the development server do I need to uninstall replication on the development server prior to restoring it with a copy from production?When I tried running the scripts I received messages indicating that the distribution database already exists. I commented that logic out of the scripts and still received error in nearly every section of the script.Can you outline the steps I need to follow?Thanks again, Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 17:04:37
|
| The script should include the dropping of replication. If the script way does not work, I would remove replication from the server before the restore, then restore the database, then manually recreate replication. Then I would script out replication, including drop and create statements that way you can rebuild replication quickly.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2003-07-22 : 17:33:33
|
| I decided to do just that. I removed replication completely and will attempt to install it with the scripts. I wasn't the original DBA who created the replication process to begin with. When I did some poking around I noticed that the distribution database contained access permissions for login ids that have long since been removed from the server. That concerned me a bit so I thought I would play it safe and reinstall. It's possible replication was not installed correctly to begin with, which may explain some of the problems I have encountered. Hope this works.Thanks, Dave |
|
|
|
|
|
|
|
|
Replication Failure After Database Restore