-
Notifications
You must be signed in to change notification settings - Fork 0
/
05_Cleanup_Subscriber.sql
104 lines (82 loc) · 3.7 KB
/
05_Cleanup_Subscriber.sql
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
DECLARE
@publisher nvarchar(1000)
,@publisher_db nvarchar(1000)
,@publication nvarchar(1000)
,@subscriber nvarchar(1000)
,@destination_db nvarchar(1000)
,@distributor nvarchar(1000)
,@distributor_db nvarchar(1000)
,@distributor_password nvarchar(1000)
,@distributor_data_file nvarchar(1000)
,@distributor_log_file nvarchar(1000)
,@SnapshotFolder nvarchar(1000)
,@job_server_publisher nvarchar(1000)
,@job_server_subscriber nvarchar(1000)
,@publisher_db_backup_path_1 nvarchar(1000)
,@publisher_db_backup_path_2 nvarchar(1000)
,@db_restore_db_path_1 nvarchar(1000)
,@db_restore_db_path_2 nvarchar(1000)
,@db_restore_db_path_3 nvarchar(1000)
,@db_restore_db_path_4 nvarchar(1000)
,@db_restore_db_path_5 nvarchar(1000)
,@db_restore_db_path_6 nvarchar(1000)
SELECT
@publisher = N'$(publisher)'
,@publisher_db = N'$(publisher_db)'
,@publication = N'$(publication)'
,@subscriber = N'$(subscriber)'
,@destination_db = N'$(destination_db)'
,@distributor = N'$(distributor)'
,@distributor_db = N'$(distributor_db)'
,@distributor_password = N'$(distributor_password)'
,@distributor_data_file = N'$(distributor_data_file)'
,@distributor_log_file = N'$(distributor_log_file)'
,@SnapshotFolder = N'$(SnapshotFolder)'
,@job_server_publisher = N'$(job_server_publisher)'
,@job_server_subscriber = N'$(job_server_subscriber)'
,@publisher_db_backup_path_1 = N'$(publisher_db_backup_path_1)'
,@publisher_db_backup_path_2 = N'$(publisher_db_backup_path_2)'
,@db_restore_db_path_1 = N'$(db_restore_db_path_1)'
,@db_restore_db_path_2 = N'$(db_restore_db_path_2)'
,@db_restore_db_path_3 = N'$(db_restore_db_path_3)'
,@db_restore_db_path_4 = N'$(db_restore_db_path_4)'
,@db_restore_db_path_5 = N'$(db_restore_db_path_5)'
,@db_restore_db_path_6 = N'$(db_restore_db_path_6)'
DECLARE
@PrintMessage varchar(1000)
,@PreviousTimestamp datetime
SELECT
@PrintMessage = ''
,@PreviousTimestamp = getdate()
SELECT @PrintMessage = 'Check if subscriber exists. Current time: '+CONVERT(varchar(20), getdate(), 121)+', Elapsed time(ms): ' + CAST(DATEDIFF(millisecond, @PreviousTimestamp, GETDATE()) AS VARCHAR(100))
, @PreviousTimestamp = GETDATE()
RAISERROR(@PrintMessage, 0, 0) WITH NOWAIT
IF DB_ID(@destination_db) IS NOT NULL
BEGIN
DECLARE @tbl TABLE (col VARCHAR(10))
,@exec_query nvarchar(1000)= 'select 1 from ' + @publisher_db + '.sys.tables where name = ''MSsubscription_properties'' and is_ms_shipped = 1'
INSERT INTO @tbl(col)
EXEC (@exec_query)
IF EXISTS(SELECT * FROM @tbl)
BEGIN
DECLARE @Query nvarchar(4000)= 'USE ' +@destination_db +
';
DECLARE
@PrintMessage varchar(1000)
,@PreviousTimestamp datetime
SELECT
@PrintMessage = ''''
,@PreviousTimestamp = getdate()
SELECT @PrintMessage = ''Dropping subscription. Current time: ''+CONVERT(varchar(20), getdate(), 121)+'', Elapsed time(ms): '' + CAST(DATEDIFF(millisecond, @PreviousTimestamp, GETDATE()) AS VARCHAR(100))
, @PreviousTimestamp = GETDATE()
RAISERROR(@PrintMessage, 0, 0) WITH NOWAIT
EXEC sp_droppullsubscription
@publisher = '''+@publisher+''',
@publisher_db = '''+@publisher_db+''',
@publication = '''+@publication+''';
SELECT @PrintMessage = ''Dropped subscription. Current time: ''+CONVERT(varchar(20), getdate(), 121)+'', Elapsed time(ms): '' + CAST(DATEDIFF(millisecond, @PreviousTimestamp, GETDATE()) AS VARCHAR(100))
, @PreviousTimestamp = GETDATE()
RAISERROR(@PrintMessage, 0, 0) WITH NOWAIT '
EXEC (@Query)
END
END