[置顶] 通过SQLServer的xp_cmdshell在服务器之间传送文件

xp_cmdshell作为SQL Server的扩展存储过程之一，也是SQL Server在安全大敌，很多SQL安全手册上都要求关闭此过程，这不利用其特性简要实现了一个在SQL服务器之间传取文件的功能，在SQL2005下测试通过，现贴出代码下，大家共赏之

/*
 * 脚本：通过SQLServer的xp_cmdshell在服务器之间传送文件
   通过SQL SQLServer实现，要求支持远程访问，并且均开启xp_cmdshell选项
 * 作者: qin
 * 整理: 2013.07.07 13:20
 * 
 * E-Mail:[e-mail]
 * QQ:45958462
 */

/*
--开启高级选项
EXEC sp_configure 'show Advanced options','1'
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE 
*/
set nocount on;

--参数
declare @FromInstrance nvarchar(100),@FromDB nvarchar(100),@FromPWD nvarchar(100);
declare @ToInstrance nvarchar(100),@ToDB nvarchar(100),@ToPWD nvarchar(100);
declare @sql nvarchar(max),@cmd nvarchar(4000);
declare @file_table nvarchar(100); 
declare @upfile_path varchar(100),@newfile_path varchar(100),@downfile_name varchar(100);
declare @error_flag int;
set nocount on;

--修改参数值
--文件名
set @upfile_path='D:\Data_bak\test.bak';--上传文件名
set @newfile_path=@upfile_path;--预留
set @downfile_name='D:\Data_bak\test3.bak';--下载后文件名
--源服务器
set @FromInstrance = '192.168.80.1';
set @FromPWD = '123asd';
set @FromDB = 'tempdb';
--目标服务器
set @ToInstrance = '192.168.80.130'; 
set @ToPWD = '123asd';
set @ToDB = 'tempdb';

--正式执行，不要修改
set @file_table='[tmp_file_'+cast(RAND()*1000000 AS VARCHAR)+']';
print 'select *,datalength(sText) file_len from tempdb.dbo.'+@file_table;

--上传
--1、生成upload.sql脚本(删除表+上传)
set @sql='if object_id(''tempdb..'+@file_table+''') is null
create table tempdb..'+@file_table+'(id int,sText nvarchar(max));
truncate table tempdb..'+@file_table+';

insert into tempdb..'+@file_table+'(id)values(1);
update tempdb..'+@file_table+' set sText=(select BULKColumn FROM OPENROWSET(BULK N'''+@newfile_path+''', SINGLE_BLOB) AS Data) where id=1;';
--print @sql;


--2、在本地释放文件upload.sql
if object_id('tempdb.dbo.tmp_table') is not null
drop table tempdb.dbo.tmp_table;
select cast(@sql as nvarchar(max))as sText into tempdb.dbo.tmp_table;
exec master..xp_cmdshell 'bcp tempdb.dbo.tmp_table out "c:\upload.sql" -T -t"|" -w -q',no_output;
--select * from tempdb.dbo.tmp_table

--3、连接源端执行upload.sql，文件放在源端tempdb库
set @cmd='osql -S '+@FromInstrance+' -U sa -P '+@FromPWD+' /d '+@FromDB+' /i c:\upload.sql';
--print (@cmd);
exec master..xp_cmdshell @cmd,no_output;


--下载
--1、创建download.sql脚本
set @sql='exec master..xp_cmdshell ''bcp "select sText from tempdb.dbo.'+@file_table+' where ID = 1" queryout "'+@downfile_name+'" -N -q -S"'+@FromInstrance+'" -U"sa" -P"'+@FromPWD+'" '''
--print @cmd;

--2、在本地释放文件download.sql
if object_id('tempdb.dbo.tmp_table') is not null
drop table tempdb.dbo.tmp_table;
select cast(@sql as nvarchar(max))as sText into tempdb.dbo.tmp_table;
exec master..xp_cmdshell 'bcp tempdb.dbo.tmp_table out "c:\download.sql" -T -t"|" -w -q',no_output;
--select * from tempdb.dbo.tmp_table


--3、连接源端执行upload.sql，文件放在源端tempdb库
set @cmd='osql -S '+@ToInstrance+' -U sa -P '+@ToPWD+' /d '+@ToDB+' /i c:\download.sql';
--print (@cmd);
exec master..xp_cmdshell @cmd,no_output;


--恢复现场：删除源端临时表
--1、生成upload.sql脚本(删除表)
set @sql='if object_id(''tempdb..'+@file_table+''') is not null
drop table tempdb..'+@file_table+';';
--print @sql;

--2、在本地释放文件upload.sql
if object_id('tempdb.dbo.tmp_table') is not null
drop table tempdb.dbo.tmp_table;
select cast(@sql as nvarchar(max))as sText into tempdb.dbo.tmp_table;
exec master..xp_cmdshell 'bcp tempdb.dbo.tmp_table out "c:\upload.sql" -T -t"|" -w -q',no_output;
--select * from tempdb.dbo.tmp_table

--3、连接源端执行upload.sql(执行删除表)
set @cmd='osql -S '+@FromInstrance+' -U sa -P '+@FromPWD+' /d '+@FromDB+' /i c:\upload.sql';
--print (@cmd);
exec master..xp_cmdshell @cmd,no_output;

--4、删除本地表
if object_id('tempdb.dbo.tmp_table') is not null
drop table tempdb.dbo.tmp_table;

--5、删除临时文件
set @cmd='if exist c:\upload.sql del c:\upload.sql /f /s /q';
exec master..xp_cmdshell @cmd,no_output;

set @cmd='if exist c:\download.sql del c:\download.sql /f /s /q';
exec master..xp_cmdshell @cmd,no_output;


/*
--关闭高级选项
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show Advanced options','0'
RECONFIGURE WITH OVERRIDE 
*/