分享: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) = 'QPAccountsDB,QPGameMatchDB,QPGameScoreDB,QPNativeWebDB,QPPlatformDB,QPPlatformManagerDB,QPRecordDB,QPTreasureDB'
 ,
    @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 VARCHAR(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 VARCHAR(4000) ,
                        @cmd_del_backup_files VARCHAR(4000),
						@with_COMPRESSION VARCHAR(20) = CASE WHEN @@version LIKE '%2008%' then ' with COMPRESSION ' ELSE '' END ;
                    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''  ' + @with_COMPRESSION 
                    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 VARCHAR(4000) ,
                        @cmd_del_backup_files_diff VARCHAR(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