backup(full,diff,log)备份并清理过时备份

需求说明：每天备份数据库，但是备份文件长期积累不删除，极大的浪费磁盘空间,

针对该情况做了2个sp来解决

---

 

备份sp： dbo.dba_db_backup

可以做全备，差备，日志备

备份的目录格式 ＹＹＹＹＭＭＤＤ＿ＦＵＬＬ／ＤＦ／ＬＯＧ

/*
editor:Lynn
function desc: 
    backup full/diff database   backup log 
    backupdir @bakpath\YYYYMMDD_FULL/DF/LOG
exec demo:
EXEC DBO.dba_db_backup 
EXEC DBO.dba_db_backup  N'lynntest,SSB3',N'D:\DbBak',N'FULL'
*/
create PROC DBO.dba_db_backup
 @db_list nvarchar(2000)=NUll    -- NULL:all db except system db,  N'lynntest,SSB3'
    ,@bakpath nvarchar(1000)=N'D:\BackupDB'    --N'D:\bak'
    ,@backupType nvarchar(4)=N'FULL'    --DEFAULT: FULL   --FULL全备份  DF差异备份 LOG日志备份
AS
BEGIN 
set nocount on;
/*
    select @db_list=N'lynntest,SSB3'
            ,@bakpath =N'D:\bak'
            ,@backupType=N'LOG'  --DB全备份  DF差异备份 LOG日志备份
*/

    declare  @dblist table (id int identity(1,1), db  sysname)
    IF @db_list IS NOT NULL
    BEGIN
        insert into @dblist(db)
        select  T.name
        from(

            SELECT
                        name = PARSENAME(
                                        LTRIM(RTRIM(T.c.value('.[1]', 'sysname'))),
                                        1
                                )
                    FROM(
                        SELECT
                            database_name_list = CONVERT(xml,
                                                        N'<c><![CDATA[' 
                                                        + REPLACE(
                                                            REPLACE(
                                                                REPLACE(
                                                                    @db_list,
                                                                    CHAR(13),
                                                                    CHAR(10)
                                                                ),
                                                                CHAR(10),
                                                                N','
                                                            ),
                                                            N',',
                                                            N']]></c><c><![CDATA['
                                                        )
                                                        + N']]></c>'
                                                    )
        
                    )REQ
                        CROSS APPLY REQ.database_name_list.nodes('/c/text()') T(c) 
                ) T
                inner join sys.databases d
                    on T.name=d.name  and d.state=0
                WHERE T.name > N''
        END
    ELSE
    BEGIN
        insert into @dblist(db)
        select name
        from sys.databases with(nolock)
        WHERE state in ( 0 )  --OFFLINE
        --==============you can filter the system database or not
        and name not in( 'master', 'tempdb', 'model', 'msdb','distribution' );
    END
    
        declare @currnpath NVARCHAR(520)
            ,@subpath  nvarchar(200)

        select @subpath=LTRIM( RTRIM(convert(  char(8) ,getdate() ,112)+'_'+@backupType))
            ,@currnpath=  @bakpath+'\'+@subpath+'\'  --RTRIM( @bakpath+'\'+@subpath+'\')


        DECLARE @temp table(subdirectory varchar(500),depth int)
        INSERT @temp
        exec xp_dirtree @bakpath
        --select * from @temp

        IF NOT EXISTS(
            SELECT *
            FROM @temp where LTRIM( RTRIM(subdirectory))=@subpath
        )
        --创建目录
        EXEC master.sys.xp_create_subdir @currnpath

        DECLARE @tunid  int,@db sysname,@sql nvarchar(max)
        while exists(select * from @dblist)
        BEGIN
            select @tunid=0,@db=null,@sql=null
            select top(1) @tunid =id,@db=db from @dblist 
            select @sql='BACKUP '+ CASE @backupType 
                                        WHEN N'log ' THEN N'LOG '
                                        ELSE N'DATABASE '
                                    END+ @db +char(10)+'TO DISK =N'''+@currnpath+@db
                                    +CASE @backupType 
                                        WHEN N'log' THEN N'.trn'' '
                                        ELSE N'.bak'' '
                                    END+CHAR(10)
                                    +CASE @backupType 
                                        WHEN N'DF' THEN N'  WITH DIFFERENTIAL, COMPRESSION '
                                        ELSE N'  WITH COMPRESSION '
                                    END
            --PRINT @sql
            EXEC(@sql)
            DELETE TOP(1)  @dblist
            WHERE ID=@tunid
        END


END

 

删除文件目录sp：dbo.dba_db_backup_delDir

删除以上sp生成的格式化目录 ，

/*
editor:Lynn
function dec:
    delete expired backup dirs
exec demo:
  exec dba_db_backup_delDir N'D:\DbBak'    ,N'FULL',1    

*/
alter proc dbo.dba_db_backup_delDir
    @bakpath nvarchar(1000)=N'D:\BackupDB'    --N'D:\bak'
    ,@backupType varchar(10)=N'FULL'    --DEFAULT: FULL   --FULL全备份  DF差异备份 LOG日志备份
    ,@n int=2                     --保留最近n次相关备份
AS
BEGIN
    SET NOCOUNT ON
    if object_id('tempdb.dbo.#temp','U') IS NOT NULL
        DROP TABLE #temp
    CREATE table #temp (id int identity(1,1),subdirectory varchar(500),depth int,backupType  varchar(10),backupdate date,isdel int)
    INSERT #temp(subdirectory,depth)
    exec xp_dirtree @bakpath


    update #temp
    set subdirectory=rtrim(ltrim(subdirectory))

    update #temp
    set 
        backupType=right(subdirectory,len(subdirectory)-9)
        ,backupdate=cast(left(subdirectory,8) as date)

    delete  #temp
    where backupType<>@backupType

    declare @sql nvarchar(max)
    select @sql=N'update  a 
        set isdel=0
    from #temp a
    inner join 
    (
        select top (@top) id  from #temp 
        where 
            backupdate<=getdate()  and backupType=@backupType1 
        order by backupdate desc
    )b
    on b.id=a.id'
    exec sp_executesql @sql,N'@top int,@backupType1 varchar(10)', @top=@n,@backupType1=@backupType
    --select * from #temp
    declare @errortb table(id int identity(1,1),errorinfo nvarchar(200))
    declare @turnid int,@subdirectory varchar(500)
    while exists (select * from #temp where isdel is null)
    begin
        select @turnid=null,@subdirectory=null
        select top(1) @turnid=id ,@subdirectory=subdirectory  from #temp where isdel is null

        --print @bakpath +'\'+ @subdirectory
        insert into @errortb(errorinfo)
        EXEC('xp_cmdshell ''rd /s/q  ' + @bakpath +'\'+ @subdirectory + '''')
        delete #temp where id=@turnid    
    end
    --select * from @errortb order by id

END

note:

1 创建JOb定时备份和清理目录,可以采用 dbo.dba_db_backup调用dbo.dba_db_backup_delDir简化job创建

2 sp中有创建和删除目录，所以需要开启高级选项

 

-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO