mssql数据库维护计划--删除早于此时间的文件如何实现

[fenghlc]

mssql新建了数据库维护计划：备份所有的数据库并删除早于5天以前的数据库备份文件

备份文件的格式如下：dbname_db_201011120200.BAK

declare @str varchar(100),@dir varchar(100),@dataname varchar(100),@fileName varchar(30)
declare @datanametable table(dataname varchar(100))


insert @datanametable (dataname)
select name
from master..sysdatabases
where name not in('master','tempdb','model','msdb')


while (select count(*) from @datanametable) > 0
begin
select top 1 @dataname = dataname
from @datanametable
order by dataname


set @dir='del D:\abcde\'
set @filename=left(replace(replace(replace(convert(varchar,getdate()-5,20),'-',''),' ',''),':',''),8)
set @str=@dir+@dataname+'_db_'+@fileName+'*.bak'

exec xp_cmdshell @str
delete @datanametable where dataname =@dataname
end

以上语句只能删除前面第5天的数据库备份，更早的备份文件不能删除

本人第一次写sql代码，各位帮个忙，帮忙改下代码，使之能删除5天以前的备份文件，谢谢

[heymal]

不要用作业删除备份文件，如果要删除备份文件使用这个：

forfiles /p "备份文件的路径" /s /D -5 /m *.bak /c "cmd /c del @path"

把这个语句写成bat文件，在操作系统上做一个这个计划任务，每天定时执行，比你用SQLSERVER的计划要好

[--小F--]

-- 如何删除 SQL2005 过期的数据库备份文件呢？ 

在 SQL2005 数据库中，不可以自动删除过期的备份文件，所以借用第三方插件完成此功能。 
-- 方式一：通过 Forfiles 删除指定目录下过期的备份文件 
目的：删除目录 i: \sqldataup 中天前的 . bak 文件： 

步骤： 

1 、定义 FORFILES 批处理脚本如下： 

C: \> FORFILES / P i: \sqldataup / M *. bak / C "cmd /C del /Q @path" / d - 5 

如果执行成功则返回当前盘符 C: \> 。 

-- 如果没有需要删除的文件则返回信息错误 : 用指定的搜索标准没有找到文件。比如： 

--C:\>FORFILES /P i:\sqldataup /M *.bak /C "cmd /C del /Q @path" /d -5 

-- 错误 : 用指定的搜索标准没有找到文件。 


2 、通过计划任务调用批处理脚本如图所示,图太长请看这里 

3 、当然也可以用 SQLAgent 调用 CMDEXEC 完成批处理作业。 


方式二：如果在 SQL2008 中因为默认安装 Powershell 1.0 程序，故可以用 Powershell 编写脚本来完成定时删除过期文件。 

太多内容，请看文章http://blog.csdn.net/claro/archive/2009/08/18/4458417.aspx。

[billpu]

Forfiles

从文件夹或树中选择要进行批处理的文件。
语法

forfiles [/p Path] [/m SearchMask] [/s] [/c Command] [/d[{+ | -}] [{MM/DD/YYYY | DD}]]
参数

/p Path

指定 Path，表明要从哪里开始搜索。默认的文件夹是当前工作目录，该目录通过键入句号 (.) 指定。

/m SearchMask

按照 SearchMask 搜索文件。默认的 SearchMask 是 *.*。

/s

指示 forfiles 在子目录中搜索。

/c Command

在每个文件上运行指定的 Command。带有空格的命令字符串必须用引号括起来。默认的 Command 是 "cmd /c echo @file"。

/d[{+ | -}] [{MM/DD/YYYY | DD}]

选择日期大于或等于 (+)（或者小于或等于 (-)）指定日期的文件，其中 MM/DD/YYYY 是指定的日期，DD 是当前日期减去 DD 天。如果未指定 + 或 -，则使用 +。DD 的有效范围是 0 - 32768。

/?

在命令提示符下显示帮助。
注释

•

Forfiles 最常用于批处理文件中。
•

Forfiles /s 与 dir /s 类似。
•

下表列出了可在 /cCommand 命令字符串中使用的变量。

变量 描述

@file


文件名

@fname


无扩展名的文件名

@ext


文件扩展名

@path


文件的完整路径

@relpath


文件的相对路径

@isdir


如果文件类型是目录，则计算值为 TRUE，否则值为 FALSE

@fsize


用字节表示的文件大小

@fdate


文件中上次修改的日期戳

@ftime


文件中上次修改的时间戳
•

使用 forfiles，可以在多个文件上运行命令或将参数传递给多个文件。例如，可以在树中带有 *.txt 扩展名的所有文件上运行 TYPE 命令。或者，可以用文件名“Myinput.txt”作为第一个参数，在 C:\ 驱动器上执行每个批处理文件 (*.bat)。
•

通过使用 forfiles，可以执行下列任何操作：
•

使用 /d 按照绝对或相对日期选择文件。
•

使用诸如 @fsize（文件大小）和 @fdate（文件日期）这样的变量构建文件的存档树。
•

使用 @isdir 变量区分文件和目录。
•

通过在命令行中包含特殊字符和使用十六进制代码 0xHH 环绕字符来格式化输出内容。
•

Forfiles 通过在旨在仅处理单个文件的工具上执行“循环子目录”标记来发挥作用。
示例

要列出驱动器 C: 上的所有批处理文件，请键入：

forfiles /p c:\ /s /m*.bat /c"cmd /c echo @file is a batch file"

要列出驱动器 C: 上的所有目录，请键入：

forfiles /p c:\ /s /m*.* /c"cmd /c if @isdir==true echo @file is a directory"

要列出驱动器 C: 上存在时间多于 100 天的所有文件，请键入：

forfiles /p c:\ /s /m*.* /dt-100 /c"cmd /c echo @file :date >= 100 days"

要列出驱动器 C: 上 1993 年 1 月 1 日以前创建的所有文件，而且对于日期早于 1993 年 1 月 1 日的文件显示“file is quite old!”，请键入：

forfiles /p c:\ /s /m*.* /dt-01011993 /c"cmd /c echo @file is quite old!"

要按列格式列出驱动器 C: 上所有文件的扩展名，请键入：

forfiles /p c:\ /s /m*.* /c "cmd /c echo extension of @file is 0x09@ext0x09" With:

要列出驱动器 C: 上的所有批处理文件，请键入：

forfiles /p c:\ /s /m *.bat /c "cmd /c echo @file is a batch file"

要列出驱动器 C: 上的所有目录，请键入：

forfiles /p c:\ /s /m *.* /c "cmd /c if @isdir==true echo @file is a directory"

要列出驱动器 C: 上存在时间多于 100 天的所有文件，请键入：

forfiles /p c:\ /s /m *.* /d t-100 /c "cmd /c echo @file :date >= 100 days"

要列出驱动器 C: 上 1993 年 1 月 1 日以前创建的所有文件，而且对于日期早于 1993 年 1 月 1 日的文件显示“file is quite old!”，请键入：

forfiles /p c:\ /s /m *.* /d t-01011993 /c "cmd /c echo @file is quite old!"

要按列格式列出驱动器 C: 上所有文件的扩展名，请键入：

forfiles /p c:\ /s /m*.* /c "cmd /c echo extension of @file is 0x09@ext0x09"

[claro]

http://blog.csdn.net/claro/archive/2009/08/18/4458417.aspx

[fenghlc]

谢谢各位的回复

小弟初学T-SQL,提这个问题的目的不只是为了达到删除文件的目的，最主要的是想学习下T-SQL,还是麻烦各位指教下通过T-SQL语句如何实现，谢谢

[--小F--]

create proc sp_backupdatabase
 @bak_path nvarchar(4000)=''       --备份路径;
,@baktype int = null               --备份类型为全备,1为差异备,2为日志备份
,@type int = null                  --设置需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库;
,@dbnames nvarchar(4000)=''        --需要备份或排除的数据库，用,隔开，当@type=3或4时生效
,@overdueDay int = null            --设置过期天数，默认天;
,@compression int =0               --是否采用sql2008的压缩备份,0为否,1为采用压缩
as
--sql server 2005/2008备份/删除过期备份T-sql 版本v1.0
/*
author:perfectaction
date  :2009.04
desc  :适用于sql2005/2008备份，自动生成库文件夹，可以自定义备份类型和备份库名等
      可以自定义备份过期的天数
              删除过期备份功能不会删除最后一次备份，哪怕已经过期
              如果某库不再备份，那么也不会再删除之前过期的备份 
       如有错误请指正，谢谢.
*/

