SQLServer实例之间克隆表

场景： 两个SQLServer实例（可能在不同机器上）

（1）实例1 ：　SarahCla

（2）实例2：     CloneServer

目标：将SarahCla中的某些表的结构及数据克隆到CloneServer中

step 1： 在CloneServer中建立SarahCla的linkserver，执行语句如下

Exec sp_droplinkedsrvlogin [SARAHCLA],Null

EXEC  sp_addlinkedserver  
      @server='SARAHCLA',
      @srvproduct='',  
      @provider='SQLOLEDB',  
      @datasrc="SarahCla"   
     
EXEC sp_addlinkedsrvlogin   
     'SARAHCLA',
     'false',   
     NULL,   
     'sa', --帐号  
     'XXXXX' --密码     嚯嚯，差点忘了删密码

step 2: 在SarahCla 实例中相应的数据库中创建存储过程，获取table的创建语句。

（linkserver直接select * into 会丢掉一些列的property，例如identity）

create procedure SP_GET_TABLE_INFO
@ObjName varchar(128),       
@sql nvarchar(4000) output,
@cols nvarchar(4000) output
as

declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID   TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length   TinyInt
declare @Prec     TinyInt
declare @Scale    TinyInt
declare @Status   TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID     SmallInt  
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName    varchar(30)
declare @strPri_Key varchar (255)

if not Exists(Select name from sysobjects where name = @ObjName)
begin
  select @DBName = db_name()
    raiserror(15009,-1,-1,@ObjName,@DBName)
    return (1)
end

create table #spscript
(
    id     int IDENTITY not null,
    Script Varchar(255) NOT NULL,
    LastLine tinyint
)

declare Cursor_Column INSENSITIVE CURSOR
  for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
        case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
        from syscolumns a, systypes b where object_name(a.id) = @ObjName
        and a.usertype = b.usertype order by a.ColID

set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)

/* Get column information */
open Cursor_Column

fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
      @Status,@cDefault,@Const_Key

Select @Script = ''
while (@@FETCH_STATUS <> -1)
begin
  if (@@FETCH_STATUS <> -2)
  begin

    if (@cols='')
        set @cols = @ColName
    else
        set @cols = @cols + ',' + @Colname
        
    Select @Script = @ColName + ' ' + @TypeName
    
    if @UserType in (1,2,3,4)
      Select @Script = @Script + '(' + Convert(char(3),@Length) + ') '
    else if @UserType in (24)
      Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
                      + Convert(char(3),@Scale) + ') '
    else
      Select @Script = @Script + ' '
    if ( @Status & 0x80 ) > 0
      Select @Script = @Script + ' IDENTITY(1,1) '

    if ( @Status & 0x08 ) > 0
      Select @Script = @Script + ' NULL '
    else
      Select @Script = @Script + ' NOT NULL '
    if @cDefault > 0
      Select @Script = @Script + ' DEFAULT ' + @Const_Key
  end
  fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale, @Status,@cDefault,@Const_Key
  if @@FETCH_STATUS = 0
  begin
    Select @Script = @Script + ','
    Insert into #spscript values(@Script,0)
  end
  else
  begin
    Insert into #spscript values(@Script,1)
    Insert into #spscript values(')',0)
  end
end
Close Cursor_Column
Deallocate Cursor_Column

/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR for
    Select name,IndID,status
    from sysindexes
    where object_name(id)=@ObjName and IndID > 0 and IndID<>255  
    order by IndID   
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
  if @@FETCH_STATUS <> -2
  begin

    declare @i TinyInt
    declare @thiskey varchar(50)
    declare @IndDesc varchar(68)

    Select  @i = 1
    while (@i <= 16)
    begin
      select @thiskey = index_col(@ObjName, @IndID, @i)
      if @thiskey is null
        break

      if @i = 1
        select @Index_Key = index_col(@ObjName, @IndID, @i)
      else
        select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
      select @i = @i + 1
    end
    if (@IndStatus & 0x02) > 0
      Select @Script = 'Create unique '
    else
      Select @Script = 'Create '
    if @IndID = 1
      select @Script = @Script + ' clustered '


    if (@IndStatus & 0x800) > 0
     select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
    else
     select @strPri_Key = ''
     
    if @IndID > 1
      select @Script = @Script + ' nonclustered '
    Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
           + '(' + @Index_Key + ')'
    Select @IndDesc = ''
    if @IndStatus & 0x01 = 0x01
      Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
    if @IndStatus & 0x40 = 0x40
      Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
    if @IndDesc <> ''
    begin
      Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
      Select @Script = @Script + ' WITH ' + @IndDesc
    end
  end
  if (@strPri_Key = '')
    Insert into #spscript values(@Script,0)
  else
    update #spscript set Script = Script + @strPri_Key where LastLine = 1
 
  Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
Close Cursor_Index
Deallocate Cursor_Index

declare @line varchar(8000)
set @sql = ''
Declare Cursor_sql CURSOR for Select script from #spscript
Open Cursor_sql
Fetch Next from Cursor_sql into @line
while (@@FETCH_STATUS <> -1)
begin
    set @sql = @sql + ' ' + @line
    Fetch Next from Cursor_sql into @line
end
Close Cursor_sql
Deallocate Cursor_sql



set nocount off

return (0)

step 3: 在CloneServer 上创建执行克隆任务的存储过程

create procedure tableClone
@table nvarchar(1000)
As
begin
    declare @sql nvarchar(4000);
    declare @cols nvarchar(4000);
    set @cols=''
    execute [SarahCla].master.dbo.SP_GET_TABLE_INFO @table, @sql output, @cols output;

    --SarahCla记得改成自己的linkserver name

    --master 改成自己要克隆的表所在库的名称

    execute sp_executesql @sql;
    set @sql = ' set identity_insert tb3 on; insert into ' + @table + '(' + @cols + ')
    select ' + @cols + ' From [SarahCla\Sarahcla2008].master.dbo.' + @table + ' set identity_insert ' + @table + ' off '
    execute sp_executesql @sql;
end

step 4: 克隆表

例如：要克隆 tb3

execute tableClone 'tb3'