SQLsever 跨库查询、备份

跨库查询

实用场景：
测试库dbA 在服务器 192.168.0.27
正式库dbB 在服务器 192.168.0.89

如何从dbB中插入数据到dbA中呢？

原始方法：直接导出数据，sqlsever会自动生成脚本，把拿来的脚本筛选 insert 语句 然后放到dbA中执行

更简单的方法就是直接连接两个服务器，形成互通性
链接服务器语句：

SELECT TOP ( 10 )
        *
FROM    OPENDATASOURCE('SQLOLEDB',
                       'Data Source=IP;User ID=D登录名;Password=密码').库名.dbo.表明;

1、先开启访问

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

--关闭
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

2、在测试库中执行链接插入测试表中

INSERT  [dbo].[SY_S_OPERATION]
        ( [FACILITY] ,
          [SITE] ,
          [OPERATION] ,
          [ACTIVE_INACTIVE] ,
          [SHIPING_ALLOW] ,
          [CREATOR] ,
          [CREATE_DATE]
        )
        SELECT TOP ( 100 )
               *
        FROM    OPENDATASOURCE('SQLOLEDB',
                               'Data Source=192.168.0.123;User ID=sa;Password=12345678@3￥5').iROCK.dbo.SY_S_OPERATION;

跨库备份

实用场景：
系统数据量太多，备份失败等情况
优点：快速备份到本地、能保证功能顺利打开运行，合适备份系统跑路
缺点：不能备份存储过程，触发器、自建函数等，需要另外手动备份

--当前库所有表
SELECT TABLE_NAME,
       RANK() OVER (ORDER BY TABLE_NAME ASC) AS [INDEX]
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;

/*****远程库**********/
--备份库的所有表
DROP TABLE #SYS_TABLE
SELECT TABLE_NAME,
       RANK() OVER (ORDER BY TABLE_NAME ASC) AS [INDEX]
INTO #SYS_TABLE
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=远程库IP;User ID=登录名;Password=密码')
     .数据库名.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;

DECLARE @TableCount INT = 0; --备份表总数
SELECT @TableCount = MAX([INDEX]) FROM #SYS_TABLE;
--PRINT @TableCount
DECLARE @TableName NVARCHAR(100);
DECLARE @Count INT = 1; --循环索引
DECLARE @Sqlquery NVARCHAR(MAX);
--循环每一张 并且 INTO
WHILE @Count <= @TableCount
BEGIN
    SELECT @TableName = TABLE_NAME FROM #SYS_TABLE WHERE [INDEX] = @Count;
    --根据备份条件 可以全部备份，这里是每张表 前1w条
    SET @Sqlquery = N'
	SELECT TOP(10000)*
	INTO ' + @TableName + N'
	FROM    OPENDATASOURCE(''SQLOLEDB'',
						   ''Data Source=192.168.0.***;User ID=***;Password=*****'').远程库名.dbo.
	' + @TableName;
    EXECUTE (@Sqlquery);
    PRINT @TableName;
    SET @Count = @Count + 1;
END;