数据库备份存储过程

IF OBJECT_ID('Pr_Backup','P') IS NULL
BEGIN
	EXEC('CREATE PROCEDURE dbo.Pr_Backup AS RETURN')
END
GO

/* =============================================
-- 作者: AirSoft
-- 日期: 2015-08-13
-- 描述: 支持全备、差异和日志备份数据库
-- 示例: 

DECLARE @ReturnDesc NVARCHAR(128)
EXEC Pr_Backup
@BackupDB   = 'DBATools',
@BakType    = 1,
@ReturnDesc = @ReturnDesc OUTPUT
SELECT @ReturnDesc

-- =============================================
*/
ALTER PROCEDURE dbo.Pr_Backup(
@BackupDB	NVARCHAR(128),   
@BakType	INT,			--0完全备份，1差异备份，2日志备份
@ReturnDesc NVARCHAR(MAX) OUTPUT
)
AS
BEGIN 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 INT
	DECLARE @Rc		   INT
	DECLARE @NoLog     INT

	SET @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 ' + @BakPath
	EXEC @rc = master..xp_cmdshell @CmdCode   --注意需要开启xp_cmdshell

	-- 完全备份才做截断日志的操作
	IF @BakType = 0
	BEGIN
		SET @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 @TDbSize
		INSERT INTO @TDbSize(dbsize,logsize)
		EXEC @Rc = sp_executesql @ExecSql

		--获取日志文件大小
		SELECT 
			@DBLogSize= (logsize * 8192 / 1048576 / 1024) --转换成G进行比较
		FROM @TDbSize

		IF ( @DBLogSize >=2 ) --大于2G
		BEGIN
			IF CHARINDEX('Microsoft SQL Server 2005',@@version) > 0
				SET @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 @ExecSql		
		END
	
		-- 日志截断成功才进行日志收缩
		IF @NoLog = 0 
		BEGIN
			SET @ExecSql ='SELECT NAME FROM ' + @BackupDB + '.sys.database_files WHERE Type=1'
			SET @LogName = NULL 
			
			DELETE FROM @TLogName
			INSERT INTO @TLogName
			EXEC sp_executesql @ExecSql	
			
			SELECT @LogName = DBLogName FROM @TLogName
			
			IF ISNULL(@LogName,'') <> '' 
			BEGIN
				SET @ExecSql = 'USE ' + @BackupDB + ';DBCC SHRINKFILE( ' + @LogName + ' , TRUNCATEONLY ) WITH NO_INFOMSGS ;'
				EXEC @Rc = sp_executesql @ExecSql 
			END
	
			IF CHARINDEX('Microsoft SQL Server 2005',@@version) <= 0
			BEGIN
				SET @ExecSql = 'ALTER DATABASE ' + @BackupDB + ' SET RECOVERY FULL WITH NO_WAIT ;'
			END 
			        
			EXEC @Rc = sp_executesql @ExecSql 
		END
	END

	SET @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 + ''''
		END

	EXEC @Rc = sp_executesql @ExecSql  

	SET @ReturnDesc = '备份成功。'
	RETURN 1
END TRY
BEGIN CATCH

	SET @ReturnDesc = '备份过程中出现以下异常：' + ERROR_MESSAGE()
	RETURN -1

END CATCH
GO