sqlserver定时差异备份_关于sqlserver2005异地自动备份

/*脚本说明：数据库备份

要点：

1 备份文件名规则：库名_backup_yyyymmdd

2 全备后缀为 bak 差异备份后缀为 dif 日志备份后缀为 trn

2 在指定路径生成指定类型备份，备份细节由初始参数决定

注意：

在try catch中只能捕获最后一条错误日志，要得到详细的错误原因，需要去掉try捕获*/

--定义参数

DECLARE @DatabaseName VARCHAR(100)DECLARE @Path VARCHAR(200)DECLARE @PathfileName VARCHAR(200)DECLARE @BackupName VARCHAR(200) --备份名

DECLARE @NowDateTime VARCHAR(100) --当前日期时间

DECLARE @v1 VARCHAR(500) --参数1

DECLARE @Err VARCHAR(500)DECLARE @mail VARCHAR(500)DECLARE @mailUser VARCHAR(500)DECLARE @backupSetId AS INT

DECLARE @Date VARCHAR(8) --yyyymmdd日期

DECLARE @command VARCHAR(50)DECLARE @backType VARCHAR(50) --full 全备 dif 差异备 log 日志备

DECLARE @IsdateDir INT --是否建立日志格式之文件夹

DECLARE @log_level INT

--参数设定

SET @DatabaseName = 'ZXFinance_Test3'

SET @Path = 'E:\DBBackup\'

SET @backType='full' --full 全备 dif 差异备 log 日志备

SET @IsdateDir=1 --1 path中建立日期文件夹 0 保存于path中的根目录

SET @log_level=1 --0 异常邮件通知 ，1全备通知，2备份通知

SET @mailUser='zhao' --操作员账号

--生成当前时间戳

SELECT @NowDateTime = STUFF(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 120), '-', ''), ' ', ''), ':', ''),9,0,'_')SELECT @Date=CONVERT(VARCHAR(17), GETDATE(), 112)--路径结尾补全

IF SUBSTRING(@Path,len(@Path),1)<>'\' SET @Path=@Path+'\'

--建立子文件夹

IF @IsdateDir=1

BEGIN

SET @Path=@Path+@Date+'\'

SET @command='mkdir'+@Path

EXEC xp_cmdshell @command

END

--防止文件夹不存在报错

IF @IsdateDir=0

BEGIN

SET @Path=@Path

SET @command='mkdir'+@Path

EXEC xp_cmdshell @command

END

--生成文件名

SET @BackupName = @DatabaseName + '_backup_'+@backType+'_'+@NowDateTime

SELECT @PathfileName = @Path + @DatabaseName + '_backup_'+@backType+'_'+@Date+ (CASE @backType WHEN 'dif' THEN '.dif'

WHEN 'log'THEN '.trn'

WHEN 'full'THEN '.bak'

end)BEGINTRYif @backType='dif'

begin

--差异备份

BACKUP DATABASE @DatabaseName TO DISK = @PathfileName

WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = @BackupName,

SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS= 10

END

IF @backType='full'

BEGIN

--全备份

BACKUP DATABASE @DatabaseName TO DISK = @PathfileName

WITH NOFORMAT, NOINIT, NAME = @BackupName,

SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS= 10

END

IF @backType='log'

BEGIN

--日志备份

BACKUP LOG @DatabaseName TO DISK = @PathfileName

WITH NOFORMAT, NOINIT, NAME = @BackupName,

SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS= 10

END

--提取备份状态

SELECT @backupSetId =positionFROMmsdb..backupsetWHERE database_name = @DatabaseName

AND backup_set_id = ( SELECT MAX(backup_set_id)FROMmsdb..backupsetWHERE database_name = @DatabaseName)IF @backupSetId IS NULL

BEGIN

SET @V1 = '验证失败。找不到数据库' + @DatabaseName + '的备份信息。'

RAISERROR(@V1, 16, 1)EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser,@subject = N'备份异常', @body = @V1

END

--备份校验

RESTORE VERIFYONLY FROM DISK = @PathfileName

WITH FILE = @backupSetId, NOUNLOAD, NOREWIND--备份完成通知管理员

SET @mail = '备份完成，备份文件路径:' + @PathfileName

IF (@log_level=1 AND @backType IN('full') ) OR (@log_level=2 AND @backType IN( 'full','dif','log'))EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser, @subject = N'备份完成', @body = @mail

ENDTRYBEGINCATCHSELECT ERROR_NUMBER() ASErrorNumber ,

ERROR_SEVERITY()ASErrorSeverity ,

ERROR_STATE()ASErrorState ,

ERROR_PROCEDURE()ASErrorProcedure ,

ERROR_LINE()ASErrorLine ,

ERROR_MESSAGE()ASErrorMessage ;SET @Err = @backType+'备份异常！错误原因:' +ERROR_MESSAGE()EXECUTE msdb.dbo.sp_notify_operator @name = @mailUser, @subject = '备份异常', @body = @Err

END CATCH