기능:

• 원격 서버에 데이터베이스 A를 백업
• 장기간 운영되는 데이터베이스의 로그 파일 크기 줄이기

절차: 0. 원격 기능 활성화

1. 소스 데이터베이스에서 SQL 스크립트 생성
2. 새 대상 데이터베이스 생성 및 소스 스크립트 실행으로 구조 복제
3. 데이터 이입을 위한 SQL 문 생성
4. 대상 데이터베이스에서 데이터 이입 SQL 실행

원격 기능 활성화

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

데이터베이스 테이블용 원격 전송용 뷰 생성

CREATE PROCEDURE CreateRemoteInsertView (@TableName NVARCHAR(200))
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'CREATE VIEW RemoteInsert_' + @TableName + ' AS SELECT ';
    DECLARE @ColumnName VARCHAR(50), @DataType VARCHAR(50);
    DECLARE ColumnCursor CURSOR FOR
        SELECT COLUMN_NAME, DATA_TYPE 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName;

    OPEN ColumnCursor;
    FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @DataType = 'xml'
            SET @sql = @sql + 'CONVERT(NVARCHAR(MAX), ' + @ColumnName + ') AS ' + @ColumnName + ',';
        ELSE
            SET @sql = @sql + @ColumnName + ',';
        FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType;
    END;

    SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1);
    SET @sql = @sql + ' FROM ' + @TableName;
    EXEC (@sql);
    CLOSE ColumnCursor;
    DEALLOCATE ColumnCursor;
END;
GO

데이터 원격 전송

DECLARE @sql NVARCHAR(4000), @src NVARCHAR(100), @tableName NVARCHAR(200);
SET @src = 'sourceDB.dbo.'; -- 소스 데이터베이스명 지정

DECLARE TableCursor CURSOR FOR
SELECT name FROM sysobjects WHERE type = 'u' AND name NOT IN ('dtproperties', 'sysdiagrams') ORDER BY name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC CreateRemoteInsertView @tableName;
    SET @sql = '';

    IF EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@tableName) AND is_identity = 1)
    BEGIN
        DECLARE @cols NVARCHAR(4000);
        SET @cols = (SELECT name + ',' FROM sys.all_columns WHERE object_id = OBJECT_ID(@tableName) FOR XML PATH(''));
        SET @cols = SUBSTRING(@cols, 0, LEN(@cols) - 1);

        SET @sql = 'SET IDENTITY_INSERT ' + @tableName + ' ON;' + 
                   'INSERT INTO ' + @tableName + '(' + @cols + ')' + 
                   'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''remoteServer;uid=sa;pwd=xxx'', ''' + @src + 'RemoteInsert_' + @tableName + ''');' +
                   'SET IDENTITY_INSERT ' + @tableName + ' OFF;';
    END
    ELSE
    BEGIN
        SET @sql = 'INSERT INTO ' + @tableName + 
                   'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''remoteServer;uid=sa;pwd=xxx'', ''' + @src + 'RemoteInsert_' + @tableName + ''');';
    END;

    PRINT @sql;
    FETCH NEXT FROM TableCursor INTO @tableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;

생성된 뷰 삭제

DECLARE @viewName NVARCHAR(200);
DECLARE CleanupCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'RemoteInsert%' AND TABLE_NAME NOT LIKE 'sys%';

OPEN CleanupCursor;
FETCH NEXT FROM CleanupCursor INTO @viewName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('DROP VIEW ' + @viewName);
FETCH NEXT FROM CleanupCursor INTO @viewName;
END;
CLOSE CleanupCursor;
DEALLOCATE CleanupCursor;