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
|