数据库备份存储过程

IF OBJECT_ID('Pr_Backup','P') IS NULLBEGINEXEC('CREATE PROCEDURE dbo.Pr_Backup AS RETURN')ENDGO/* =============================================-- 作者: AirSoft-- 日期: 2015-08-13-- 描述: 支持全备、差异和日志备份数据库-- 示例: DECLARE @ReturnDesc NVARCHAR(128)EXEC Pr_Backup@BackupDB   = 'DBATools',@BakType    = 1,@ReturnDesc = @ReturnDesc OUTPUTSELECT @ReturnDesc-- =============================================*/ALTER PROCEDURE dbo.Pr_Backup(@BackupDBNVARCHAR(128),   @BakTypeINT,--0完全备份，1差异备份，2日志备份@ReturnDesc NVARCHAR(MAX) OUTPUT)ASBEGIN TRY--DECLARE @BakType INT    -- 0完全备份，1差异备份，2日志备份--DECLARE @BackupDB NVARCHAR(128)DECLARE @ExecSql NVARCHAR(MAX)DECLARE @CmdCode NVARCHAR(1024)DECLARE @LogName NVARCHAR(128)DECLARE @BakPath NVARCHAR(512)DECLARE @BakFile NVARCHAR(128)DECLARE @NowFile NVARCHAR(128)DECLARE @TDbSize TABLE(dbsize bigint,logsize bigint)DECLARE @TLogName TABLE(DBLogName NVARCHAR(32))DECLARE @DBLogSize INTDECLARE @Rc   INTDECLARE @NoLog     INTSET @BakPath = 'F:\DBBak'SET @NowFile = CONVERT(NVARCHAR,GETDATE(),112)+REPLACE(CONVERT(NVARCHAR,GETDATE(),108),':','')SET @BakFile = @NowFile + CASE WHEN @BakType = 1 THEN '.dif'WHEN @BakType = 2 THEN '.trn'ELSE '.bak'END-- 创建备份目录SET @CmdCode = N'if not exist '+ @BakPath + ' mkdir ' + @BakPathEXEC @rc = master..xp_cmdshell @CmdCode   --注意需要开启xp_cmdshell-- 完全备份才做截断日志的操作IF @BakType = 0BEGINSET @ExecSql = 'SELECT sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) AS dbsize, '  + ' sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) AS logsize ' + ' FROM ' + @BackupDB + '.dbo.sysfiles'DELETE FROM @TDbSizeINSERT INTO @TDbSize(dbsize,logsize)EXEC @Rc = sp_executesql @ExecSql--获取日志文件大小SELECT @DBLogSize= (logsize * 8192 / 1048576 / 1024) --转换成G进行比较FROM @TDbSizeIF ( @DBLogSize >=2 ) --大于2GBEGINIF CHARINDEX('Microsoft SQL Server 2005',@@version) > 0SET @ExecSql = 'BACKUP LOG ' + @BackupDB + ' WITH NO_LOG ;'   --SQL2005版本支持脚本截断日志ELSE SET @ExecSql = 'ALTER DATABASE ' + @BackupDB + ' SET RECOVERY SIMPLE WITH NO_WAIT ;'    -- 设置截断日志成功标记0为已截断日志    EXEC @NoLog = sp_executesql @ExecSqlEND-- 日志截断成功才进行日志收缩IF @NoLog = 0 BEGINSET @ExecSql ='SELECT NAME FROM ' + @BackupDB + '.sys.database_files WHERE Type=1'SET @LogName = NULL DELETE FROM @TLogNameINSERT INTO @TLogNameEXEC sp_executesql @ExecSqlSELECT @LogName = DBLogName FROM @TLogNameIF ISNULL(@LogName,'') <> '' BEGINSET @ExecSql = 'USE ' + @BackupDB + ';DBCC SHRINKFILE( ' + @LogName + ' , TRUNCATEONLY ) WITH NO_INFOMSGS ;'EXEC @Rc = sp_executesql @ExecSql ENDIF CHARINDEX('Microsoft SQL Server 2005',@@version) <= 0BEGINSET @ExecSql = 'ALTER DATABASE ' + @BackupDB + ' SET RECOVERY FULL WITH NO_WAIT ;'END         EXEC @Rc = sp_executesql @ExecSql ENDENDSET @ExecSql = CASE WHEN @BakType = 1 THEN 'Backup DataBase ' + @BackupDB + ' to Disk = ''' + @BakPath + '\' + @BakFile + '''' + ' WITH DIFFERENTIAL 'WHEN @BakType = 2 THEN 'Backup Log ' + @BackupDB + ' to Disk = ''' + @BakPath + '\' + @BakFile + ''''ELSE 'Backup DataBase ' + @BackupDB + ' to Disk = ''' + @BakPath + '\' + @BakFile + ''''ENDEXEC @Rc = sp_executesql @ExecSql  SET @ReturnDesc = '备份成功。'RETURN 1END TRYBEGIN CATCHSET @ReturnDesc = '备份过程中出现以下异常：' + ERROR_MESSAGE()RETURN -1END CATCHGO