SQLServer 通过导入数据的形式远程复制数据库(V2.0)

功能一、远程将数据库A -> 备份到另一个服务器中；功能二、对于长期执行的数据库会产生很大的日志，这样可以清理一些空间；

步骤：
0:开启远程功能
1:从源数据库中生成sql脚本
2:新建一个目标数据库，执行源数据库sql脚本来创建结构一样的数据库
3:执行sql语句输出导入数据sql语句
4:在目标数据库中执行导入数据sql语句

在源数据库与目标数据库中都开启远程功能

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

以下的所有Function、Procedure等SQL语句都是在源目标数据库执行

-- 判断此表是否有自增列
CREATE  FUNCTION [dbo].[HasIdentity] ( @Table NVARCHAR(200) )
RETURNS NVARCHAR(4000)
AS
    BEGIN
        DECLARE @sRs NVARCHAR(4000);
        SET @sRs = ISNULL(( SELECT  name + ','
                            FROM    sys.all_columns
                            WHERE   is_identity = 1
                                    AND object_id = ( SELECT  object_id
                                                      FROM    sys.all_objects
                                                      WHERE   type = 'u'
                                                              AND name = @Table
                                                    )
                          FOR
                            XML PATH('')
                          ), '');
        IF LEN(@sRs) > 0
            BEGIN
                SET @sRs = SUBSTRING(@sRs, 1, LEN(@sRs) - 1);
            END;
        RETURN @sRs;
    END;
GO

CREATE  FUNCTION [dbo].[GetColumns] ( @Table NVARCHAR(200) )
RETURNS NVARCHAR(4000)
AS
    BEGIN
        DECLARE @sRs NVARCHAR(4000);
        SET @sRs = ( SELECT name + ','
                     FROM   sys.all_columns
                     WHERE  object_id = ( SELECT    object_id
                                          FROM      sys.all_objects
                                          WHERE     type = 'u'
                                                    AND name = @Table
                                        )
                   FOR
                     XML PATH('')
                   );
        SET @sRs = SUBSTRING(@sRs, 1, LEN(@sRs) - 1);
        RETURN @sRs;
    END;
GO

-- 创建专用于远程传输的视图
CREATE PROCEDURE CreateViewForRemoteInsert ( @Table NVARCHAR(200) )
AS
    BEGIN	
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = 'CREATE VIEW For_Remote_Insert_'+@Table+' AS SELECT ';
        DECLARE Column_Cursor CURSOR SCROLL
        FOR
            SELECT  COLUMN_NAME , DATA_TYPE
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE   TABLE_NAME = @Table; 
        OPEN Column_Cursor;
        DECLARE @COLUMNNAME VARCHAR(50) ,
            @DATATYPE VARCHAR(50);
        FETCH FIRST FROM Column_Cursor 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 Column_Cursor INTO @COLUMNNAME, @DATATYPE; 
            END;
        SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1);
        SET @sql = @sql + ' FROM '+@Table;
        EXEC (@sql);
        CLOSE Column_Cursor;
        DEALLOCATE Column_Cursor;
    END;

-- 打印所有远程执行SQL插入语句，将查询结果全部拷贝粘贴到目标数据库执行
DECLARE @sql NVARCHAR(4000);
DECLARE @src NVARCHAR(100);
DECLARE @table NVARCHAR(200);
DECLARE @cols NVARCHAR(4000);
SET @src = 'SCZL正式.dbo.'; -- 指定源数据库全称

DECLARE Cur112 CURSOR
FOR
    SELECT  [name]
    FROM    [sysobjects]
    WHERE   [type] = 'u'
            AND [name] NOT IN ( 'dtproperties', 'sysdiagrams' )
    ORDER BY [name];
OPEN Cur112;
FETCH NEXT FROM Cur112 INTO @table;
WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC CreateViewForRemoteInsert @table;
        SET @sql = '';
        IF ( LEN(dbo.HasIdentity(@table)) > 0 )--存在自增列
            BEGIN
                SET @cols = dbo.GetColumns(@table);
                SET @sql = @sql + 'SET IDENTITY_INSERT ' + @table + ' on;';
                SET @sql = @sql + 'INSERT INTO ' + @table + '(' + @cols
                    + ') select * from openrowset( ''SQLOLEDB '', ''192.168.131.xxx''; ''sa''; ''xxx'','
                    + @src + 'For_Remote_Insert_' + @table + ');';
                SET @sql = @sql + 'SET IDENTITY_INSERT ' + @table + ' off;';
            END;
        ELSE
            BEGIN
                SET @sql = @sql + 'insert into ' + @table
                    + ' select * from openrowset( ''SQLOLEDB '', ''192.168.131.xxx''; ''sa''; ''xxx'','
                    + @src + 'For_Remote_Insert_' + @table + ');';
            END;
        PRINT @sql;
        FETCH NEXT FROM Cur112 INTO @table;
    END;
CLOSE Cur112;
DEALLOCATE Cur112;

-- 传输成功后，将这些视图都DROP掉
DECLARE @TableName NVARCHAR(200)
DECLARE ClearCursor CURSOR
FOR 
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%For_Remote_Insert_%'
OPEN ClearCursor;
FETCH NEXT FROM ClearCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC('DROP VIEW ' + @TableName);
        FETCH NEXT FROM ClearCursor INTO @TableName;
END

__EOF__

本文作者：徐徐赵赵
本文链接：https://www.cnblogs.com/xuxuzhaozhao/p/16921185.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！