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

    ——jcstone（备忘）

采用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、判断是否过期需获取文件名中的时间，采用表值函数（），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: <石教承,,Name>

-- Create date: <Create Date,2018-8-13,>

-- Description: <Description,数据库备份和清除工具,@BackupDir备份目录（如'E:\DBBackup\'）,@DatabaseName备份数据库,@InvalidMonth清除备份所距离今天的月份数>

-- =============================================

Alter 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