Replikation entfernen (SQL): Unterschied zwischen den Versionen
Aus InfoAgent Dokumentation
K (→Einleitung) |
Ijanot (Diskussion | Beiträge) |
||
Zeile 27: | Zeile 27: | ||
begin | begin | ||
set @sql = 'drop trigger ' + @trigname | set @sql = 'drop trigger ' + @trigname | ||
+ | execute sp_executesql @sql | ||
+ | FETCH NEXT FROM curRS INTO @trigname | ||
+ | END | ||
+ | CLOSE curRS | ||
+ | DEALLOCATE curRS | ||
+ | </source> | ||
+ | |||
+ | === Entfernen aller Systemtabellen der Replikation === | ||
+ | <source lang="tsql"> | ||
+ | declare @trigname nvarchar(300) | ||
+ | declare @sql nvarchar(300) | ||
+ | DECLARE curRS CURSOR FOR | ||
+ | select name from sysobjects where name like 'conflict_%' or name like 'MSMerge_%' or name like 'MSrepl_%' or name like 'sysmerge%' or name like 'msdynamicsnapshot%' | ||
+ | order by name | ||
+ | Open curRS | ||
+ | FETCH NEXT FROM curRS INTO @trigname | ||
+ | WHILE (@@FETCH_Status = 0 ) | ||
+ | begin | ||
+ | set @sql = 'drop table ' + @trigname | ||
execute sp_executesql @sql | execute sp_executesql @sql | ||
FETCH NEXT FROM curRS INTO @trigname | FETCH NEXT FROM curRS INTO @trigname |
Version vom 24. Mai 2007, 11:32 Uhr
Inhaltsverzeichnis
- 1 Replikation nach DB-Restore entfernen
Replikation nach DB-Restore entfernen
Einleitung
Eine replizierte Datenbank wurde durch Rücksicherung von BACKUP-File oder MDF/LDF wiederhergestellt. Bei der Sicherung wurden aber die Systemdatenbanken nicht mitgesichert. Folglich muss das Datenbank-Schema von den Replikationsinhalten entfernt werden.
Gehen Sie dazu wie folgt vor...
SQL-Befehle nur im Microsoft Enterprise Manager bzw. im Microsoft SQL Management Studio ausführen.
Entfernen aller Trigger der Replikation
declare @trigname nvarchar(300) declare @sql nvarchar(300) DECLARE curRS CURSOR FOR select name from sysobjects where xtype = 'tr' and ( name like 'ins_%' or name like 'upd_%' or name like 'del_%' ) order by name Open curRS FETCH NEXT FROM curRS INTO @trigname WHILE (@@FETCH_Status = 0 ) begin set @sql = 'drop trigger ' + @trigname execute sp_executesql @sql FETCH NEXT FROM curRS INTO @trigname END CLOSE curRS DEALLOCATE curRS
Entfernen aller Systemtabellen der Replikation
declare @trigname nvarchar(300) declare @sql nvarchar(300) DECLARE curRS CURSOR FOR select name from sysobjects where name like 'conflict_%' or name like 'MSMerge_%' or name like 'MSrepl_%' or name like 'sysmerge%' or name like 'msdynamicsnapshot%' order by name Open curRS FETCH NEXT FROM curRS INTO @trigname WHILE (@@FETCH_Status = 0 ) begin set @sql = 'drop table ' + @trigname execute sp_executesql @sql FETCH NEXT FROM curRS INTO @trigname END CLOSE curRS DEALLOCATE curRS
Entfernen aller Stored Procedures der Replikation
declare @trigname nvarchar(300) declare @sql nvarchar(300) DECLARE curRS CURSOR FOR select name from sysobjects where (name like 'sp_%' or name like 'sel_%') and name <> 'sparten' and name <> 'selkennzeichen' order by name Open curRS FETCH NEXT FROM curRS INTO @trigname WHILE (@@FETCH_Status = 0 ) begin set @sql = 'drop procedure ' + @trigname execute sp_executesql @sql FETCH NEXT FROM curRS INTO @trigname END CLOSE curRS DEALLOCATE curRS
Entfernen aller Sichten der Replikation
declare @trigname nvarchar(300) declare @sql nvarchar(300) DECLARE curRS CURSOR FOR select name from sysobjects where name like 'ctsv_%' or name like 'tsvw_%' order by name Open curRS FETCH NEXT FROM curRS INTO @trigname WHILE (@@FETCH_Status = 0 ) begin set @sql = 'drop view ' + @trigname execute sp_executesql @sql FETCH NEXT FROM curRS INTO @trigname END CLOSE curRS DEALLOCATE curRS
Entfernen des Merkmals Mergepublication der Replikation
Voraussetzung: im SQL-Server Direktänderungen erlauben
EXEC master.dbo.sp_configure N'allow updates', N'1' GO RECONFIGURE WITH OVERRIDE GO
Der eigentliche Update-Befehl
update sysobjects set replinfo = 0 where xtype = 'U' and replinfo = 128
Voraussetzung zurücksetzen
EXEC master.dbo.sp_configure N'allow updates', N'0' GO RECONFIGURE WITH OVERRIDE GO