1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
| select @@servername rendait "NULL"
exec sp_addserver 'fr-ap027', local pour fixer les choses
exec sp_dropdistpublisher @publisher='opconprd.sep.unilog.fr'
pour sortir de l'ancien nom, ne donne rien,
sp_dropdistributiondb @database = 'opconxps-prod' 'opconprd'
ne passait pas : la base n'est pas éditée
-- automated script for replication
-- from : FR-AP027
-- Input base : opconxps-prod
-- repl data : \\FR-AP027\d$\baseDonnées\MSSQL\REPLDATA
-- Dist DB : opconxps-prod_distrib
-- to : fr-dev-ap027
-- Output base : Opconxps-Prod
-- to : (NONE)
-- Output base : (NONE)
-- Created the : 2010/10/11 17:00:16
-- SnapShot : FR-AP027-opconxps-prod-OPCON_complete-%
-- Post_SnapShot_Script : C:\Program Files\OpConxps\Utilities\Database\MAJDBFullScript.opconxps-prod.33151.20080402175607.sql
-- replication restauration start --- In case of replication restauration, delete these lignes up to -- replication restauration end
use master
GO
exec sp_adddistributor @distributor = N'FR-AP027', @password = N''
GO
-- Adding the distribution database
exec sp_adddistributiondb @database = N'opconxps-prod_distrib', @data_file = N'opconxps-prod_distrib.MDF', @data_file_size = 50, @log_file = N'opconxps-prod_distrib.LDF', @log_file_size = 10, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @publisher = N'FR-AP027', @distribution_db = N'opconxps-prod_distrib', @security_mode = 1, @working_directory = N'\\FR-AP027\d$\baseDonnées\MSSQL\REPLDATA', @trusted = N'false', @thirdparty_flag = 0
GO
-- Adding the registered subscriber 1
exec sp_addsubscriber @subscriber = N'fr-dev-ap027', @type = 0, @security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 2, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235900, @description = N''
exec sp_changesubscriber_schedule @subscriber = N'fr-dev-ap027', @agent_type = 1, @active_end_date = 0
GO
-- Adding the registered subscriber 2
--exec sp_addsubscriber @subscriber = N'(NONE)', @type = 0, @security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 2, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235900, @description = N''
--exec sp_changesubscriber_schedule @subscriber = N'(NONE)', @agent_type = 1, @active_end_date = 0
--GO
-- replication restauration end
-- Enabling the replication database
use master
GO
exec sp_replicationdboption N'opconxps-prod', N'publish', N'true'
GO
use [opconxps-prod]
GO
-- Adding the transactional publication
exec sp_addpublication @publication = N'OPCON_complete', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of OPCONXPS database from Publisher FR-AP027.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 72, @post_snapshot_script = N'C:\Program Files\OpConxps\Utilities\Database\MAJDBFullScript.opconxps-prod.33151.20080402175607.sql'
exec sp_addpublication_snapshot @publication = N'OPCON_complete',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 233000, @active_end_time_of_day = 0
GO
exec sp_grant_publication_access @publication = N'OPCON_complete', @login = N'sa'
GO
-- Adding the transactional articles
declare @TABLENAME varchar(128)
declare @p1 varchar(64)
declare @p2 varchar(64)
declare @p3 varchar(64)
declare TABLECURSOR cursor for select name
from sysobjects where type = 'U' and status >= 0 and category <> 2 and name not like 'pdsa%'
open TABLECURSOR
print 'Tables found : '
fetch next from TABLECURSOR into @TABLENAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
print ' '+@TABLENAME
set @p1 = N'CALL sp_MSins_'+@TABLENAME
set @p2 = N'CALL sp_MSdel_'+@TABLENAME
set @p3 = N'CALL sp_MSupd_'+@TABLENAME
exec sp_addarticle @publication = N'OPCON_complete', @article = @TABLENAME, @source_owner = N'dbo', @source_object = @TABLENAME, @destination_table = @TABLENAME, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false', @ins_cmd = @p1, @del_cmd = @p2, @upd_cmd = @p3, @filter = null, @sync_object = null
End
fetch next from TABLECURSOR into @TABLENAME
End
DEALLOCATE TABLECURSOR
-- Adding the transactional subscription 1
exec sp_addsubscription @publication = N'OPCON_complete', @article = N'all', @subscriber = N'fr-dev-ap027', @destination_db = N'Opconxps-Prod', @sync_type = N'automatic', @update_mode = N'read only'
GO
-- Adding the transactional subscription 2
-- exec sp_addsubscription @publication = N'OPCON_complete', @article = N'all', @subscriber = N'(NONE)', @destination_db = N'(NONE)', @sync_type = N'automatic', @update_mode = N'read only'
-- GO
-- Starting SnapShot agent
use msdb
go
declare @jobname sysname
declare @nbjob int
select @nbjob = count(*) from sysjobs where name like 'FR-AP027-opconxps-prod-OPCON_complete-%'
if @nbjob = 1
begin
select @jobname = name from sysjobs where name like 'FR-AP027-opconxps-prod-OPCON_complete-%'
print @jobname
exec sp_start_job @job_name = @jobname
end
else
begin
raiserror ('unable to found snapshot job', 4, 1)
end
pour recréer une réplication ne monte pas : une réplication est déjà en place sur cette base
sp_removedbreplication 'Opconxps-prod'
go
passe
select * from syssubscriptions
select * from syspublications
renvoient toutes les deux
Serveur*: Msg 208, Niveau 16, État 1, Ligne 1
'syspublications' : nom d'objet incorrect. |
Problème réplication SQL2000 après renommage sauvage de machines sans arrêt préalable
Répondre avec citation
Envoyé par Seb_sqlnovice
Partager