SQL Server备份数据库清除过期备份的存储过程
采用SQL Server自带的数据库维护向导，需手动创建数据库备份和清理备份工作任务，不方便对数据库名称、保存路径及过期时间进行动态设置，因此笔者创建存储过程，可以设置数据库名、存档路径以及过期月份（如按天、周者自行修改），然后在新建工作中的步骤执行该存储过程即可。在新建工作可自行设定执行时间和周期，实现数据库按周期备份和清理。
备份数据名称格式：[数据库名称]_年_月_日_时_分_秒.bak
其中年为四位数字，其它月日时分秒均为两位数字
将计划工作创建后可生成创建代码，另建存储过程，将创建工作代码纳入存储过程中，方便后台系统调用灵活部署和调用。
主要步骤及难点：
1、数据库备份名称日期时间格式的生成SQL语句：
set @dateTime = REPLACE(CONVERT(varchar(10), GETDATE(), 120),'-','_')+'_'+ +REPLACE(CONVERT(varchar(10), GETDATE(), 8),':','_');
set @File=@BackupDir+@DatabaseName+'_'+@dateTime+'.Bak';
2、备份数据库SQL语句：
Backup database @DatabaseName to Disk=@File With NOINIT ,NOUNLOAD,NOSKIP,STATS=10,NOFORMAT
3、启动xp_cmdshell 存储过程中不允许有Use语句，因此才有SQL语句字符串执行：
set @sql='
use msdb
exec sp_configure ''show advanced options'', 1;
reconfigure;
exec sp_configure ''xp_cmdshell'',1;
reconfigure; '
exec(@sql)
4、定义表变量并将备份目录中的文件保存到表变量中，SQL语句：
DECLARE @Tab table (subdirectory nvarchar(Max),depth smallint,[file] bit)
insert @Tab exec master..xp_dirtree @BackupDir,1,1
5、游标遍历表变量中文件，根据文件名所含的时间信息判断是否过期，游标SQL语句：
declare my_cursor cursor for (select subdirectory from @Tab)
--打开游标--
open my_cursor
--开始循环游标变量--
fetch next from my_cursor into @subdirectory
while @@FETCH_STATUS = 0
begin
--根据文件名所含的时间信息判断是否过期，过期则采用xp_cmdshell删除
......
fetch next from my_cursor into @subdirectory
end
close my_cursor
deallocate my_cursor
6、判断是否过期需获取文件名中的时间，采用表值函数Get_StrArrayStrOfIndex（见后），SQL语句：
set @Year= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',2)
set @Month= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',3)
set @Day= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',4)
set @MyDatetime=convert(varchar(4),@Year) +'-'+convert(varchar(2),@Month) +'-'+convert(varchar(2),@Day)
7、判断是否过期，过期采用xp_cmdshell删除，SQL语句：
if(DATEDIFF ( MONTH,@MyDatetime,GETDATE () )>@InvalidMonth)
Begin
set @sql='exec master.dbo.xp_cmdshell ''del '+@File+''''
exec( @sql)
End
存储过程如下：
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author: <jcstone,,Name>
7 -- Create date: <Create Date,2018-8-13,>
8 -- Description: <Description,数据库备份和清除工具,@BackupDir备份目录（如'E:\DBBackup\'）,@DatabaseName备份数据库,@InvalidMonth清除备份所距离今天的月份数>
9 -- =============================================
10 Create PROCEDURE [dbo].[P_ZTool_BackupAndClearDatabase]
11 @BackupDir nvarchar(300) ,
12 @DatabaseName nvarchar(100) ,
13 @InvalidMonth int
14 AS
15 BEGIN
16
17 --备份数据库
18 declare @sql varchar(200);
19
20 declare @File NVARCHAR(100);
21 DECLARE @dateTime NVARCHAR(20);
22 set @dateTime = REPLACE(CONVERT(varchar(10), GETDATE(), 120),'-','_')+'_'+ +REPLACE(CONVERT(varchar(10), GETDATE(), 8),':','_');
23 set @File=@BackupDir+@DatabaseName+'_'+@dateTime+'.Bak';
24
25 Backup database @DatabaseName to Disk=@File With NOINIT ,NOUNLOAD,NOSKIP,STATS=10,NOFORMAT
26
27
28 --启动xp_cmdshell
29 set @sql='
30 use msdb
31 exec sp_configure ''show advanced options'', 1;
32 reconfigure;
33 exec sp_configure ''xp_cmdshell'',1;
34 reconfigure; '
35 exec(@sql)
36
37
38 --将文件夹下文件和子文件夹存入表变量@Tab
39 DECLARE @Tab table (subdirectory nvarchar(Max),depth smallint,[file] bit)
40 insert @Tab exec master..xp_dirtree @BackupDir,1,1
41
42 --定义文件名中时间临时表（便于重置清除临时表数据），在循环中重复赋值后再清除
43 declare @MyDatetime datetime
44 declare @Year int ,@Month int , @Day int
45
46
47 --循环遍历表变量中的文件，比对日期是否超过2个月，超过则删除
48 declare @subdirectory nvarchar(200)
49 declare @delFile nvarchar(200)
50 declare my_cursor cursor for (select subdirectory from @Tab)
51 --打开游标--
52 open my_cursor
53 --开始循环游标变量--
54 fetch next from my_cursor into @subdirectory
55 while @@FETCH_STATUS = 0
56 begin
57 set @Year= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',2)
58 set @Month= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',3)
59 set @Day= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',4)
60 set @MyDatetime=convert(varchar(4),@Year) +'-'+convert(varchar(2),@Month) +'-'+convert(varchar(2),@Day)
61
62 set @File=@BackupDir+@subdirectory
63
64 if(DATEDIFF ( MONTH,@MyDatetime,GETDATE () )>@InvalidMonth)
65 Begin
66 --select @MyDatetime,'删除文件：'+@File
67 set @sql='exec master.dbo.xp_cmdshell ''del '+@File+''''
68 exec( @sql)
69 End
70 else
71 --Begin
72 -- --select @MyDatetime ,'不删除文件：'+@subdirectory
73 --End
74 fetch next from my_cursor into @subdirectory
75 end
76 close my_cursor
77 deallocate my_cursor
78
79 END
其中表值函数Get_StrArrayStrOfIndex的代码如下：
1 -- =============================================
2 -- Author: <Author,jcstone,Name>
3 -- Create date: <Create Date,2013-1-28,>
4 -- Description: <Description,字符串分解为int表值,>
5 -- =============================================
6
7 Create function [dbo].[Get_StrArrayStrOfIndex]
8 (
9 @str varchar(5000), --要分割的字符串
10 @split varchar(10), --分隔符号
11 @index int --取第几个元素
12 )
13 returns varchar(200)
14 as
15 begin
16 declare @location int
17 declare @start int
18 declare @next int
19 declare @seed int
20
21 set @str=ltrim(rtrim(@str))
22 set @start=1
23 set @next=1
24 set @seed=len(@split)
25
26 set @location=charindex(@split,@str)
27 while @location<>0 and @index>@next
28 begin
29 set @start=@location+@seed
30 set @location=charindex(@split,@str,@start)
31 set @next=@next+1
32 end
33 if @location =0 select @location =len(@str)+1
34 return substring(@str,@start,@location-@start)
35 end
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
