/*
脚本说明：数据库备份
    
要点：
    1 备份文件名规则：库名_backup_yyyymmdd
    2 全备后缀为 bak 差异备份后缀为 dif 日志备份后缀为 trn
    2 在指定路径生成指定类型备份，备份细节由初始参数决定

注意：
    在try catch中只能捕获最后一条错误日志，要得到详细的错误原因，需要去掉try捕获
*/


--定义参数
DECLARE @DatabaseName VARCHAR(100)
DECLARE @Path VARCHAR(200)
DECLARE @PathfileName VARCHAR(200)
DECLARE @BackupName VARCHAR(200)   --备份名
DECLARE @NowDateTime VARCHAR(100)  --当前日期时间
DECLARE @v1 VARCHAR(500)       --参数1
DECLARE @Err VARCHAR(500)
DECLARE @mail VARCHAR(500)
DECLARE @mailUser VARCHAR(500)
DECLARE @backupSetId AS INT  
DECLARE @Date  VARCHAR(8)      --yyyymmdd日期
DECLARE @command VARCHAR(50)
DECLARE @backType VARCHAR(50)  -- full 全备  dif  差异备  log  日志备
DECLARE @IsdateDir  INT           --是否建立日志格式之文件夹
DECLARE @log_level INT

--参数设定
SET @DatabaseName = 'ZXFinance_Test3'
SET @Path = 'E:\DBBackup\'
SET @backType='full'    -- full 全备  dif  差异备  log  日志备
SET @IsdateDir=1       --1 path中建立日期文件夹  0 保存于path中的根目录
SET @log_level=1       -- 0 异常邮件通知 ，1全备通知，2备份通知
SET @mailUser='zhao'   --操作员账号

--生成当前时间戳
SELECT @NowDateTime = STUFF(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 120), '-', ''), ' ', ''), ':', ''),9,0,'_')  
SELECT @Date=CONVERT(VARCHAR(17), GETDATE(), 112)

--路径结尾补全
IF SUBSTRING(@Path,len(@Path),1)<>'\' SET @Path=@Path+'\'

--建立子文件夹
IF @IsdateDir=1 
BEGIN
    SET @Path=@Path+@Date+'\'
    SET @command='mkdir '+@Path
    EXEC xp_cmdshell @command
END


--防止文件夹不存在报错
IF @IsdateDir=0
BEGIN
    SET @Path=@Path
    SET @command='mkdir '+@Path
    EXEC xp_cmdshell @command
END

--生成文件名
SET @BackupName = @DatabaseName + '_backup_'+@backType+'_'+@NowDateTime

SELECT  @PathfileName = @Path + @DatabaseName + '_backup_'+@backType+'_'+@Date+ (CASE @backType WHEN 'dif' THEN '.dif' 
                                                             WHEN 'log'THEN '.trn'
                                                             WHEN 'full'THEN '.bak'
                                                             end)

BEGIN TRY

if @backType='dif' 
begin
--差异备份
BACKUP DATABASE @DatabaseName TO  DISK = @PathfileName
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = @BackupName, 
SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

END

IF @backType='full' 
BEGIN
--全备份
  BACKUP DATABASE @DatabaseName TO  DISK = @PathfileName 
  WITH NOFORMAT, NOINIT,  NAME = @BackupName, 
  SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10

END

IF @backType='log' 
BEGIN
--日志备份
BACKUP LOG @DatabaseName TO  DISK = @PathfileName
  WITH  NOFORMAT, NOINIT,  NAME = @BackupName, 
  SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
END

--提取备份状态
SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = @DatabaseName
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = @DatabaseName
                            )
IF @backupSetId IS NULL 
    BEGIN
        SET @V1 = '验证失败。找不到数据库' + @DatabaseName + '的备份信息。'
            RAISERROR(@V1, 16, 1) 
            EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser,
                @subject = N'备份异常', @body = @V1
    END


--备份校验  
RESTORE VERIFYONLY FROM  DISK = @PathfileName 
  WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
  
--备份完成通知管理员
SET @mail = '备份完成，备份文件路径:' + @PathfileName
IF (@log_level=1 AND  @backType IN('full') ) OR (@log_level=2 AND  @backType IN( 'full','dif','log'))
EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser, @subject = N'备份完成', @body = @mail
    

END TRY
BEGIN CATCH

    SELECT  ERROR_NUMBER() AS ErrorNumber ,
            ERROR_SEVERITY() AS ErrorSeverity ,
            ERROR_STATE() AS ErrorState ,
            ERROR_PROCEDURE() AS ErrorProcedure ,
            ERROR_LINE() AS ErrorLine ,
            ERROR_MESSAGE() AS ErrorMessage ;
        
    SET @Err = @backType+'备份异常！错误原因:' + ERROR_MESSAGE()

    EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser, @subject = '备份异常', @body = @Err
END CATCH