set nocount on
--开启xp_cmdshell支持
exec sp_configure 'show advanced options', 1
reconfigure with override
exec sp_configure 'xp_cmdshell', 1 
reconfigure with override
exec sp_configure 'show advanced options', 0
reconfigure with override
print char(13)+'------------------------'

--判断是否填写路径
if isnull(@bak_path,'')=''
    begin
        print('error:请指定备份路径')
        return 
    end

--判断是否指定需要备份的库
if isnull(ltrim(@baktype),'')=''
    begin
        print('error:请指定备份类型aa:0为全备,1为差异备,2为日志备份')
        return 
    end
else
    begin
        if @baktype not between 0 and 2
        begin
            print('error:指定备份类型只能为,1,2:  0为全备,1为差异备,2为日志备份')
            return 
        end
    end
--判断是否指定需要备份的库
if isnull(ltrim(@type),'')=''
    begin
        print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')
        return 
    end
else
    begin
        if @type not between 0 and 4
        begin
            print('error:请指定需要备份的库,0为全部库,1为系统库,2为全部用户库,3为指定库,4为排除指定库')
            return 
        end
    end

--判断指定库或排除库时，是否填写库名
if @type>2
    if @dbnames=''
    begin
        print('error:备份类型为'+ltrim(@type)+'时，需要指定@dbnames参数')
        return 
    end

--判断指定指定过期时间
if isnull(ltrim(@overdueDay),'')=''
begin
    print('error:必须指定备份过期时间,单位为天,0为永不过期')
    return 
end

--判断是否支持压缩
if @compression=1 
    if charindex('2008',@@version)=0 or charindex('Enterprise',@@version)=0
    begin
        print('error:压缩备份只支持sql2008企业版')
        return 
    end

--判断是否存在该磁盘
declare @drives table(drive varchar(1),[size] varchar(20))
insert into @drives exec('master.dbo.xp_fixeddrives')
if not exists(select 1 from @drives where drive=left(@bak_path,1))
    begin
        print('error:不存在该磁盘:'+left(@bak_path,1))
        return 
    end

--格式化参数
select @bak_path=rtrim(ltrim(@bak_path)),@dbnames=rtrim(ltrim(@dbnames))
if right(isnull(@bak_path,''),1)!='\' set @bak_path=@bak_path+'\'
if isnull(@dbnames,'')!='' set @dbnames = ','+@dbnames+','
set @dbnames=replace(@dbnames,' ','')

--定义变量
declare @bak_sql nvarchar(max),@del_sql nvarchar(max),@i int,@maxid int
declare @dirtree_1 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int)
declare @dirtree_2 table (id int identity(1,1) primary key,subdirectory nvarchar(600),depth int,files int,
dbname varchar(300),baktime datetime,isLastbak int)
declare @createfolder nvarchar(max),@delbackupfile nvarchar(max),@delbak nvarchar(max)

--获取需要备份的库名--------------------start
declare @t table(id int identity(1,1) primary key,name nvarchar(max))
declare @sql nvarchar(max)
set @sql = 'select name from sys.databases where state=0 and name!=''tempdb''  '
    + case when @baktype=2 then ' and recovery_model!=3 ' else '' end
    + case @type when 0 then 'and 1=1'
        when 1 then 'and database_id<=4'
        when 2 then 'and database_id>4'
        when 3 then 'and charindex('',''+Name+'','','''+@dbnames+''')>0'
        when 4 then 'and charindex('',''+Name+'','','''+@dbnames+''')=0 and database_id>4'
        else '1>2' end
insert into @t exec(@sql)
--获取需要备份的库名---------------------end

--获取需要创建的文件夹------------------start
insert into @dirtree_1 exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')
select @createfolder=isnull(@createfolder,'')+'exec master.dbo.xp_cmdshell ''md '+@bak_path+''+name+''',no_output '+char(13)
from @t as a left join @dirtree_1 as b on a.name=b.subdirectory and b.files=0 and depth=1 where  b.id is null
--获取需要创建的文件夹-------------------end


