【1】备份SP
use master go if object_id('Generalization_Backup') is not null drop procedure Generalization_Backup; go set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create PROCEDURE [dbo].[Generalization_Backup] @Database_Name NVARCHAR(50), --输入_备份数据库名称 @Bakup_Type varchar(200), --输入_备份类型:full完整,diff差异备份,tran事务日志 @Bakup_PATH_DIR NVARCHAR(200), --输入_备份文件夹URL @Out_Message NVARCHAR(400) OUT --输出_输出消息 as begin DECLARE @Bakup_Dir NVARCHAR(200), --备份路径上级目录 @SQL NVARCHAR(MAX), --动态语句 @Day NCHAR(8), --备份时间(年月日) @Date NCHAR(11), --备份时间(年月日_小时) @Path NVARCHAR(200), --备份路径 @Bakup_name NVARCHAR(100), --备份文件名 @Bakup_Folder NVARCHAR(200), --备份文件夹名 @bakup_URL nvarchar(1000) --判断数据库名是否正确 IF NOT EXISTS(SELECT TOP 1 NULL FROM master.sys.databases WHERE NAME=@Database_Name) BEGIN SET @Out_Message='未找到名为'+@Database_Name+'的数据库,请检查输入数据库名' RAISERROR (@Out_Message,16,1) RETURN -1 END --创建备份文件夹 EXEC MASTER.dbo.xp_create_subdir @Bakup_PATH_DIR --验证备份文件夹是否创建成功 declare @create_dir_info table (folder varchar(500),tree_high int) SELECT @Bakup_Dir=LEFT(@Bakup_PATH_DIR,LEN(@Bakup_PATH_DIR)-charindex('',REVERSE(@Bakup_PATH_DIR),2)+1) select @Bakup_Folder=SUBSTRING( @Bakup_PATH_DIR,LEN(@Bakup_PATH_DIR)-charindex('',REVERSE(@Bakup_PATH_DIR),2)+2,charindex('',REVERSE(@Bakup_PATH_DIR),2)-2) insert into @create_dir_info exec master.dbo.xp_dirtree @Bakup_Dir,1,0 if not exists(select 1 from @create_dir_info where folder = @Bakup_Folder) begin SET @Out_Message='新建备份文件夹'+@Bakup_PATH_DIR+' 失败！' RAISERROR (@Out_Message,16,1) RETURN -1 end --全备 declare @disk varchar(100),@dbsize_GB decimal(18,3),@disk_freeSize_GB decimal(18,3) declare @disk_info table (drive varchar(100),size_MB int) --获取磁盘分区及对应大小 insert into @disk_info exec master.dbo.xp_fixeddrives --获取我们要备份的数据库现在有多大 select @dbsize_GB=cast(sum(t1.size*8.0/1024/1024) as decimal(18,3)) from master.sys.master_files t1 join master.sys.databases t2 on t1.database_id=t2.database_id where t2.name=@Database_Name --判断当前磁盘空间是否充足,阀值为被备份的数据库+20G select @disk=left(@Bakup_PATH_DIR,1) select @disk_freeSize_GB=cast(size_MB/1024.0 as decimal(18,3)) from @disk_info where drive=@disk if @disk_freeSize_GB<@dbsize_GB+20 begin SET @Out_Message='备份路径：'+@Bakup_PATH_DIR+' 磁盘空间不足（少于备份数据库大小+20G）！' RAISERROR (@Out_Message,16,1) RETURN -1 end declare @create_sondir varchar(1000) if @Bakup_Type='full' begin --创建备份子文件夹 set @create_sondir=@Bakup_PATH_DIR+@Database_Name+'' EXEC MASTER.dbo.xp_create_subdir @create_sondir begin try select @Bakup_name=@database_name+'_'+@bakup_type+'_'+convert(char(8),getdate(),112) set @bakup_URL=@Bakup_PATH_DIR+@Database_Name+''+@Bakup_name+'.bak' set @sql='backup database '+@database_name+' to disk='''+@bakup_URL+''' with init' exec(@sql) end try BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+';错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH end if @Bakup_Type='diff' begin --创建备份子文件夹 set @create_sondir=@Bakup_PATH_DIR+@Database_Name+'' EXEC MASTER.dbo.xp_create_subdir @create_sondir begin try select @Bakup_name=@database_name+'_'+@bakup_type+'_'+convert(char(8),getdate(),112)+'_'+cast(datepart(hour,getdate()) as varchar) set @bakup_URL=@Bakup_PATH_DIR+@Database_Name+''+@Bakup_name+'.bak' set @sql='backup database '+@database_name+' to disk='''+@bakup_URL+''' with init,differential' exec(@sql) end try BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+';错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH end if @Bakup_Type='tran' begin --创建备份子文件夹 set @create_sondir=@Bakup_PATH_DIR+@Database_Name+'' EXEC MASTER.dbo.xp_create_subdir @create_sondir begin try select @Bakup_name=@database_name+'_'+@bakup_type+'_'+convert(char(8),getdate(),112)+'_'+replace(CONVERT(varchar(100), GETDATE(), 24),':','') set @bakup_URL=@Bakup_PATH_DIR+@Database_Name+''+@Bakup_name+'.trn' set @sql='backup log '+@database_name+' to disk='''+@bakup_URL+''' with init' exec(@sql) end try BEGIN CATCH SET @Out_Message='系统输出了错误信息,错误信息为:'+ERROR_MESSAGE()+';错误号为:'+CAST(ERROR_NUMBER() AS NVARCHAR(10))+',执行失败!' RAISERROR (@Out_Message,16,1) RETURN -1 END CATCH end set @Out_Message='OK' end
【2】全备代码
/* 需求：如果数据库小的，就每天备份一次，数据库大的就每周做一次全备，大小阀值为10G */ use master go --select * from master..small_db --select * from master..big_db --select * from master..backup_info --select * from master..tran_db --select * from master..diff_db set nocount on go --【1】构造临时存放表 --构造小数据库 small_db if object_id('small_db') is not null begin drop table small_db end ;with temp1 as ( select t2.name,sum(t1.size*8.0/1024/1024) as size_GB,case when isnumeric(right(t2.name,6) )=0 then t2.name+'_300000' else t2.name end as temp_name from master.sys.master_files t1 join master.sys.databases t2 on t1.database_id=t2.database_id where t2.name not in ('tempdb','model') group by t2.name ) ,temp2 as ( select *, row_number() over(partition by left(t2.temp_name,len(t2.temp_name)-7) order by cast(right(t2.temp_name,6) as int) desc) as rn from temp1 t2 ) select t2.name,t2.size_GB,row_number() over(order by t2.size_GB) as rn,0 as flag into master..small_db from temp2 t2 where rn=1 and size_GB<=10 --区分大数据库与小数据库的阀值，单位GB --构造大数据库 big_db if object_id('big_db') is not null begin drop table big_db; end ;with temp1 as ( select t2.name,sum(t1.size*8.0/1024/1024) as size_GB,case when isnumeric(right(t2.name,6) )=0 then t2.name+'_300000' else t2.name end as temp_name from master.sys.master_files t1 join master.sys.databases t2 on t1.database_id=t2.database_id where t2.name not in ('tempdb','model') group by t2.name ) ,temp2 as ( select *, row_number() over(partition by left(t2.temp_name,len(t2.temp_name)-7) order by cast(right(t2.temp_name,6) as int) desc) as rn from temp1 t2 ) select t2.name,t2.size_GB,row_number() over(order by t2.size_GB) as rn,0 as flag into master..big_db from temp2 t2 where rn=1 and size_GB>10 --区分大数据库与小数据库的阀值，单位GB -- 构造日志记录表 backup_info if object_id('backup_info') is null create table backup_info( db_name varchar(100), recovery_date varchar(20), operate varchar(50), msg varchar(4000), flag as case when msg='OK' then 1 else 0 end ) --【2】delcare 定义相关变量 与 初始化 use master go declare @bak_path varchar(300),@db_name varchar(300),@operate_type varchar(50) declare @rn int,@rn_count int declare @flag int declare @msg varchar(4000)
--指定备份目录 set @bak_path='d:DB_AUTO_BAK' --【3】小数据库 备份 set @rn=1 select @rn_count=count(1) from master..small_db while @rn<=@rn_count begin set @operate_type='full' select @db_name=name,@flag=flag from master..small_db where rn = @rn begin try if @flag=0 begin exec master..[Generalization_Backup] @Database_Name=@db_name, @Bakup_Type=@operate_type, @Bakup_PATH_DIR=@bak_path, @Out_Message= @msg output if @msg='OK' update master..small_db set flag=1 where rn=@rn end end try begin catch if @msg='ok' set @msg='operate fail!' insert into backup_info(db_name,recovery_date,operate,msg) values(@db_name,convert(char(10),getdate(),120),@operate_type,@msg); end catch set @rn=@rn+1 end --【4】大数据库 备份 set @rn=1 select @rn_count=count(1) from master..big_db while @rn<=@rn_count begin select @db_name=name,@flag=flag from master..big_db where rn = @rn set @operate_type='full' if exists( select 1 from msdb..backupset where type='D' and datediff(day,backup_start_date,getdate())<=7 and database_name=@db_name) and @db_name!='master' begin set @rn=@rn+1 continue end begin try if @flag=0 begin exec master..[Generalization_Backup] @Database_Name=@db_name, @Bakup_Type=@operate_type, @Bakup_PATH_DIR=@bak_path, @Out_Message= @msg output if @msg='OK' update master..big_db set flag=1 where rn=@rn end end try begin catch if @msg='ok' set @msg='operate fail!' insert into backup_info(db_name,recovery_date,operate,msg) values(@db_name,convert(char(10),getdate(),120),@operate_type,@msg); end catch set @rn=@rn+1 end
【3】差异备代码
--备份策略 /* 6小时备份一次，一天备份4次 */ /* --查看当前所需要的备份库有多大 select sum(size_GB) from ( select t2.name,sum(t1.size*8.0/1024/1024) as size_GB ,row_number() over(partition by left(t2.name,len(t2.name)-7) order by cast(right(t2.name,6) as int) desc) as rn from master.sys.master_files t1 join master.sys.databases t2 on t1.database_id=t2.database_id where isnumeric(right(t2.name,6))=1 and t2.name not in ('tempdb','model') group by t2.name union all select t2.name,sum(t1.size*8.0/1024/1024) as size_GB,row_number() over(partition by t2.name order by t2.name) as rn from master.sys.master_files t1 join master.sys.databases t2 on t1.database_id=t2.database_id where isnumeric(right(t2.name,6))=0 and t2.name not in ('tempdb','model') group by t2.name ) t2 where rn=1 --查看磁盘剩余空间 exec master..xp_fixeddrives */ use master go --select * from master..small_db --select * from master..big_db --select * from master..backup_info --select * from master..tran_db --select * from master..diff_db set nocount on go --【1】构造临时表 if object_id('diff_db') is not null drop table master..diff_db select *,row_number() over(order by name) as rn,0 as flag into master..diff_db from ( select name,size_GB from master..big_db union all select name,size_GB from master..small_db ) t --【2】delcare 定义相关变量 与 初始化 use master go declare @bak_path varchar(300),@db_name varchar(300),@operate_type varchar(50) declare @rn int,@rn_count int declare @flag int declare @msg varchar(4000) set @bak_path='d:DB_AUTO_BAK' --【3】差异 备份 set @rn=1 select @rn_count=count(1) from master..diff_db while @rn<=@rn_count begin select @db_name=name,@flag=flag from master..diff_db where rn = @rn if exists(select 1 from msdb..backupset where type='D' and datediff(day,backup_start_date,getdate())<=7 and database_name=@db_name) and @db_name!='master' set @operate_type='diff' begin try if @flag=0 begin exec master..[Generalization_Backup] @Database_Name=@db_name, @Bakup_Type=@operate_type, @Bakup_PATH_DIR=@bak_path, @Out_Message= @msg output if @msg='OK' update master..diff_db set flag=1 where rn=@rn end end try begin catch if @msg='ok' set @msg='operate fail!' insert into backup_info(db_name,recovery_date,operate,msg) values(@db_name,convert(char(10),getdate(),120),@operate_type,@msg); end catch set @rn=@rn+1 end
【4】事务日志备代码
use master; --每15分钟备份一次 --【2】delcare 定义相关变量 与 初始化 declare @bak_path varchar(300),@db_name varchar(300),@operate_type varchar(50) declare @rn int,@rn_count int declare @flag int declare @msg varchar(4000) set @bak_path='d:DB_AUTO_BAK' if object_id('small_db') is not null and object_id('big_db') is not null and object_id('backup_info') is not null begin if object_id('tran_db') is not null drop table tran_db select t1.*,row_number() over(order by t1.name ) as rn into master..tran_db from ( select name,size_GB from master..small_db union all select name,size_GB from master..big_db ) t1 join master.sys.databases t2 on t1.name=t2.name where t2.recovery_model_desc!='simple' --【3】数据库 tran 备份 set @rn=1 select @rn_count=count(1) from master..tran_db while @rn<=@rn_count begin select @db_name=name from master..tran_db where rn = @rn if exists(select 1 from msdb..backupset where type='D' and datediff(day,backup_start_date,getdate())<=7 and database_name=@db_name) begin set @operate_type='tran' begin try print @rn exec master..[Generalization_Backup] @Database_Name=@db_name, @Bakup_Type=@operate_type, @Bakup_PATH_DIR=@bak_path, @Out_Message= @msg output if @msg!='OK' insert into backup_info(db_name,recovery_date,operate,msg) values(@db_name,convert(char(20),getdate(),120),@operate_type,@msg); end try begin catch insert into backup_info(db_name,recovery_date,operate,msg) values(@db_name,convert(char(10),getdate(),120),@operate_type,@msg); end catch end set @rn=@rn+1 end end --select * from backup_info
【5】删除过期备份文件
-- /* 每天一次，5点开始执行 */ --[1]get #temp1 tale use master go if object_id('diff_db') is not null begin select name,row_number() over(order by name) as rn into #temp1 from master..diff_db DECLARE @oldDate DATETIME declare @bak_path nvarchar(300) declare @rn int,@rn_count int declare @db_name varchar(200),@URL varchar(1000) set @rn=1 select @rn_count=count(1) from #temp1 set @bak_path=N'd:DB_AUTO_BAK' SET @oldDate = GETDATE()-7 --sql server2005 while @rn<=@rn_count begin select @db_name=name from #temp1 where @rn=rn set @URL=@bak_path+@db_name+'' EXECUTE MASTER.dbo.xp_delete_file 0, --0: 备份文件，1: 维护计划文本报告 @URL, --文件路径 N'trn', --文件扩展名 @oldDate --在此时间之前的文件一律删除 EXECUTE MASTER.dbo.xp_delete_file 0, --0: 备份文件，1: 维护计划文本报告 @URL, --文件路径 N'bak', --文件扩展名 @oldDate --在此时间之前的文件一律删除 set @rn=@rn+1 end --sql server 2008及以上 /* EXECUTE MASTER.dbo.xp_delete_file 0, --0: 备份文件，1: 维护计划文本报告 @URL, --文件路径 N'trn', --文件扩展名 @oldDate, --在此时间之前的文件一律删除 1 -- 0: URL下的文件 1：URL下的文件及所有子文件夹下的文件 */ end
【6】收缩日志
/* /* 每天一次，5点开始执行 */ */ use master go --drop table #temp2 SELECT t1.name AS logic_name,t2.name ,row_number() over(order by t2.name) as rn into #temp2 FROM master.sys.master_files t1 JOIN master.sys.databases t2 ON t1.database_id=t2.database_id WHERE type_desc='LOG' DECLARE @oldDate DATETIME declare @bak_path nvarchar(300) declare @rn int,@rn_count int declare @db_name varchar(200),@logic_name VARCHAR(200) set @rn=1 select @rn_count=count(1) from #temp2 while @rn<=@rn_count begin select @db_name=name,@logic_name=logic_name from #temp2 where @rn=rn EXEC('USE '+@db_name+';dbcc shrinkfile('+@logic_name+',10);') set @rn=@rn+1 end
【7】清理备份记录 和 作业执行记录
--清理固定时间之前所有的备份历史记录 USE msdb; GO EXEC sp_delete_backuphistory @oldest_date = '2020-08-01';
--清理固定时间之前所有作业的历史记录 USE msdb ; GO EXEC dbo.sp_purge_jobhistory @oldest_date='2020-08-01'; GO
参考官网：
sp_delete_backuphistory sp_purge_jobhistory