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
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
