• TSQL backup database/log hourly job


    
    alter procedure [zsp_backup_HourlyJob]
        @dbName sysname = null
        , @BackupRootPath varchar(256)
        , @oddWeekPath varchar(256) = 'OddWeek'
        , @evenWeekPath varchar(256) = 'EvenWeek'
        --严禁手动执行
    as
    begin
        --zsp_backup_HourlyJob 'TEST', 'd:\dbbackup'
        declare @now datetime
        set @now = getdate()
        -- 周一 到 周日
        -- 1 2 3 4 5 6 7
        declare @weekDay int
        set @weekDay = (@@Datefirst + datepart(weekday, @now)) % 7
                        + case
                                when
                                    (@@Datefirst + datepart(weekday, @now)) % 7 < 2
                                        then
                                            6
                                else
                                            - 1
                            end
        declare @hour int --钟点
        set @hour = datepart(Hour, @now)
        if @weekDay = 7 --周日
        begin
            if @hour in (4, 5)
            begin
                --周日凌晨 4、5点不做备份,为3点的全备留出足够的时间
                return
            end
        end
        set @BackupRootPath = rtrim(ltrim(isnull(@BackupRootPath,'')))
        set @oddWeekPath = rtrim(ltrim(isnull(@oddWeekPath,'')))
        set @evenWeekPath = rtrim(ltrim(isnull(@evenWeekPath,'')))
        set @dbName = rtrim(ltrim(isnull(@dbName,'')))
        if rtrim(ltrim(isnull(@dbName,''))) = ''
        begin
            set @dbName = db_name()
        end
        if (right(@BackupRootPath,1) != '\')
        begin
            set @BackupRootPath = @BackupRootPath + '\'
        end
        if (right(@oddWeekPath,1) != '\')
        begin
            set @oddWeekPath = @oddWeekPath + '\'
        end
        if (right(@evenWeekPath,1) != '\')
        begin
            set @evenWeekPath = @evenWeekPath + '\'
        end
        declare @BaseDate datetime --基准日期
        set @BaseDate = 0
        declare @weeksDiff int
        set @weeksDiff =
                            datediff(week, @BaseDate, @now)
                            + case
                                    when
                                        (@@Datefirst + datepart(weekday,@BaseDate)) % 7 = 1
                                            then
                                                1
                                    else
                                                0
                                end
                            - case
                                    when
                                        (@@Datefirst + datepart(weekday,@now)) % 7 = 1
                                            then
                                                1
                                    else
                                                0
                                end
        set @BackupRootPath +=
                        case
                            when
                                @weeksDiff % 2 = 0
                                    then
                                        @evenWeekPath
                            else
                                        @oddWeekPath
                        end
        if (RIGHT(@BackupRootPath,1) != '\')
        begin
            set @BackupRootPath += '\'
        end
        declare @w char(1)
        set @w = cast(@weekday as char(1))
        declare @h varchar(2)
        set @h = right('0' + cast(@hour as varchar(2)), 2)
        declare @bakType varchar(25)
        set @bakType = 'LOG'
        declare @bakFile varchar(100)
        set @bakFile = @dbName
                        + '.'
                        + @w
                        + '.'
                        + @h
                        + '.'
        declare @path varchar(max)
        declare @result int = 0
        set @path =
                        @BackupRootPath
                        + @bakFile
                        + @bakType
                        + '.bak'
        exec zsp_Backup_Log @dbname, @path, @result out
        select
            GETDATE()                    as [DealTime]
            , 'zsp_Backup_Log'            as [Operation]
            , @dbname                    as [DataBaseName]
            , @path                        as [BackupFilePath]
            , @result                    as [Result]
        if @hour = 3 -- 03:00
        begin
            if @weekDay = 7 -- 周日 full backup
            begin
                set @bakType = 'FUL'
                set @path =
                            @BackupRootPath
                            + @bakFile
                            + @bakType
                            + '.bak'
                exec zsp_Backup_Database @dbName, @path, @result out
                select
                    GETDATE()                    as [DealTime]
                    , 'zsp_Backup_Database'        as [Operation]
                    , @dbname                    as [DataBaseName]
                    , @path                        as [BackupFilePath]
                    , @result                    as [Result]
            end
            else
            begin  -- except 周日 diff backup
                set @bakType = 'DIF'
                set @path =
                            @BackupRootPath
                            + @bakFile
                            + @bakType
                            + '.bak'
                exec zsp_Backup_Database_With_Differential @dbName, @path, @result out
                select
                    GETDATE()                                        as [DealTime]
                    , 'zsp_Backup_Database_With_Differential'        as [Operation]
                    , @dbname                                        as [DataBaseName]
                    , @path                                            as [BackupFilePath]
                    , @result                                        as [Result]
            end
        end
    end
    GO
    alter procedure [zsp_Backup_Database]
        @DataBaseName sysname = null
        , @Path varchar(max)
        , @Result int = 0 out
    as
    begin
        --exec zsp_Backup_Database default,'d:\dbbackup\OddWeek\Test\2\Test.2.23.LOG.bak'
        if rtrim(ltrim(isnull(@DataBaseName,''))) = ''
        begin
            set @DataBaseName = db_name()
        end
        backup database
                @DataBaseName
            TO DISK = @Path
                WITH
                    INIT
                    ,compression
        begin try
            restore verifyonly
                from disk =  @Path
        end try
        begin catch
            set @Result = ERROR_NUMBER()
            SELECT
                'restore verifyonly
                from disk = ''' + @Path + '''' as SQL
                , @Result AS ErrorNumber
                , ERROR_SEVERITY() AS ErrorSeverity
                , ERROR_STATE() AS ErrorState
                , ERROR_PROCEDURE() AS ErrorProcedure
                , ERROR_LINE() AS ErrorLine
                , ERROR_MESSAGE() AS ErrorMessage
        end catch
    end
    go
    alter procedure [zsp_Backup_Database_With_Differential]
        @DataBaseName sysname = null
        , @Path varchar(max)
        , @Result int = 0 out
    as
    begin
        --exec zsp_Backup_Database default, 'd:\dbbackup\OddWeek\Test\2\Test.2.23.LOG.bak'
        if rtrim(ltrim(isnull(@DataBaseName,''))) = ''
        begin
            set @DataBaseName = db_name()
        end
        backup database
                @DataBaseName
            TO DISK = @Path
                WITH
                    INIT
                    , Differential
                    , compression
        begin try
            restore verifyonly
                from disk = @Path
        end try
        begin catch
            set @Result = ERROR_NUMBER()
            SELECT
                'restore verifyonly
                from disk = ''' + @Path + '''' as SQL
                , @Result AS ErrorNumber
                , ERROR_SEVERITY() AS ErrorSeverity
                , ERROR_STATE() AS ErrorState
                , ERROR_PROCEDURE() AS ErrorProcedure
                , ERROR_LINE() AS ErrorLine
                , ERROR_MESSAGE() AS ErrorMessage
        end catch
    end
    go
    alter procedure [zsp_Backup_Log]
        @DataBaseName sysname = null
        , @Path varchar(max)
        , @Result int = 0 out
    as
    begin
        --exec zsp_Backup_Database default, 'd:\dbbackup\OddWeek\Test\2\Test.2.23.LOG.bak'
        if rtrim(ltrim(isnull(@DataBaseName,''))) = ''
        begin
            set @DataBaseName = db_name()
        end
        backup log
                @DataBaseName
            TO DISK = @Path
                WITH
                    INIT
                    , compression
        begin try
            restore verifyonly
                from disk = @Path
        end try
        begin catch
            set @Result = ERROR_NUMBER()
            SELECT
                'restore verifyonly
                from disk = ''' + @Path + '''' as SQL
                , @Result AS ErrorNumber
                , ERROR_SEVERITY() AS ErrorSeverity
                , ERROR_STATE() AS ErrorState
                , ERROR_PROCEDURE() AS ErrorProcedure
                , ERROR_LINE() AS ErrorLine
                , ERROR_MESSAGE() AS ErrorMessage
        end catch
    end
    
    
  • 相关阅读:
    wpf 文字动态动画效果
    c# 使用 Tchart控件之数据绑定
    Linq to sql学习之查询句法
    非常有用的查询所有SqlServer数据库字典的操作
    利用WPF建立自适应窗口大小布局的WinForm窗口
    SqlMethods
    wpf 炫彩动画效果简单实例
    SetBkMode与SetBkColor理解
    Windows的字体LOGFONT
    GetWindowRect和GetWindDC GetClientRect和GetDC 在标题栏输入文字
  • 原文地址:https://www.cnblogs.com/Microshaoft/p/2044263.html
Copyright © 2020-2023  润新知