Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
I've said this elsewhere, but go get the RedGate tools. Surely you have better ways to spend your time than solve problems that have already been solved. Even if you just use the trial edition for a one-time task. –  Aaron Bertrand Apr 17 '13 at 19:53
add comment

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.