sql server 创建备份任务后sa锁定 sql创建备份设备的代码

 目的：1.通用易于移植的备份方案。

2.易于监控备份情况。

3.方便选择不同组合备份方案。

下面先贴上说明：具体能实现的方案说明参见说明文档。

1. 备份方案参数说明 
2.  
3.  @Bakdbname Nvarchar(100)         --备份数据库名 
4. ,@BAKFILE NVARCHAR(100)       --备份集文件路径 
5. ,@ismirror int        =0
6. ,@BAKMIRFILE NVARCHAR(100) =NULL    --镜像备份集文件路径,默认是null 
7.                                --一般设置网络路径例如'\\192.168.6.100\sqlbak\' 
8. ,@devtype varchar(20) ='DISK'       --备份设备默认'disk' 
9. ,@D_EXP_DATE INT      =28
10. ,@I_EXP_DATE INT      =7
11. ,@iit INT             =2
12. ,@DbakBtime int       =3--完整或者差异备份开始时间点 默认 3点 
13. ,@DbakEtime int       =7--完整或者差异备份结束时间点 默认 7点 
14.  
15.  
16. D备份为完整备份 
17. I备份为差异备份 
18. L备份为日志备份 
19. --  参数说明 
20.  
21. --1.备份以最近的该库的D备份作为集合的起点，每个I和D备份为一个备份分块起点并设置每个分块备份的过期时间为 创建时间+@I_EXP_DATE。 
22.  
23. --2.当备份分块个数为 @D_EXP_DATE INT/@I_EXP_DATE 个个数的时候且最后的备份分块已经过期，则开始自动切到下一个完整备份集进行备份作业。如果已经是最大的备份集，则从头第一个备份集开始做备份作业 
24.  
25. --3.参数设置上需要注意 
26. >=@I_EXP_DATE 
27. >0 @I_EXP_DATE>=0 
28. >=1  代表需要最少保留的历史备份集合为@iit-1个最多设置999个！ 
29.      d.@ismirror ,@BAKMIRFILE 
30.      @ismirror 确定是否使用镜像备份集为主备份集做一个镜像，如果为1则做， 
31.      当@ismirror为1的时候@bakmirfile镜像备份集的存放路径(可以是网络路径)参数    一定不能为NULL切需要保证可用！ 
32.      
33.   程序利用备份参数设置来实现不同的备份方案！ 
34. 设置的@D_EXP_DATE 为一个D备份开始的整体的备份集有效期间隔 
35.       @I_EXP_DATE为单个D和单个I备份的备份块有效期间隔 
36. 例如 @I_EXP_DATE=7则代表 从D开始算，每7天需要做一个I备份知道 D备份个数+I备份个数达到@D_EXP_DATE /@I_EXP_DATE（除法使用向上取整）个，且最后的备份区块也过期，则会自动轮换到下一个备份集做D备份。 
37. 在没有过期的这段时间间隔里，如果执行作业将会进行L备份！ 
38.    
39. 4.举例来说： 
40.    
41.   @D_EXP_DATE ,@I_EXP_DATE,@iit 三个参数将涉及到备份策略下面举例说明。 
42.    
43.   支持三种备份方案 
44.   1.方案为 D+I+L 
45.   每28天做一次D备份 
46.   每7天做一次I备份 
47.   其它时间每小时做一次L备份 
48.   至少保持一个历史的备份集（既保持最最少28最多56天的历史备份记录） 
49.    
50.    
51.   参数设置如下： 
52. D_EXP_DATE=28
53. I_EXP_DATE=7
54. iit=2
55. >=28/7=4
56.  
57. 用户可以定义@DbakBtime ,@DbakEtime 起止时间点来确定做D和I备份的时间范围。例如值允许系统 不是很忙得晚上2点到3点之间做等 
58.    
59.    
60.    
61.   2.方案为 D+L 
62.   每7天做一次D备份 
63.   每小时做L备份 
64.   参数设置如下： 
65. D_EXP_DATE=7
66. I_EXP_DATE=7
67. >=7/7=1
68.   这样就达到了 D+L的备份策略 
69.    
70.    
71.   3.方案为 D 
72.   每天做一个D 
73.    
74. D_EXP_DATE=1
75. I_EXP_DATE=1
76.    
77. >=1/11=1 且最后的I备份或者D备份也已经过期的情况下，则会进行下一个备份集的备份操作，而保留之前这个完全饱和的备份集！ 
78.   这样就达到了只做 D的备份策略 
79.    
80.    
81.   如果要更细分的 例如 D备份的时间间隔不是以天为单位 对大型数据库意义不大也不是很现实！所有 备份作业时间间隔都是以天作为单位 
82.  
83.    
84.   4.方案为 D+I 
85.    
86.    
87.   每天10天一个D备份 
88.   每1天一个I备份 
89.    
90.   参数如下 
91. D_EXP_DATE=10
92. I_EXP_DATE=1
93.    
94. >=10/11=1 且最后的I备份或者D备份也已经过期的情况下，则会进行下一个备份集的备份操作，而保留之前这个完全饱和的备份集！ 
95.  这样就实现了D+I备份的备份策略！ 
96.    
97.    
98.  
99. 注释 D备份为完整备份 
100.      I备份为差异备份 
101.      L备份为日志备份 
102.  
103.  
104. 该备份任务作业优点如下： 
105.     快速部署提供便利，只需要在master中建立对应存储过程即可 
106.     灵活方便，随意设置备份集个数，以及每个备份集的备份文件有效期天数，通过不同参数的设置即可实现各种不同需求的备份方案组合 
107.     自动覆盖过去备份集，省去对过期备份集的额外处理 
108.     提供镜像备份集，将备份同时放到两个地方，以防备份出现丢失等意外 
109.     提供简单快捷的备份任务监控语句，通过语句即可轻松监控每次备份任务的运行情况 
110. 例如 
111. 该脚本注意事项： 
112.     如果设置了多个定时任务，一定要将每个定时任务执行的时间错开，目前的机制是要使用到备份设备，每次备份会自动生成，所以防止互相干扰。如果放到一个定时任务中则可以一起运行。 
113.     如果要监控备份执行情况，请运行“备份监控语句”查看每个备份的执行情况！

