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

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

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

---

 

备份sp： dbo.dba_db_backup

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

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

/*
editor:Lynn
function desc:
backup full/diff database
backup log
backupdir @bakpathYYYYMMDD_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