sql 备份到网上邻居上

  1 EXEC sp_configure 'show advanced options', 1    
  2 
  3  
  4 
  5 GO   
  6 
  7  
  8 
  9 RECONFIGURE  WITH OVERRIDE   
 10 
 11  
 12 
 13 GO 
 14 
 15  
 16 
 17 --以下写入作业
 18 
 19  
 20 
 21 EXEC sp_configure'xp_cmdshell', 1     
 22 
 23 GO   
 24 
 25 RECONFIGURE      
 26 
 27 GO 
 28 
 29  
 30 
 31 declare @sql varchar(4000) 
 32 
 33 declare @backupfile varchar(2000)
 34 
 35 declare @retaindays int
 36 
 37 declare @now datetime
 38 
 39 declare @deletefiles varchar(2000)
 40 
 41 declare @cmd varchar(2000)
 42 
 43 declare @i int
 44 
 45  
 46 
 47  
 48 
 49 declare @User varchar(2000)
 50 
 51 declare @Pwd varchar(2000)
 52 
 53 declare @Store varchar(2000)
 54 
 55 declare @IPPart varchar(2000)
 56 
 57 declare @IP varchar(2000)
 58 
 59  
 60 
 61  
 62 
 63 set @Store='EF_DATA'      --数据库名
 64 
 65 set @User ='administrator'    --用户名(异地服务器)-----要管理员权限
 66 
 67 set @Pwd ='abcd159357'      --密码（异地服务器）-------密码不要有符号
 68 
 69 set @IPPart='数据交换'        --路径(异地服务器的共享目录，此目录要有有上面用户的访问读写权限)
 70 
 71 set @IP='192.168.1.7' 
 72 
 73 set @retaindays=6         --要保留备份的天数
 74 
 75  
 76 
 77  
 78 
 79 --建立映射
 80 
 81 set @cmd='net use x: \\'+ @IP +'\'+ @IPPart + ' '+ @Pwd +' /user:'+ @IP +'\'+ @User
 82 
 83 exec master..xp_cmdshell @cmd
 84 
 85  
 86 
 87  
 88 
 89 --删除以前的备份
 90 
 91 set @now=getdate()
 92 
 93 set @i=0
 94 
 95 while (@i < 30)
 96 
 97 begin
 98 
 99     set @deletefiles = 'x:\*' +convert(varchar(8),dateadd(dd,-@retaindays-@i,@now),112)+'*.*'
100 
101     set @cmd='del ' + @deletefiles
102 
103     exec master..xp_cmdshell @cmd
104 
105     set @i = @i +1
106 
107 end
108 
109  
110 
111 --开始备份
112 
113 set @backupfile='x:\'+ @Store + '_db_'+
114 
115     replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.BAK'
116 
117 set @sql='backup database ' + @Store + ' to disk='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
118 
119 --print @sql
120 
121 exec (@sql)
122 
123  
124 
125 set @backupfile='x:\'+ @Store +'_tlog_'+
126 
127     replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.TRN'
128 
129 set @sql='backup LOG ' + @Store + ' TO DISK='''+@backupfile+''' with retaindays='+convert(varchar(10),@retaindays)
130 
131 --print @sql
132 
133 exec (@sql)
134 
135  
136 
137  
138 
139 --删除映射
140 
141 exec master..xp_cmdshell'net use x: /delete'
142 
143  
144 
145 --关闭允许执行xp_cmdshell
146 
147 EXEC sp_configure'xp_cmdshell', 0    
148 
149 GO     
150 
151 RECONFIGURE WITH OVERRIDE    
152 
153 GO