错误代码含义：

0:执行正常！ 11：首次完整备份时间段不合法！ 12：备份作业执行异常！ 13：镜像备份集参数配置未合格！

 

贴上使用方式的存储过程代码：

 

1. USE [msdb] 
2. GO 
3.  
4. /****** Object:  StoredProcedure [dbo].[USP_Bak_Sche]    Script Date: 2012/12/26 11:24:33 ******/ 
5. /*作者：泥 巴                ******/ 
6. /*日期：20130322         ******/ 
7. SET ANSI_NULLS ON
8. GO 
9.  
10. SET QUOTED_IDENTIFIER ON
11. GO 
12.  
13. CREATE
14. ( 
15. --备份数据库名
16. ,@BAKFILE NVARCHAR(100)       --备份集文件路径
17. ,@ismirror int        =0       --是否建立镜像备份集，默认为0不建立
18. ,@BAKMIRFILE NVARCHAR(100) =NULL    --镜像备份集文件路径,默认是null
19. ,@devtype varchar(20) ='DISK'       --默认'disk'
20. ,@D_EXP_DATE INT      =28       --备份集过期时间单位 (天) 默认 28天 
21. ,@I_EXP_DATE INT      =7       --差异和完整备份过期时间单位(天) 默认  7天
22. ,@iit INT             =2        --备份集数目用于交替备份到同备份集 默认2
23. ,@DbakBtime int       =3--完整或者差异备份开始时间点 默认 3点
24. ,@DbakEtime int       =7--完整或者差异备份结束时间点 默认 7点
25.  
26.  
27. ) 
28. AS
29. BEGIN
30.  
31. if @ismirror=1 begin
32. if @BAKMIRFILE is null begin
33. print N'用户选择开启镜像备份集，但是镜像备份路径参数@bakmirfile不能为NULL！'
34. print N'注意：中途更改这个参数@ismirror会自动在每次做新的备份集的时候才会生效。'
35. return
36. end
37. end
38.  
39. --参数校正
40.  
41. IF @D_EXP_DATE<=0 
42. BEGIN
43.  
44. SET @D_EXP_DATE=(CASE WHEN @D_EXP_DATE<=0 THEN 1 ELSE @D_EXP_DATE END) 
45. PRINT N'备份集参数过期设置@d_exp_date 不能小于1,系统自动修正为1!'
46.  
47. END
48.  
49. IF @D_EXP_DATE<@I_EXP_DATE 
50.  
51. BEGIN
52.  
53. SET
54. PRINT N'备份块过期值应该小于或等于备份集过期值，参数已经修正为相等！'
55.  
56. END
57.  
58. --set @allbgname=''
59. declare @it int                 --循环种子
60. ,@baklogicname varchar(20)      --备份集逻辑名
61. ,@bakfilename varchar(100)      --备份集路径文件名
62. ,@MIRbaklogicname varchar(20)   --镜像备份集逻辑名
63. ,@MIRbakfilename varchar(100)   --镜像备份集路径文件名
64. ,@bacname Nvarchar(100)         --备份名称
65. ,@bacmeidaname Nvarchar(100)    --备份媒体名称
66. ,@sql   Nvarchar(2000) 
67. set @bacname=@Bakdbname+'_BAK'
68. set @bacmeidaname=@Bakdbname+'_MEDIA'
69. --建立所需要的备份集合
70. set
71. while @it<=@iit 
72. begin
73. set @baklogicname='My_bak'+ convert(varchar(10),@it) 
74. set @bakfilename=@BAKFILE+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
75. IF NOT EXISTS(SELECT * FROM sys.backup_devices WHERE name=@baklogicname) 
76. BEGIN
77. exec
78. end
79. IF @ISMIRROR=1 
80. BEGIN
81. set @MIRbaklogicname='MIRROR_'+@baklogicname 
82. set @MIRbakfilename=@BAKMIRFILE+'MIRROR_'+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
83. IF NOT EXISTS(SELECT 1 FROM sys.backup_devices WHERE name=@MIRbaklogicname) 
84. BEGIN
85. exec
86.  
87. END
88. END
89.  
90. set
91.  
92. end
93.  
94. --建立当前活动备份集的临时表
95.  
96.  
97.  
98. ;WITH
99. AS
100. ( 
101. select A.database_name,A.backup_set_id,position, type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
102. join (select database_name,MAX(backup_set_id) backup_set_id from
103. where database_name=@Bakdbname and type='D'
104. group by
105. on a.backup_set_id=b.backup_set_id and
106. --找出同组的备份组集合所有
107. union all
108. select A.database_name,A.backup_set_id,a.position, a.type,a.first_lsn,a.last_lsn,a.checkpoint_lsn,a.database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
109. join
110. select A.database_name,A.backup_set_id,position, A.type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
111. join (select database_name,MAX(backup_set_id) backup_set_id from
112. where database_name=@Bakdbname and type='D'
113. group by
114. on a.backup_set_id=b.backup_set_id and
115. ) b 
116. on a.database_backup_lsn=b.checkpoint_lsn AND
117. where a.type <>'D'
118.  
119. ) 
120. select  bf.*,cf.exp_day into
121. from
122. join
123. (select
124. bcs.backup_set_id, 
125. bcs.position, 
126. [BACK_TYPE]=bcs.type, 
127. [bacname]=bcs.name, 
128. bcs.database_name, 
129. [medianame]=bms.name, 
130. [BACdevicename]=bmf.logical_device_name, 
131. [dev_type]=bmf.device_type, 
132. [PHYS_DEVICE_NAME]=bmf.physical_device_name, 
133. bcs.expiration_date, 
134. --bcs.backup_set_uuid,
135. bcs.database_backup_lsn, 
136. bcs.first_lsn, 
137. bcs.last_lsn, 
138. bcs.checkpoint_lsn, 
139. --bcs.database_creation_date,
140. bcs.backup_start_date, 
141. bcs.backup_finish_date, 
142. [size/M]=bcs.compressed_backup_size/1024/1024 
143. from
144. join
145. join
146. on
147. on
148. on
149. where BF.database_name=@Bakdbname and BACdevicename in
150. select name from
151. where name like '%My_bak%'
152. ) 
153. order by
154.  
155. --初始备份集参数
156. set
157. --判断需要备份的数据库是否有D备份在所有备份集中
158. --
159. IF  exists( 
160. select * from #tempbak where BACK_TYPE='D'
161. ) 
162. --有D备份存在的情况
163. begin
164. --用历史备份集参数来重新赋值,以保证一个没有完全饱和的备份集参数都是一致的
165.  
166. select @it=SUBSTRING(BACdevicename,7,3) from #tempbak  where back_type='D' and BACdevicename like 'My_bak%'
167. select @baklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
168. select @MIRbaklogicname='MIRROR_'+@baklogicname 
169. select @bacmeidaname=MEDIANAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
170. SELEct @bacname=bacname FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
171. --set @baklogicname='My_bak'+ convert(varchar(10),@it)
172. SELECT @bakfilename=[PHYS_DEVICE_NAME]  from #tempbak  where back_type='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
173. select @MIRbaklogicname= [BACdevicename] from #tempbak where back_type='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
174. select @MIRbakfilename= [PHYS_DEVICE_NAME]  from #tempbak  where back_type='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
175.  
176. --判断历史D备中如果没有MIRROR备份集那么把标签自动设置为0
177.  
178. --如果
179.  
180.  
181. IF datepart(hh,GETDATE()) >=@DbakBtime and
182. --在3点到7点之间
183. begin
184. --判断最后的差异或者D备是否已经过期
185. select CONVERT(VARCHAR(8),MAX(expiration_date),112) exp_day FROM #TEMPBAK)<=CONVERT(VARCHAR(8),GETDATE(),112) 
186. --备份块已经过期
187. begin
188. --检查D+I备的个数是否已经饱和
189. select COUNT(1) from #tempbak  where back_type in ('D','I') and BACdevicename like 'My_bak%')>= ceiling(convert(numeric(19,2),@D_EXP_DATE)/convert(numeric(19,2),case  when @I_EXP_DATE<=0 then @D_EXP_DATE else @I_EXP_DATE end)) 
190. --备份分块已经饱和
191. begin
192.             IF @it<@iit 
193. --备份集还没有达到最大
194. begin
195. --设置备份集参数为下一个
196. select @it=SUBSTRING(BACdevicename,7,3) from #tempbak  where back_type='D' and BACdevicename like 'My_bak%'
197. set
198. GOTO
199. end
200.              
201. else
202. --备份集已经达到最大
203. begin
204. --设置备份集参数为初始第一个
205. set
206. GOTO
207. end
208.              
209. end
210. --备份集没有饱和
211. else
212. begin
213. goto
214. end
215. end
216.      
217. ELSE
218. --备份分块没有过期
219. begin
220. goto
221. end
222.  
223. end
224. --当前时间不在完整和差异备份时间段之间
225. else begin
226. --做L备份
227. goto
228. end
229. end
230. --在没有D备份存在的情况
231. else begin
232.      
233. set
234. --当前时间在完整和差异备份时间段之间
235. IF datepart(hh,GETDATE()) >=@DbakBtime and
236. begin
237. GOTO
238. end
239. --当前时间不在完整和差异备份时间段之间
240. else begin
241.  
242. '当前的备份集下还没有过任何D备份,且不在做D备份时间段，请选择正确时间段运行以便首次建立D备份！请确认设置的时间段合法!'
243. return
244.      
245. end
246.  
247. end
248.  
249.  
250.  
251. --第创建使用fomat创建媒体集和备份集以及镜像备份集
252.  
253. Dbak: 
254. begin
255. set @baklogicname='My_bak'+ convert(varchar(10),@it) 
256. SET @MIRbaklogicname='MIRROR_'+@baklogicname 
257. set @bacname=@Bakdbname+'_BAK'
258. set @bacmeidaname=@Bakdbname+'_MEDIA'
259. set @bakfilename=@BAKFILE+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
260. set @MIRbakfilename=@BAKMIRFILE+'MIRROR_'+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
261. --重建基础备份设备
262. SELECT 1 FROM sys.backup_devices WHERE name
263. begin
264. exec
265. exec
266. end else exec
267. --重建镜像备份设备
268.             IF @ismirror=1  
269. BEGIN
270. SELECT 1 FROM sys.backup_devices WHERE name
271. begin
272. exec
273. exec
274. end else exec
275. END
276.              
277. IF @ismirror=1  
278. begin
279. set
280. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
281. MIRROR TO '+@MIRbaklogicname+'
282. WITH  RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+', FORMAT,  
283. INIT,  MEDIANAME =  '''+@bacmeidaname+''', NAME = '''+@bacname+''', 
284. SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
285. ' 
286. drop table
287. print @sql 
288. EXEC
289. IF @@ERROR<>0 GOTO
290. print N'完整备份成功带镜像！'
291. return
292. end
293. else begin
294. set
295. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
296. WITH  RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+', FORMAT,  
297. INIT,  MEDIANAME =  '''+@bacmeidaname+''', NAME = '''+@bacname+''', 
298. SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
299. ' 
300. drop table
301. print @sql 
302. EXEC
303. IF @@ERROR<>0 GOTO
304. print N'完整备份成功无镜像！'
305. return
306. end
307.      
308. end
309. --创建差异备份
310. Ibak: 
311. begin
312. --重建基础备份设备
313. SELECT 1 FROM sys.backup_devices WHERE name
314. begin
315. exec
316. exec
317. end else exec
318. --重建镜像备份设备
319.             IF @ismirror=1  
320. BEGIN
321. SELECT 1 FROM sys.backup_devices WHERE name
322. begin
323. exec
324. exec
325. end else exec
326. END
327. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
328. --判断历史D备份中的是否也包含了MIRROR备份，如果没有则修正@ismirror为0
329. SELECT 1 from #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it)) 
330. begin
331. SET
332. select @MIRbaklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
333. end
334. else set
335.  
336. IF @ismirror=1 BEGIN
337. set
338. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
339. MIRROR TO '+@MIRbaklogicname+'
340. WITH  DIFFERENTIAL ,RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+'
341. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
342. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
343. ' 
344.  
345. drop table
346. print @sql 
347. EXEC
348. IF @@ERROR<>0 GOTO
349. print N'差异备份成功带镜像！'
350. return
351. END
352. ELSE BEGIN
353. set
354. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
355. WITH  DIFFERENTIAL ,RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+'
356. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
357. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
358. ' 
359. drop table
360. print @sql 
361. EXEC
362. IF @@ERROR<>0 GOTO
363. print N'差异备份成功无镜像！'
364. return
365. END
366. end
367. --创建日志备份
368.  
369. Lbak: 
370. begin
371.  
372. --重建基础备份设备
373. SELECT 1 FROM sys.backup_devices WHERE name
374. begin
375. exec
376. exec
377. end else exec
378. --重建镜像备份设备
379.             IF @ismirror=1  
380. BEGIN
381. SELECT 1 FROM sys.backup_devices WHERE name
382. begin
383. exec
384. exec
385. end else exec
386. END
387.              
388. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
389. --判断历史D备份中的是否也包含了MIRROR备份，如果没有则修正@ismirror为0
390.  
391. SELECT 1 from #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it)) 
392. begin
393. SET
394. select @MIRbaklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
395. end
396. else set
397.  
398.  
399. IF @ismirror=1 
400. --参数需要镜像备份集
401. BEGIN
402. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
403. set
404. BACKUP LOG ['+@Bakdbname+'] TO  '+@baklogicname+'
405. MIRROR TO '+@MIRbaklogicname+'
406. WITH
407. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
408. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' 
409.  
410. drop table
411. print @sql 
412. EXEC
413. IF @@ERROR<>0 GOTO
414. print N'日志备份成功带镜像！'
415. return
416. END
417.  
418. --参数设置不需要镜像备份集
419. ELSE BEGIN
420.  
421. set
422. BACKUP LOG ['+@Bakdbname+'] TO  '+@baklogicname+'
423. WITH
424. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
425. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' 
426.  
427. drop table
428. print @sql 
429. EXEC
430. IF @@ERROR<>0 GOTO
431. print N'日志备份成功无镜像！'
432. return
433.  
434. END
435. end
436.  
437. ----备份日志尾部
438. --BACKUP LOG [ReportServer] TO SH_TC_BAK WITH  NO_TRUNCATE , 
439. --NOFORMAT, NOINIT,MEDIANAME=N'SH_TC_BAKMEDIA', NAME = N'SH_TC_TRUNLOG', 
440. --NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10
441.  
442. ERR: 
443. BEGIN
444.  
445. PRINT N'备份作业异常！请检查'
446. return
447. END
448.  
449.  
450. END
451.  
452.  
453. GO

1. USE [msdb] 
2. GO 
3.  
4. /****** Object:  StoredProcedure [dbo].[USP_Bak_Sche]    Script Date: 2012/12/26 11:24:33 ******/ 
5. /*作者：泥 巴                ******/ 
6. /*日期：20130322         ******/ 
7. SET ANSI_NULLS ON
8. GO 
9.  
10. SET QUOTED_IDENTIFIER ON
11. GO 
12.  
13. CREATE
14. ( 
15. --备份数据库名
16. ,@BAKFILE NVARCHAR(100)       --备份集文件路径
17. ,@ismirror int        =0       --是否建立镜像备份集，默认为0不建立
18. ,@BAKMIRFILE NVARCHAR(100) =NULL    --镜像备份集文件路径,默认是null
19. ,@devtype varchar(20) ='DISK'       --默认'disk'
20. ,@D_EXP_DATE INT      =28       --备份集过期时间单位 (天) 默认 28天 
21. ,@I_EXP_DATE INT      =7       --差异和完整备份过期时间单位(天) 默认  7天
22. ,@iit INT             =2        --备份集数目用于交替备份到同备份集 默认2
23. ,@DbakBtime int       =3--完整或者差异备份开始时间点 默认 3点
24. ,@DbakEtime int       =7--完整或者差异备份结束时间点 默认 7点
25.  
26.  
27. ) 
28. AS
29. BEGIN
30.  
31. if @ismirror=1 begin
32. if @BAKMIRFILE is null begin
33. print N'用户选择开启镜像备份集，但是镜像备份路径参数@bakmirfile不能为NULL！'
34. print N'注意：中途更改这个参数@ismirror会自动在每次做新的备份集的时候才会生效。'
35. return
36. end
37. end
38.  
39. --参数校正
40.  
41. IF @D_EXP_DATE<=0 
42. BEGIN
43.  
44. SET @D_EXP_DATE=(CASE WHEN @D_EXP_DATE<=0 THEN 1 ELSE @D_EXP_DATE END) 
45. PRINT N'备份集参数过期设置@d_exp_date 不能小于1,系统自动修正为1!'
46.  
47. END
48.  
49. IF @D_EXP_DATE<@I_EXP_DATE 
50.  
51. BEGIN
52.  
53. SET
54. PRINT N'备份块过期值应该小于或等于备份集过期值，参数已经修正为相等！'
55.  
56. END
57.  
58. --set @allbgname=''
59. declare @it int                 --循环种子
60. ,@baklogicname varchar(20)      --备份集逻辑名
61. ,@bakfilename varchar(100)      --备份集路径文件名
62. ,@MIRbaklogicname varchar(20)   --镜像备份集逻辑名
63. ,@MIRbakfilename varchar(100)   --镜像备份集路径文件名
64. ,@bacname Nvarchar(100)         --备份名称
65. ,@bacmeidaname Nvarchar(100)    --备份媒体名称
66. ,@sql   Nvarchar(2000) 
67. set @bacname=@Bakdbname+'_BAK'
68. set @bacmeidaname=@Bakdbname+'_MEDIA'
69. --建立所需要的备份集合
70. set
71. while @it<=@iit 
72. begin
73. set @baklogicname='My_bak'+ convert(varchar(10),@it) 
74. set @bakfilename=@BAKFILE+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
75. IF NOT EXISTS(SELECT * FROM sys.backup_devices WHERE name=@baklogicname) 
76. BEGIN
77. exec
78. end
79. IF @ISMIRROR=1 
80. BEGIN
81. set @MIRbaklogicname='MIRROR_'+@baklogicname 
82. set @MIRbakfilename=@BAKMIRFILE+'MIRROR_'+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
83. IF NOT EXISTS(SELECT 1 FROM sys.backup_devices WHERE name=@MIRbaklogicname) 
84. BEGIN
85. exec
86.  
87. END
88. END
89.  
90. set
91.  
92. end
93.  
94. --建立当前活动备份集的临时表
95.  
96.  
97.  
98. ;WITH
99. AS
100. ( 
101. select A.database_name,A.backup_set_id,position, type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
102. join (select database_name,MAX(backup_set_id) backup_set_id from
103. where database_name=@Bakdbname and type='D'
104. group by
105. on a.backup_set_id=b.backup_set_id and
106. --找出同组的备份组集合所有
107. union all
108. select A.database_name,A.backup_set_id,a.position, a.type,a.first_lsn,a.last_lsn,a.checkpoint_lsn,a.database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
109. join
110. select A.database_name,A.backup_set_id,position, A.type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
111. join (select database_name,MAX(backup_set_id) backup_set_id from
112. where database_name=@Bakdbname and type='D'
113. group by
114. on a.backup_set_id=b.backup_set_id and
115. ) b 
116. on a.database_backup_lsn=b.checkpoint_lsn AND
117. where a.type <>'D'
118.  
119. ) 
120. select  bf.*,cf.exp_day into
121. from
122. join
123. (select
124. bcs.backup_set_id, 
125. bcs.position, 
126. [BACK_TYPE]=bcs.type, 
127. [bacname]=bcs.name, 
128. bcs.database_name, 
129. [medianame]=bms.name, 
130. [BACdevicename]=bmf.logical_device_name, 
131. [dev_type]=bmf.device_type, 
132. [PHYS_DEVICE_NAME]=bmf.physical_device_name, 
133. bcs.expiration_date, 
134. --bcs.backup_set_uuid,
135. bcs.database_backup_lsn, 
136. bcs.first_lsn, 
137. bcs.last_lsn, 
138. bcs.checkpoint_lsn, 
139. --bcs.database_creation_date,
140. bcs.backup_start_date, 
141. bcs.backup_finish_date, 
142. [size/M]=bcs.compressed_backup_size/1024/1024 
143. from
144. join
145. join
146. on
147. on
148. on
149. where BF.database_name=@Bakdbname and BACdevicename in
150. select name from
151. where name like '%My_bak%'
152. ) 
153. order by
154.  
155. --初始备份集参数
156. set
157. --判断需要备份的数据库是否有D备份在所有备份集中
158. --
159. IF  exists( 
160. select * from #tempbak where BACK_TYPE='D'
161. ) 
162. --有D备份存在的情况
163. begin
164. --用历史备份集参数来重新赋值,以保证一个没有完全饱和的备份集参数都是一致的
165.  
166. select @it=SUBSTRING(BACdevicename,7,3) from #tempbak  where back_type='D' and BACdevicename like 'My_bak%'
167. select @baklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
168. select @MIRbaklogicname='MIRROR_'+@baklogicname 
169. select @bacmeidaname=MEDIANAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
170. SELEct @bacname=bacname FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
171. --set @baklogicname='My_bak'+ convert(varchar(10),@it)
172. SELECT @bakfilename=[PHYS_DEVICE_NAME]  from #tempbak  where back_type='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
173. select @MIRbaklogicname= [BACdevicename] from #tempbak where back_type='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
174. select @MIRbakfilename= [PHYS_DEVICE_NAME]  from #tempbak  where back_type='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
175.  
176. --判断历史D备中如果没有MIRROR备份集那么把标签自动设置为0
177.  
178. --如果
179.  
180.  
181. IF datepart(hh,GETDATE()) >=@DbakBtime and
182. --在3点到7点之间
183. begin
184. --判断最后的差异或者D备是否已经过期
185. select CONVERT(VARCHAR(8),MAX(expiration_date),112) exp_day FROM #TEMPBAK)<=CONVERT(VARCHAR(8),GETDATE(),112) 
186. --备份块已经过期
187. begin
188. --检查D+I备的个数是否已经饱和
189. select COUNT(1) from #tempbak  where back_type in ('D','I') and BACdevicename like 'My_bak%')>= ceiling(convert(numeric(19,2),@D_EXP_DATE)/convert(numeric(19,2),case  when @I_EXP_DATE<=0 then @D_EXP_DATE else @I_EXP_DATE end)) 
190. --备份分块已经饱和
191. begin
192.             IF @it<@iit 
193. --备份集还没有达到最大
194. begin
195. --设置备份集参数为下一个
196. select @it=SUBSTRING(BACdevicename,7,3) from #tempbak  where back_type='D' and BACdevicename like 'My_bak%'
197. set
198. GOTO
199. end
200.              
201. else
202. --备份集已经达到最大
203. begin
204. --设置备份集参数为初始第一个
205. set
206. GOTO
207. end
208.              
209. end
210. --备份集没有饱和
211. else
212. begin
213. goto
214. end
215. end
216.      
217. ELSE
218. --备份分块没有过期
219. begin
220. goto
221. end
222.  
223. end
224. --当前时间不在完整和差异备份时间段之间
225. else begin
226. --做L备份
227. goto
228. end
229. end
230. --在没有D备份存在的情况
231. else begin
232.      
233. set
234. --当前时间在完整和差异备份时间段之间
235. IF datepart(hh,GETDATE()) >=@DbakBtime and
236. begin
237. GOTO
238. end
239. --当前时间不在完整和差异备份时间段之间
240. else begin
241.  
242. '当前的备份集下还没有过任何D备份,且不在做D备份时间段，请选择正确时间段运行以便首次建立D备份！请确认设置的时间段合法!'
243. return
244.      
245. end
246.  
247. end
248.  
249.  
250.  
251. --第创建使用fomat创建媒体集和备份集以及镜像备份集
252.  
253. Dbak: 
254. begin
255. set @baklogicname='My_bak'+ convert(varchar(10),@it) 
256. SET @MIRbaklogicname='MIRROR_'+@baklogicname 
257. set @bacname=@Bakdbname+'_BAK'
258. set @bacmeidaname=@Bakdbname+'_MEDIA'
259. set @bakfilename=@BAKFILE+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
260. set @MIRbakfilename=@BAKMIRFILE+'MIRROR_'+@bakdbname+'_BAK'+ convert(varchar(10),@it)+'.BAK'
261. --重建基础备份设备
262. SELECT 1 FROM sys.backup_devices WHERE name
263. begin
264. exec
265. exec
266. end else exec
267. --重建镜像备份设备
268.             IF @ismirror=1  
269. BEGIN
270. SELECT 1 FROM sys.backup_devices WHERE name
271. begin
272. exec
273. exec
274. end else exec
275. END
276.              
277. IF @ismirror=1  
278. begin
279. set
280. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
281. MIRROR TO '+@MIRbaklogicname+'
282. WITH  RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+', FORMAT,  
283. INIT,  MEDIANAME =  '''+@bacmeidaname+''', NAME = '''+@bacname+''', 
284. SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
285. ' 
286. drop table
287. print @sql 
288. EXEC
289. IF @@ERROR<>0 GOTO
290. print N'完整备份成功带镜像！'
291. return
292. end
293. else begin
294. set
295. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
296. WITH  RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+', FORMAT,  
297. INIT,  MEDIANAME =  '''+@bacmeidaname+''', NAME = '''+@bacname+''', 
298. SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
299. ' 
300. drop table
301. print @sql 
302. EXEC
303. IF @@ERROR<>0 GOTO
304. print N'完整备份成功无镜像！'
305. return
306. end
307.      
308. end
309. --创建差异备份
310. Ibak: 
311. begin
312. --重建基础备份设备
313. SELECT 1 FROM sys.backup_devices WHERE name
314. begin
315. exec
316. exec
317. end else exec
318. --重建镜像备份设备
319.             IF @ismirror=1  
320. BEGIN
321. SELECT 1 FROM sys.backup_devices WHERE name
322. begin
323. exec
324. exec
325. end else exec
326. END
327. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
328. --判断历史D备份中的是否也包含了MIRROR备份，如果没有则修正@ismirror为0
329. SELECT 1 from #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it)) 
330. begin
331. SET
332. select @MIRbaklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
333. end
334. else set
335.  
336. IF @ismirror=1 BEGIN
337. set
338. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
339. MIRROR TO '+@MIRbaklogicname+'
340. WITH  DIFFERENTIAL ,RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+'
341. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
342. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
343. ' 
344.  
345. drop table
346. print @sql 
347. EXEC
348. IF @@ERROR<>0 GOTO
349. print N'差异备份成功带镜像！'
350. return
351. END
352. ELSE BEGIN
353. set
354. BACKUP DATABASE ['+@Bakdbname+'] TO '+@baklogicname+'
355. WITH  DIFFERENTIAL ,RETAINDAYS = '+convert(varchar(10),@I_EXP_DATE)+'
356. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
357. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 
358. ' 
359. drop table
360. print @sql 
361. EXEC
362. IF @@ERROR<>0 GOTO
363. print N'差异备份成功无镜像！'
364. return
365. END
366. end
367. --创建日志备份
368.  
369. Lbak: 
370. begin
371.  
372. --重建基础备份设备
373. SELECT 1 FROM sys.backup_devices WHERE name
374. begin
375. exec
376. exec
377. end else exec
378. --重建镜像备份设备
379.             IF @ismirror=1  
380. BEGIN
381. SELECT 1 FROM sys.backup_devices WHERE name
382. begin
383. exec
384. exec
385. end else exec
386. END
387.              
388. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
389. --判断历史D备份中的是否也包含了MIRROR备份，如果没有则修正@ismirror为0
390.  
391. SELECT 1 from #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it)) 
392. begin
393. SET
394. select @MIRbaklogicname=BACDEVICENAME FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'MIRROR_My_bak'+convert(varchar(10),@it) 
395. end
396. else set
397.  
398.  
399. IF @ismirror=1 
400. --参数需要镜像备份集
401. BEGIN
402. select @I_EXP_DATE=exp_day FROM #TEMPBAK WHERE BACK_TYPE='D' and BACdevicename = 'My_bak'+convert(varchar(10),@it) 
403. set
404. BACKUP LOG ['+@Bakdbname+'] TO  '+@baklogicname+'
405. MIRROR TO '+@MIRbaklogicname+'
406. WITH
407. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
408. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' 
409.  
410. drop table
411. print @sql 
412. EXEC
413. IF @@ERROR<>0 GOTO
414. print N'日志备份成功带镜像！'
415. return
416. END
417.  
418. --参数设置不需要镜像备份集
419. ELSE BEGIN
420.  
421. set
422. BACKUP LOG ['+@Bakdbname+'] TO  '+@baklogicname+'
423. WITH
424. NOINIT,  MEDIANAME = '''+@bacmeidaname+''',  NAME = '''+@bacname+''',  
425. NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' 
426.  
427. drop table
428. print @sql 
429. EXEC
430. IF @@ERROR<>0 GOTO
431. print N'日志备份成功无镜像！'
432. return
433.  
434. END
435. end
436.  
437. ----备份日志尾部
438. --BACKUP LOG [ReportServer] TO SH_TC_BAK WITH  NO_TRUNCATE , 
439. --NOFORMAT, NOINIT,MEDIANAME=N'SH_TC_BAKMEDIA', NAME = N'SH_TC_TRUNLOG', 
440. --NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10
441.  
442. ERR: 
443. BEGIN
444.  
445. PRINT N'备份作业异常！请检查'
446. return
447. END
448.  
449.  
450. END
451.  
452.  
453. GO

调用方式：直接调用存储过程并按说明给出参数即可。

监控实现代码：

1. ;WITH
2. AS
3. ( 
4. select A.database_name,A.backup_set_id,position, type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
5. join (select database_name,MAX(backup_set_id) backup_set_id from
6. where  type='D'
7. group by
8. on a.backup_set_id=b.backup_set_id and
9. --找出同组的备份组集合所有
10. union all
11. select A.database_name,A.backup_set_id,a.position, a.type,a.first_lsn,a.last_lsn,a.checkpoint_lsn,a.database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
12. join
13. select A.database_name,A.backup_set_id,position, A.type,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,isnull(datediff(d,backup_start_date,expiration_date),0) exp_day from
14. join (select database_name,MAX(backup_set_id) backup_set_id from
15. where type='D'
16. group by
17. on a.backup_set_id=b.backup_set_id and
18. ) b 
19. on a.database_backup_lsn=b.checkpoint_lsn AND
20. where a.type <>'D'
21.  
22. ) 
23. select  bf.*,cf.exp_day into
24. from
25. join
26. (select
27. bcs.backup_set_id, 
28. bcs.position, 
29. [BACK_TYPE]=bcs.type, 
30. [bacname]=bcs.name, 
31. bcs.database_name, 
32. [medianame]=bms.name, 
33. [BACdevicename]=bmf.logical_device_name, 
34. [dev_type]=bmf.device_type, 
35. [PHYS_DEVICE_NAME]=bmf.physical_device_name, 
36. bcs.expiration_date, 
37. --bcs.backup_set_uuid,
38. bcs.database_backup_lsn, 
39. bcs.first_lsn, 
40. bcs.last_lsn, 
41. bcs.checkpoint_lsn, 
42. --bcs.database_creation_date,
43. bcs.backup_start_date, 
44. bcs.backup_finish_date, 
45. [size/M]=bcs.compressed_backup_size/1024/1024 
46. from
47. join
48. join
49. on
50. on
51. on
52. where BACdevicename in
53. select name from
54. where name like '%My_bak%'
55. ) 
56. order by
57.  
58.  
59. SELECT DATABASE_NAME,[BACdevicename],back_type,count(1) times,MAX(backup_finish_date) last_time,SUM([size/M]) 'size/M' FROM
60. group by
61. order by
62.  
63. drop table

写出来分享给大家，希望大家提出好的建议和意见，也希望对大家有所帮助。