日期:2014-05-17 浏览次数:20511 次
/* 注意:由于用GUI备份会导致备份链中断,而作业无法实现“仅复制备份”,所以使用脚本备份 create by huangzj 20120510 */ --定义备份时间,精确到秒 DECLARE @date NVARCHAR(64) SELECT @date = SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 1, 4) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 6, 2) + '_' + SUBSTRING(CONVERT(VARCHAR(20), GETDATE(), 121), 9, 2) + '_' + CONVERT(CHAR(2), DATEPART(hh, GETDATE())) + CONVERT(CHAR(2), DATEPART(mi, GETDATE())) + CONVERT(CHAR(2), DATEPART(ss, GETDATE())) --定义要备份的数据库名 DECLARE @db NVARCHAR(20) SET @db = '' + 'HK_ERP_BaTa' + '' --定义备份文件的全名 DECLARE @bakname NVARCHAR(128) SELECT @bakname = @db + '_' + @date --定义备份存放路径 DECLARE @disk NVARCHAR(256) SELECT @disk = N'DataBak' + @bakname + '.bak' --定义备份描述 DECLARE @name NVARCHAR(128) SELECT @name = @db + '-完整 数据库 备份' --定义错误信息 DECLARE @error NVARCHAR(128) SELECT @error = '验证失败。找不到数据库“' + @db + '”的备份信息。' BACKUP DATABASE @db TO DISK = @disk WITH COPY_ONLY, NOFORMAT, NOINIT, NAME =@name, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM ; DECLARE @backupSetId AS INT SELECT @backupSetId = position FROM msdb..backupset WHERE database_name = @db AND backup_set_id = ( SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = @db ) IF @backupSetId IS NULL BEGIN RAISERROR(@error, 16, 1) END RESTORE VERIFYONLY FROM DISK = @disk WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO