| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2008-04-02 : 08:27:26
|
| Started a publication on a few tables, and decided to cancel it. Now there are no publications set up, but the sharing hand is still there on the database, and agents all over the place. Boss is angry, can I tidy up this mess?I can't convince him I haven't made changes to the production database... |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-02 : 09:02:34
|
| Depends how you cancelled it. You will need to clean up the agents, the databases and anything in between. There are clean up procedures, look in BOL for more on them.You did change the production database btw, replication creates some tables.What type of replication did you try as it does make a difference depending on whether it was Transactional or Merge. |
 |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-04-02 : 09:07:47
|
| You also added some columns to the tables you replicated."God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-02 : 09:24:25
|
| Are you trying to delete all replication? How many publishers are there in replication? |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-04-02 : 09:42:15
|
| Thank you very much. Very important to get rid of those extras. Good lessons. I know which tables I selected. Can I remove these bits with GUI tools? That sharing hand has to go for me to keep this job...It was transactional replication.I had a little window from a local EM session which got to setting up 2 of 5 articles, and got no further after half and hour. Connected to server to check. When I logged out the server asked about continuing to set up the publication Y/N and I said no. Now I have bad icons hanging around. No publications existing... How do I clean up? It's 2000What is BOL? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-02 : 10:13:30
|
| BOL is books online, SQLs help file.look up sp_removedbreplication and sp_removedistpublisherdbreplicationDo NOT delete the publication database yourself (question asked in other thread!). Try to clean up replication properly before attempting any quick fixes. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-04-02 : 12:10:54
|
| If I need some tables from Production, will backing up be less disruptive to services, and can I select just those tables, not the whole database? The test environment I have is just 7GB, while Production is 21Gb. The contractor has gone, and I am now he.I need to update my test DB daily and I was sure replication was the way to go - to leep changes passing to my instance, but the boss didn't like it. He wanted me to use tapes!There are no backups on SQL Server! Backups are done seperately by files to tapes on the server. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 22:45:26
|
| No, you can't backup table directly. Tried copy table in dts? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-03 : 02:05:05
|
| The fastest way is to BCP the tables out to files and BCP them into your Dev environment. This can be set up as a job.Do you mean the backups are straight to tape? If so, then I hope you've tested them. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-04-03 : 04:23:30
|
| I have run sp_removedbreplication. I got 'completed successfully' but nothing else in terms of evidence. I'm happy to think: progress. Still server hand on Production. Agents still there.When I do exec sp_removedistpublisherdbreplication I get the SP cannot be found. I tried looking in master. What do I do if it's not there? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-03 : 04:35:27
|
| If this is 2000, go to the (I think, haven't got it in front of me though) Tools -> Replication -> Manage Relication(? Not sure if this is the one, but it is something similar).Just untick the boxes for your database. Like magic, the hand will disappear. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2008-04-03 : 04:55:31
|
| Rick!You done it for me! No playing with scary SPs any more. Who can't check thru a few menus for replication and disable the distributor / publishers...? The 'sharing hand' has gone from the big database and I think I'll get to keep my job for now. It seems a mess in 2000 the way it is now in 2005All I really need to do is get a handful of tables copied to my testenv. I'm used to daily backups on a system, and there are none to use here. I'm not allowed to touch the DB. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-04-03 : 05:41:23
|
| As I said, look up BCP, you can output the results to a csv and then input to your local table. |
|
|
|
|
Started / Cancelled Replication 2000