SQL Server备份数据库清除过期备份的存储过程

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

 

存储过程如下：

  　　

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <jcstone,,Name>
-- Create date: <Create Date,2018-8-13,>
-- Description:    <Description,数据库备份和清除工具,@BackupDir备份目录（如'E:\DBBackup\'）,@DatabaseName备份数据库,@InvalidMonth清除备份所距离今天的月份数>
-- =============================================
Create PROCEDURE [dbo].[P_ZTool_BackupAndClearDatabase] 
     @BackupDir nvarchar(300) ,
     @DatabaseName nvarchar(100) ,
     @InvalidMonth int
AS
BEGIN 
 
    --备份数据库
    declare @sql varchar(200);
    
    declare @File  NVARCHAR(100);
    DECLARE @dateTime NVARCHAR(20);
    set @dateTime = REPLACE(CONVERT(varchar(10), GETDATE(), 120),'-','_')+'_'+  +REPLACE(CONVERT(varchar(10), GETDATE(), 8),':','_');
    set @File=@BackupDir+@DatabaseName+'_'+@dateTime+'.Bak';
     
    Backup database @DatabaseName to Disk=@File  With NOINIT ,NOUNLOAD,NOSKIP,STATS=10,NOFORMAT
     
    
    --启动xp_cmdshell 
    set @sql='
    use msdb 
    exec sp_configure ''show advanced options'', 1;
    reconfigure;
    exec sp_configure ''xp_cmdshell'',1;
    reconfigure; '
    exec(@sql)
  
  
       --将文件夹下文件和子文件夹存入表变量@Tab
    DECLARE @Tab table (subdirectory nvarchar(Max),depth smallint,[file] bit)  
    insert @Tab exec master..xp_dirtree @BackupDir,1,1
     
    --定义文件名中时间临时表（便于重置清除临时表数据），在循环中重复赋值后再清除
    declare @MyDatetime datetime
    declare @Year int ,@Month int , @Day int 
     
    
    --循环遍历表变量中的文件，比对日期是否超过2个月，超过则删除
    declare @subdirectory nvarchar(200)
    declare @delFile nvarchar(200)
    declare my_cursor cursor for (select subdirectory from @Tab)
    --打开游标--
    open my_cursor
    --开始循环游标变量--
    fetch next from my_cursor into @subdirectory
    while @@FETCH_STATUS = 0     
        begin  
             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)
            
            set @File=@BackupDir+@subdirectory
            
            if(DATEDIFF ( MONTH,@MyDatetime,GETDATE () )>@InvalidMonth)
            Begin 
                --select @MyDatetime,'删除文件：'+@File
                set @sql='exec master.dbo.xp_cmdshell ''del '+@File+''''
                exec( @sql)
            End  
            else
            --Begin 
            --    --select @MyDatetime ,'不删除文件：'+@subdirectory 
            --End  
            fetch next from my_cursor into @subdirectory
        end    
    close my_cursor   
    deallocate my_cursor    

END

其中表值函数Get_StrArrayStrOfIndex的代码如下：

-- =============================================
-- Author:        <Author,jcstone,Name>
-- Create date: <Create Date,2013-1-28,>
-- Description:    <Description,字符串分解为int表值,>
-- =============================================
 
Create function [dbo].[Get_StrArrayStrOfIndex]
(
  @str varchar(5000),  --要分割的字符串
  @split varchar(10),  --分隔符号
  @index int --取第几个元素
)
returns varchar(200)
as
begin
  declare @location int
  declare @start int
  declare @next int
  declare @seed int

  set @str=ltrim(rtrim(@str))
  set @start=1
  set @next=1
  set @seed=len(@split)
  
  set @location=charindex(@split,@str)
  while @location<>0 and @index>@next
  begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
  end
  if @location =0 select @location =len(@str)+1 
  return substring(@str,@start,@location-@start)
end