--生成处理过期备份的sql语句-------------start
if @overdueDay>0
begin
    insert into @dirtree_2(subdirectory,depth,files) exec('master.dbo.xp_dirtree '''+@bak_path+''',0,1')
    if @baktype =0 
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Full_bak_',subdirectory)=0 
    if @baktype =1 
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Diff_bak_',subdirectory)=0 
    if @baktype=2
    delete from @dirtree_2 where depth=1 or files=0 or charindex('_Log_bak_',subdirectory)=0 
    if exists(select 1 from @dirtree_2)
    delete from @dirtree_2 where isdate(
            left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +  
            substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2) 
            )=0
    if exists(select 1 from @dirtree_2)
    update @dirtree_2 set dbname = case when @baktype=0 then left(subdirectory,charindex('_Full_bak_',subdirectory)-1)
        when @baktype=1 then left(subdirectory,charindex('_Diff_bak_',subdirectory)-1) 
        when @baktype=2 then left(subdirectory,charindex('_Log_bak_',subdirectory)-1) 
        else '' end    
        ,baktime=left(right(subdirectory,19),8)+' '+ substring(right(subdirectory,20),11,2) + ':' +  
            substring(right(subdirectory,20),13,2) +':'+substring(right(subdirectory,20),15,2) 
    from @dirtree_2 as a
    delete @dirtree_2 from @dirtree_2 as a left join @t as b on b.name=a.dbname where b.id is null
    update @dirtree_2 set isLastbak= case when (select max(baktime) from @dirtree_2 where dbname=a.dbname)=baktime 
    then 1 else 0 end from @dirtree_2 as a
    select @delbak=isnull(@delbak,'')+'exec master.dbo.xp_cmdshell ''del '+@bak_path+''+dbname+'\'
    +subdirectory+''',no_output '+char(13) from @dirtree_2 where isLastbak=0 and datediff(day,baktime,getdate())>@overdueDay
end
--生成处理过期备份的sql语句--------------end




begin try
    print(@createfolder)  --创建备份所需文件夹
    exec(@createfolder)   --创建备份所需文件夹
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
    return
end catch


select @i=1 ,@maxid=max(id) from @t
while @i<=@maxid
begin
    select @bak_sql=''+char(13)+'backup '+ case when @baktype=2 then 'log ' else 'database ' end
            +quotename(Name)+' to disk='''+@bak_path + Name+'\'+
            Name+ case when @baktype=0 then '_Full_bak_' when @baktype=1 then '_Diff_bak_' 
            when @baktype=2 then '_Log_bak_' else null end + case when @compression=1 then 'compression_' else '' end+
            replace(replace(replace(convert(varchar(20),getdate(),120),'-',''),' ','_'),':','')+
            case when @baktype=2 then '.trn' when @baktype=1 then '.dif' else '.bak' end +'''' 
            + case when @compression=1 or @baktype=1 then ' with ' else '' end
            + case when @compression=1 then 'compression,' else '' end
            + case when @baktype=1 then 'differential,' else '' end
            + case when @compression=1 or @baktype=1 then ' noformat' else '' end 
    from @t where id=@i
    set @i=@i+1
    begin try
        print(@bak_sql)--循环执行备份
        exec(@bak_sql) --循环执行备份
    end try
    begin catch
        print 'err:'+ltrim(error_number())
        print 'err:'+error_message()
    end catch
end

begin try
    print(@delbak)   --删除超期的备份
    exec(@delbak)    --删除超期的备份
end try
begin catch
    print 'err:'+ltrim(error_number())
    print 'err:'+error_message()
end catch


--关闭xp_cmdshell支持
--exec sp_configure 'show advanced options', 1
--reconfigure with override
--exec sp_configure 'xp_cmdshell', 1 
--reconfigure with override
--exec sp_configure 'show advanced options', 0
--reconfigure with override

[obuntu]

可以考虑下powershell

#by obuntu
# 2010-8-23
#将F:\bk_test里的所有文件，包括目录，满足一定条件的移动到 F:\bk_test2下
$sourcePath="F:\bk_test"
$targetPath="F:\bk_test2"
$sourcePathD=$sourcePath+"\*"
$DateToCompare = (Get-date).AddDays(-7)
Copy-Item $sourcePathD $targetPath -Force
Get-ChildItem -Path $sourcePath -Recurse -include *.* |where-object {$_.lastwritetime –lt $DateToCompare}`
| Move-Item -Destination { Join-Path $targetPath $_.FullName.SubString(($
sourcePath).Length) } -Force

http://topic.csdn.net/u/20100824/21/562dc23b-9adf-4cfc-9fa3-3d11cc796a34.html

[王向飞]

其实你这么写没问题，你先删掉没用的文件（五天前的所有文件），
然后你的作业会每天运行，也就是每天就会把之前第五天的文件删除，没问题啊。