分享:sp_backupdb 数据库备份方案

USE master 
go 
IF object_id('sp_backupdb','P') IS NOT NULL
DROP PROC sp_backupdb
go 

CREATE     PROC sp_backupdb  
@db_list VARCHAR(4000) = '' , @type VARCHAR(128) = 'full' ,@dir_path VARCHAR(512) = 'E:\DB_BACKUP' ,@del_days_ago SMALLINT = 7 
AS
/*
作者:陈恩辉-弘恩 
*/
IF ISNULL(@type,'') NOT IN ('full','diff')
BEGIN 
	PRINT '参数  @type 只能是full,diff两种类型!' 
	RETURN 
END 
SET NOCOUNT ON ;
DECLARE @db_dir VARCHAR(4000) ,@db NVARCHAR(128)
DECLARE c CURSOR FAST_FORWARD FOR 
SELECT name  FROM master.sys.databases
WHERE database_id > 4 
	AND CHARINDEX ( ',' + name + ',' ,','+@db_list+',' ) > 0 
OPEN c 
FETCH NEXT FROM c INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN 
	--1.创建文件夹开始
   SET @db_dir =  @dir_path  + '\' +  @db  
   EXEC master.dbo.xp_create_subdir  @db_dir
	--1.创建文件夹结束
	
	IF @type = 'full' 
	BEGIN 
		--2完整备份开始
		DECLARE @sql_full_backup NVARCHAR(4000),@cmd_del_backup_files NVARCHAR(4000)
		SET @sql_full_backup = ' BACKUP DATABASE ['+ @db +'] TO DISK = '''+ @dir_path + '\' +  @db + '\' +  @db + '_full_backup_'+ CONVERT(VARCHAR(10) ,GETDATE(),112) +'_'+ RIGHT('0'+ CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR),2) + RIGHT('0'+ CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR),2) + '.bak''  '
		PRINT @sql_full_backup 
		EXEC (@sql_full_backup)

		--2完整备份结束
		--3删除历史文件,保留几天的备份文件
		SET @cmd_del_backup_files = ' dir ' + @db_dir + ' /b/a '
		--PRINT @cmd_del_backup_files
		CREATE TABLE #backup_full_files ( files VARCHAR(512))
		INSERT INTO #backup_full_files ( files )
		EXEC xp_cmdshell  @cmd_del_backup_files
		
		IF NOT EXISTS (SELECT 1 FROM #backup_full_files WHERE files LIKE '%'+CONVERT(VARCHAR(10),GETDATE(),112)+'%'+'.bak')
		BEGIN 
		SELECT 1
		END 
		
		DECLARE c_del_files_full_backup CURSOR FAST_FORWARD FOR 
		SELECT files--,CAST(SUBSTRING(files,LEN(files)- 14,8) AS DATETIME) dt  
		FROM #backup_full_files 
		WHERE files LIKE '%.bak' 
		AND CASE WHEN ISNUMERIC(SUBSTRING(files,LEN(files)- 16,8)) = 1 THEN  CAST(SUBSTRING(files,LEN(files)- 16,8) AS DATETIME) ELSE '1901-01-01' END  < DATEADD(DAY,-1*ABS(@del_days_ago),GETDATE())
		OPEN c_del_files_full_backup
		FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @cmd_del_backup_files = 'del ' + @db_dir + '\' + @cmd_del_backup_files 
			PRINT @cmd_del_backup_files
			EXEC xp_cmdshell @cmd_del_backup_files --执行删除CMD命令
			FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files 
		END 
		CLOSE c_del_files_full_backup      --关闭游标
		DEALLOCATE c_del_files_full_backup --销毁游标
		
		DROP TABLE #backup_full_files
		--3删除历史文件,保留几天的备份文件

	END 
	
	IF @type = 'diff'
	BEGIN 
		DECLARE @sql_diff_backup NVARCHAR(4000),@cmd_del_backup_files_diff NVARCHAR(4000)
		SET @sql_diff_backup = ' BACKUP DATABASE ['+ @db +'] TO DISK = '''+ @dir_path + '\' +  @db + '\' +  @db + '_diff_backup_'+ CONVERT(VARCHAR(10) ,GETDATE(),112) +'_'+ RIGHT('0'+ CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR),2) + RIGHT('0'+ CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR),2) + '.diffbak'' WITH  DIFFERENTIAL  '
		PRINT @sql_diff_backup 
		EXEC (@sql_diff_backup)
		SET @cmd_del_backup_files_diff = ' dir ' + @db_dir + ' /b/a '
		PRINT @cmd_del_backup_files_diff
		CREATE TABLE #backup_diff_files ( files VARCHAR(512))
		INSERT INTO #backup_diff_files ( files )
		EXEC xp_cmdshell  @cmd_del_backup_files_diff
		
		DECLARE c_del_files_full_backup CURSOR FAST_FORWARD FOR 
		SELECT files--,CAST(SUBSTRING(files,LEN(files)- 14,8) AS DATETIME) dt  
		FROM #backup_diff_files 
		WHERE files LIKE '%.diffbak' 
		AND CASE WHEN ISNUMERIC(SUBSTRING(files,LEN(files)- 14,8)) = 1 THEN  CAST(SUBSTRING(files,LEN(files)- 16,8) AS DATETIME) ELSE '1901-01-01' END  < DATEADD(DAY,-1*ABS(@del_days_ago),GETDATE())
		OPEN c_del_files_full_backup
		FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @cmd_del_backup_files_diff = 'del ' + @db_dir + '\' + @cmd_del_backup_files_diff 
			PRINT @cmd_del_backup_files_diff
			EXEC xp_cmdshell @cmd_del_backup_files_diff --执行删除CMD命令
			FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff 
		END 
		CLOSE c_del_files_full_backup      --关闭游标
		DEALLOCATE c_del_files_full_backup --销毁游标
		
		DROP TABLE #backup_diff_files
	END 

   FETCH NEXT FROM c INTO @db

END 
CLOSE c			--关闭游标
DEALLOCATE c	--销毁游标
 

go