mssqlserver 复制拷贝附件sql脚本（二）之异地服务器导出

说明：以下脚本利用mssql实现附件从一个盘导出拷贝到另外一台服务器上面的另外一个盘（ps：数据库、原始附件所在位置部署在同一台服务器，导出附件则存放在与它们不同的另一台服务器）

--数据库、附件在同一台服务器，导出附件到另外一条服务器

declare @outputDir varchar(500)
declare @oriDir varchar(500)
declare @serverName varchar(500)   --存放附件服务器ip名称
declare @serverUrl varchar(500)    --存放附件服务器ip及存放磁盘路径
declare @serverPwd varchar(500)    --存放附件服务器密码
declare @serverUser varchar(500)   --存放附件服务器登录名
declare @cmd varchar(4000)
declare @desDir varchar(500)
declare @folder varchar(500)

declare @modeid int 
declare @fileid int
declare @filename varchar(500)
declare @filePath varchar(500)

declare @rowid int
declare @xh int 
declare @zihao varchar(1000)
declare @title varchar(1000)


--原始附件所在目录(因为数据库和附件在同一台服务器，所以系统自动识别数据库所在服务器下的盘符)
Set @oriDir='d:\FileSite' 
--要导出附件存放的服务器及所在目录
Set @outputDir='\\192.168.1.20\d$\CopyFileSite\TestFile'
--远程服务器地址、密码、用户(导出附件存放的服务器用户信息及存放盘符)
Set @serverUrl='\\192.168.1.20\d$'
Set @serverName='192.168.1.20\'
Set @serverUser='administrator' --最好用超级管理员，防止没有权限
Set @serverPwd='123456'

--从数据库查找附件名称、路径等信息（根据实际情况编写脚本）
select rowid=row_number() over(partition by kp.xh order by kp.xh),kp.xh,kp.docid,kp.zihao,kp.title
 ,att.filename,att.modeid,att.fileid,att.filepath
into ##temp
from kp
 join fileTable att on att.modeid=kp.docid
order by kp.xh
 
 --创建远程服务器映射关系
 declare @cmd1 varchar(1000)
 set @cmd1='net use ' + @serverUrl + ' "' + @serverPwd + '" /user:"' + @serverName + @serverUser + '"'
 EXEC	master..xp_cmdshell @cmd1
--print @cmd1

--游标循环遍历导出附件
declare cur cursor for 
	select xh,rowid,zihao,title,modeid,fileid,fileName,FilePath from ##temp 
open cur

fetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepath
while @@fetch_status=0
Begin
		set @zihao=replace(@zihao,char(9),'')
		set @zihao=replace(@zihao,char(10),'')
		set @zihao=replace(@zihao,char(11),'')
		set @zihao=replace(@zihao,char(12),'')
		set @zihao=replace(@zihao,char(13),'')
		set @zihao=replace(@zihao,'\','')
		set @zihao=replace(@zihao,'/','')
		set @zihao=replace(@zihao,':','')
		set @zihao=replace(@zihao,'*','')
		set @zihao=replace(@zihao,'?','')
		set @zihao=replace(@zihao,'"','')
		set @zihao=replace(@zihao,'<','')
		set @zihao=replace(@zihao,'>','')
		set @zihao=replace(@zihao,'|','')

		set @filename=replace(@filename,char(9),'')
		set @filename=replace(@filename,char(10),'')
		set @filename=replace(@filename,char(11),'')
		set @filename=replace(@filename,char(12),'')
		set @filename=replace(@filename,char(13),'')
		set @filename=replace(@filename,'\','')
		set @filename=replace(@filename,'/','')
		set @filename=replace(@filename,':','')
		set @filename=replace(@filename,'*','')
		set @filename=replace(@filename,'?','')
		set @filename=replace(@filename,'"','')
		set @filename=replace(@filename,'<','')
		set @filename=replace(@filename,'>','')
		set @filename=replace(@filename,'|','')
		
		set @title=replace(@title,char(9),'')
		set @title=replace(@title,char(10),'')
		set @title=replace(@title,char(11),'')
		set @title=replace(@title,char(12),'')
		set @title=replace(@title,char(13),'')
		set @title=replace(@title,'\','')
		set @title=replace(@title,'/','')
		set @title=replace(@title,':','')
		set @title=replace(@title,'*','')
		set @title=replace(@title,'?','')
		set @title=replace(@title,'"','')
		set @title=replace(@title,'<','')
		set @title=replace(@title,'>','')
		set @title=replace(@title,'|','')

	--去除加急的网页符号
		set @title=replace(@title,'[font color=red','')
		set @title=replace(@title,'font]','_')
	--end	
	if @title is null or @title=''
		begin
			set @title='无标题'
		end
	
	--序号+docid+文号+标题
	--d:\testdownload\1_29076_ 中国〔2009〕3号_关于附件异地导出脚本测试
	set @folder=rtrim(cast(@xh as varchar) + '_' + cast(@modeid as varchar) + '_' + @zihao + '_' + @title) 
	
	set @cmd='md "'+@outputDir + '\' + @folder +'"'	
	EXEC	master..xp_cmdshell  @cmd,no_output
--	print @cmd	
	
	set @cmd= 'copy  "' +@oriDir +replace(@filePath,'/','\')+'"' + ' "' +@outputDir +'\'+ @folder +'\'+cast(@rowid as varchar)+'_'+@filename+'"'
	EXEC	master..xp_cmdshell   @cmd
--	print  @cmd

	set @cmd=''
fetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepath
End
close cur
deallocate cur


drop table ##temp