Best practice for copying tables from server to server Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)

copyTables

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


copyTable

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
This looks like a good working solution. Keep in mind however, that copying large amounts of data within a transaction will potentially grow your logfiles significantly. That costs a lot of time and can cause unexpected drive space shortages. Also, during that time the target tables are completely unaccessible for outside connection. The source tabes might also be blocked, depending on you transaction isolation level.

It might be better to break the actual data movement down into small batches. But then you would loose transactional consistency if concurrent writes happen.

As Aaron mentioned, you might also want to look into existing tools like DataCompare (would also not guarantee transactional consistency). If you have to execute this copy process regularly, you could also look into snapshot replication. (This would guarantee transactional consistency, but it would need to be setup by a sysadmin. However, after that initial setup you could start the synchronization yourself.)

Finally, if you go with your solution, make sure that in the error condition you first rollback the transaction and then record to the error log table, otherwise you would rollback that new entry too. You should also check that there is actually a transaction at the time of the error: IF XACT_STATE() != 0 ROLLBACK