SQLServer备份数据库清除过期备份的存储过程
jcstone 2018-8-13
采⽤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 p_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
存储过程如下：
1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5-- =============================================
6-- Author: <jcstone,,Name>
7-- Create date: <Create Date,2018-8-13,>
8-- Description: <Description，数据库备份和清除⼯具,@BackupDir备份⽬录（如'E:\DBBackup\'）,@DatabaseName备份数据库,@InvalidMonth清除备份所距离今天的⽉份数> 9-- =============================================
10Create PROCEDURE[dbo].[P_ZTool_BackupAndClearDatabase]
11@BackupDir nvarchar(300) ,
12@DatabaseName nvarchar(100) ,
13@InvalidMonth int
14AS
15BEGIN
16
17--备份数据库
18declare@sql varchar(200);
19
20declare@File NVARCHAR(100);
21DECLARE@dateTime NVARCHAR(20);
22set@dateTime=REPLACE(CONVERT(varchar(10), GETDATE(), 120),'-','_')+'_'++REPLACE(CONVERT(varchar(10), GETDATE(), 8),':','_'); 23set@File=@BackupDir+@DatabaseName+'_'+@dateTime+'.Bak';
24
25Backup database@DatabaseName to Disk=@File With NOINIT ,NOUNLOAD,NOSKIP,STATS=10,NOFORMAT
26
sqlserver备份表语句27
28--启动xp_cmdshell
29set@sql='
30 use msdb
31 exec sp_configure ''show advanced options'', 1;
32 reconfigure;
33 exec sp_configure ''xp_cmdshell'',1;
34 reconfigure; '
35exec(@sql)
36
37
38--将⽂件夹下⽂件和⼦⽂件夹存⼊表变量@Tab
39DECLARE@Tab table (subdirectory nvarchar(Max),depth smallint,[file]bit)
40insert@Tab p_dirtree @BackupDir,1,1
41
42--定义⽂件名中时间临时表（便于重置清除临时表数据），在循环中重复赋值后再清除
43declare@MyDatetime datetime
44declare@Year int ,@Month int , @Day int
45
46
47--循环遍历表变量中的⽂件，⽐对⽇期是否超过2个⽉，超过则删除
48declare@subdirectory nvarchar(200)
49declare@delFile nvarchar(200)
50declare my_cursor cursor for (select subdirectory from@Tab)
51--打开游标--
52open my_cursor
53--开始循环游标变量--
54fetch next from my_cursor into@subdirectory
55while@@FETCH_STATUS=0
56begin
57set@Year= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',2)
58set@Month= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',3)
59set@Day= dbo.Get_StrArrayStrOfIndex(@subdirectory,'_',4)
60set@MyDatetime=convert(varchar(4),@Year) +'-'+convert(varchar(2),@Month) +'-'+convert(varchar(2),@Day)
61
62set@File=@BackupDir+@subdirectory
63
64if(DATEDIFF ( MONTH,@MyDatetime,GETDATE () )>@InvalidMonth)
65Begin
66--select @MyDatetime,'删除⽂件：'+@File
67set@sql='exec master.dbo.xp_cmdshell ''del '+@File+''''
68exec( @sql)
69End
70else
71--Begin
72-- --select @MyDatetime ,'不删除⽂件：'+@subdirectory
73--End
74fetch next from my_cursor into@subdirectory
75end
76close my_cursor
77deallocate my_cursor
78
79END
其中表值函数Get_StrArrayStrOfIndex的代码如下：
1-- =============================================
2-- Author: <Author,jcstone,Name>
3-- Create date: <Create Date,2013-1-28,>
4-- Description: <Description，字符串分解为int表值,>
5-- =============================================
6
7Create function[dbo].[Get_StrArrayStrOfIndex]
8 (
9@str varchar(5000), --要分割的字符串
10@split varchar(10), --分隔符号
11@index int--取第⼏个元素
12 )
13returns varchar(200)
14as
15begin
16declare@location int
17declare@start int
18declare@next int
19declare@seed int
20
21set@str=ltrim(rtrim(@str))
22set@start=1
23set@next=1
24set@seed=len(@split)
25
26set@location=charindex(@split,@str)
27while@location<>0and@index>@next
28begin
29set@start=@location+@seed
30set@location=charindex(@split,@str,@start) 31set@next=@next+1
32end
33if@location=0select@location=len(@str)+1 34return substring(@str,@start,@location-@start) 35end
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论