About replication, never involved, always use the most stupid method. Today, I read a post, I benefited a lot, summarize ^ _ ^ method 1: Use the Enterprise Manager to "copy" [Temporary assumption: store the stored procedure, custom function, view, table, and triggers in the library MY_TEST, etc. Copy to my_test_copy library] 1 Right-click on the MY_TEST_COPY library in Enterprise Manager, select all tasks - import data. Operation by prompt, pay attention to select a good source database and target database! 2 Note the following choices: Pay attention to the details, OK! Method 2: Use the SQL statement to copy --1. Build the following object (synchronized server) on the target server
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[sys_syscomments_bak]') And ObjectProperty (id, n'susertable ') = 1) DROP TABLE [SYS_SYSCOMMENTS_BAK] Go
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_process_object]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [p_process_object] GO
- Creating auxiliary processing table Create Table Sys_SysComments_bak (Name Sysname, Xtype Char (2), Number Smallint, Colid Smallint, Status Smallint, Ctext Varbinary (8000)) GO
EXEC SP_CONFIGURE 'Allow Updates', 1 Reconfigure with Overridego
- Create a stored procedure create proc p_process_objectasset xact_abort onexec sp_configure process 'allow updates', 1 reconfigure with overridebegin tran - deleting the old system table records delete a from syscomments c, sysobjects o, sys_syscomments_bak ob where c.id = o .id and o.name = obs.name and o.xtype = ob.xtype
- Insert a new record into the system table Insert SysComments ([ID], [Number], [Colid], [Status], [CText]) Select O. [ID], OB. [Number], OB. [Colid ], OB. [status], ob. [ctext] from sysobjects o, sys_syscomments_bak ob u.name = obs.name and o.xtype = ob.xtypecommit TRAN
- Recompass all objects Declare TB CURSOR LOCAL for SELECT CASE WHEN XTYPE = 'Else' EXEC SP_REFRESHVIEW 'ELSE' SP_RECOMPILE 'END ' [' Replace (Object_name (ID), N'], N '] ] ') '] '' 'from sys_syscomments_bak declare @s nvarchar (4000) open tb fetch tb into @s while @@ fetch_status = 0 begin exec (@s) fetch tb into @s end close tb deallocate tbexec sp_configure' allow Updates', 0 Reconfigure with overridegoExec sp_configure 'allow updates', 0 Reconfigure with overridego - 2. On the source server (providing a synchronous server)
- create linked server, linked to the target server if exists (select * from master..sysservers where srvname = 'srv_lnk') exec sp_dropserver 'srv_lnk', 'droplogins'exec sp_addlinkedserver' srv_lnk ',' ',' SQLOLEDB ', 'Target server IP address' exec sp_addlinkedsrvlogin' srv_lnk ',' false ', NULL,' Login User name ',' login password 'exec sp_serverOption' srv_lnk ',' rpc out ',' True'go'GO
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_copyobject]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [p_copyobject] GO
