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 |