从远程数据库复制全部数据到本地数据库

经常遇到这种情况：需要获得整个远程服务器上的SQLServer数据库，但是没有权限导出数据，而使用脚本生成的方法总有这样那样的问题，并且速度超慢。这两天查找了有关的工具，发现BCP 命令行工具适合大量导入导出，BULK INSERT （SQLServer2005之后支持）命令适合带有自动增长列的表数据导入。

首先要生成数据库的脚本，在本地建立数据库，这个了解SQL Server的基本上都知道。

然后使用下列脚本进行数据的复制了。

 

 1DECLARE @remoteServerNameOrIP VARCHAR(50);                --远程服务器名称或IP
 2SET @remoteServerNameOrIP = '205.100.100.100';
 3DECLARE @remoteServerUserName VARCHAR(50);                --远程服务器用户名
 4SET @remoteServerUserName = 'user';
 5DECLARE @remoteServerPassword VARCHAR(50);                --远程服务器密码
 6SET @remoteServerPassword = '123';
 7DECLARE @remoteServerDatabaseName VARCHAR(50);            --远程数据库名
 8SET @remoteServerDatabaseName = 'global';
 9DECLARE @localServerNameOrIP VARCHAR(50);                --本地服务器名称或IP
SET @localServerNameOrIP = '127.0.0.1';
DECLARE @localServerDatabaseName VARCHAR(50);            --本地数据库名
SET @localServerDatabaseName = 'global';
DECLARE @tableName VARCHAR(50);                            --远程\本地数据库中表名
DECLARE @fileDir VARCHAR(50);                            --导出\导入文件目录
SET @fileDir = 'D:\global\';
DECLARE @isSimple bit;                                        --是否单表
SET @isSimple = 1;
DECLARE @simpleTableName VARCHAR(50);    
SET @simpleTableName = 'NewsImages';

exec('declare #tb cursor for select name from '+@localServerDatabaseName+'..sysobjects where xtype=''U''')
open #tb
fetch next from #tb into @tableName
while @@fetch_status=0
begin
    --查看表数目
    EXEC('SELECT '''+@tableName+''', COUNT(*) FROM '+@localServerDatabaseName+'.dbo.'+@tableName);
    IF (@isSimple=0 OR (@isSimple=1 AND @tableName=@simpleTableName))
    BEGIN

    --导出远程数据库表数据到本地文件
    DECLARE @bcp VARCHAR(500);
    SET @bcp = 'BCP "'+@remoteServerDatabaseName+'.dbo.'+@tableName+'" out "'+@fileDir+@tableName+'.txt" -w -S"'+@remoteServerNameOrIP+'" -U"'+@remoteServerUserName+'" -P"'+@remoteServerPassword+'"';
    --EXEC master..xp_cmdshell @bcp;

    --导入数据文件到本地数据库
    DECLARE @bulk VARCHAR(500); 
    SET @bulk = 'BULK INSERT '+@localServerDatabaseName+'.dbo.'+@tableName+' FROM "'+@fileDir+@tableName+'.txt" 
                WITH(
                        CHECK_CONSTRAINTS,
                        DATAFILETYPE = ''widechar'',
                        KEEPIDENTITY,
                        KEEPNULLS,
                        MAXERRORS = 1000,
                    
    ERRORFILE = '''+@fileDir+@tableName+'_err.txt''
                    )';
    --EXEC(@bulk);
    END
fetch next from #tb into @tableName
end
close #tb
deallocate #tb