从服务器到服务器复制表的最佳实践

不是 DBA 并且没有系统管理员权限来进行备份/恢复，以下是复制一组表的好解决方案吗？（我有一个链接服务器从serverA到serverB）

复制表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTables]
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @serverName varchar(50), @dbName varchar(50), @schemaName varchar(50)

    SET @serverName = QUOTENAME('serverB')
    SET @dbName = QUOTENAME('db')
    SET @schemaName = QUOTENAME('dbo')

    BEGIN TRY
        BEGIN TRANSACTION

        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl1', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl2', 'copyLog'
        EXEC [dbo].[copyTable] @serverName, @dbName, @schemaName, 'tbl3', 'copyLog'
        ...

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- Insert error into log table

        ROLLBACK
    END CATCH
END
GO

复制表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[copyTable]
    @serverName varchar(50),
    @dbName varchar(50),
    @schemaName varchar(50),
    @tableName varchar(50),
    @logTableName varchar(50)
AS
-- NOCOUNT ON added to prevent extra result sets from interfering
-- with SELECT statements. XACT_ABORT ON to terminate the transaction
-- and rollback if a run-time error occurs.
SET NOCOUNT, XACT_ABORT ON

BEGIN
    DECLARE @localFullName varchar(200), @localShortName varchar(150),
        @foreignFullName varchar(200), @logShortName varchar(150);

    SET @localFullName = QUOTENAME(@dbName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);
    SET @localShortName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @foreignFullName = QUOTENAME(@serverName) + '.' + QUOTENAME(@dbName)
        + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
    SET @logShortName = QUOTENAME(@logTableName) + '.' + QUOTENAME(@schemaName)
        + '.' + QUOTENAME(@tableName);

    IF EXISTS 
    (
        SELECT * 
        FROM sys.objects 
        WHERE object_id = OBJECT_ID(@localShortName) 
                AND type in (N'U')
    )
    BEGIN
        DROP TABLE @localShortName;
        SELECT * 
            INTO @localFullName 
            FROM @foreignFullName;

        INSERT INTO @logShortName (stamp, [message]) 
        VALUES 
            (
                    GETDATE(), 
                    'Table ' + @foreignFullName + ' was copied'
            );
    END
END