USE [master]
GO
/****** Object: StoredProcedure [dbo].[AutoBakDataBaseByLogSize] Script Date: 2017/8/8 10:22:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<MrLi>
-- Create date: <2017-06-13 12:09:45>
-- Description: 根据日志剩余空间大小备份数据库及收缩日志文件
-- 1：读取日志的剩余空间-》判断空间低于20%时（可传参）》执行数据库完整、覆盖备份，
-- 2：同时收缩日志文件大小为10MB（可传参）
-- @DBName：数据库名(多个库名用“，”号分割)
-- @FolderPath：备份文件存放的文件夹路径 默认值：C:\AutoBakDBByLogSize\
-- @LogMinimum：日志空间最低值百分比：默认为20
-- @LogDefaultSize：收缩日志的默认大小，默认值为10MB
-- =============================================
CreatePROCEDURE [dbo].[AutoBakDataBaseByLogSize]
@DBNames varchar(1000) = 'Platform_U_v3.0', --默认数据库名
@FolderPath varchar(200) = 'C:\AutoBakDBByLogSize\',
@LogMinimum int = 20,
@LogDefaultSize int = 10
AS
BEGIN
begin try
declare @DBName varchar(300);--当前遍历的数据库名
declare @douHaoIndex int;--当前遍历的逗号下标
declare @index int;--当前遍历的对象下标
--在传入的字符串上拼接一个“，”
set @DBNames = @DBNames+',';
set @douHaoIndex=CHARINDEX(',',@DBNames);
set @index=1
WHILE @douHaoIndex>0
BEGIN
--处理当前遍历的数据库名
set @DBName=substring(@DBNames,@index,@douHaoIndex-@index)
--print @DBName
set @index=@douHaoIndex+1
set @douHaoIndex=CHARINDEX(',',@DBNames,@index)
--申明变量
declare @Sql varchar(1000);
declare @BakFileFullPath varchar(200);
declare @RemainingSpace int; --剩余空间（百分比）
set @BakFileFullPath = @FolderPath+'AutoBakFullDB_'+@DBName+'.bak';
--根据剩余空间判断是否继续操作
declare @Sql2 nvarchar(300);
declare @SpaceSizeGet int;
set @Sql2 = 'select @param = (select CONVERT(int,((maxsize/128)-(size/128)) / ( maxsize/128.0) * 100) as ''剩余空间（%）'' from ['+@DBName+'].[dbo].[sysfiles] where filename like ''%.ldf'')';
exec sp_executesql @Sql2,N'@param int output',@SpaceSizeGet output
if(@SpaceSizeGet >= @LogMinimum)
continue;
--创建备份文件夹
--EXEC sp_configure 'show advanced options', 1
--GO
--RECONFIGURE
--GO
--EXEC sp_configure 'xp_cmdshell', 1
--RECONFIGURE
--GO
exec('EXEC xp_cmdshell ''mkdir '+@FolderPath+'''');
--拼接完整覆盖备份脚本
set @Sql='BACKUP DATABASE ['+@DBName+'] TO DISK = N'''+@BakFileFullPath+''' WITH NOFORMAT, INIT,'+
'NAME = N''['+@DBName+']-完整备份，执行时间：'+Convert(varchar(20),getdate(),120)+''','+
'SKIP,'+
'NOREWIND,'+
'NOUNLOAD,'+
'COMPRESSION,'+
'STATS = 10;';
--拼接收缩日志文件语句
set @Sql = @Sql+
' Use ['+@DBName+']'+
--获取日志文件名称
' declare @LogName varchar(50);'+
' SELECT @LogName = Name FROM sys.database_files where physical_name like ''%.ldf'';'+
--设置数据库为简单模式
' ALTER DATABASE ['+@DBName+']'+
' SET RECOVERY SIMPLE;'+
--收缩日志文件为1MB
' DBCC SHRINKFILE (@LogName, '+Convert(varchar(10),@LogDefaultSize)+',truncateonly);'+
--设置数据库为完整模式'
' ALTER DATABASE ['+@DBName+']'+
' SET RECOVERY FULL';
--执行脚本
exec(@Sql);
END
end try
begin catch
print('异常详情——错误号：'+Convert(varchar(20),ERROR_NUMBER())+';错误消息：'+Error_Message()+';错误行号：'+Convert(varchar(20),ERROR_LINE()));
end catch
END
GO