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 intdeclare @filename varchar(500)declare @filePath varchar(500)declare @rowid intdeclare @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.filepathinto ##tempfrom kp join fileTable att on att.modeid=kp.docidorder by kp.xh  --创建远程服务器映射关系 declare @cmd1 varchar(1000) set @cmd1='net use ' + @serverUrl + ' "' + @serverPwd + '" /user:"' + @serverName + @serverUser + '"' EXECmaster..xp_cmdshell @cmd1--print @cmd1--游标循环遍历导出附件declare cur cursor for select xh,rowid,zihao,title,modeid,fileid,fileName,FilePath from ##temp open curfetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepathwhile @@fetch_status=0Beginset @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]','_')--endif @title is null or @title=''beginset @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 +'"'EXECmaster..xp_cmdshell  @cmd,no_output--print @cmdset @cmd= 'copy  "' +@oriDir +replace(@filePath,'/','\')+'"' + ' "' +@outputDir +'\'+ @folder +'\'+cast(@rowid as varchar)+'_'+@filename+'"'EXECmaster..xp_cmdshell   @cmd--print  @cmdset @cmd=''fetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepathEndclose curdeallocate curdrop table ##temp