27,579
社区成员
发帖
与我相关
我的任务
分享
/*
注意:由于用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
--数据库周1完整备份周2-7差异备份脚本
declare @name varchar(250)
declare @weekday varchar(2)
set @weekday = (select datepart(weekday,GETDATE()-1))
IF(@weekday ='1')
BEGIN
set @name='E:\DB\db_'+convert(varchar(50),getdate(),112)+'_auto.bak'
--数据库完整备份脚本
BACKUP DATABASE [DBTEST] TO
DISK = @name
WITH NOFORMAT, NOINIT,
NAME = N'完整 数据库 备份',
SKIP, NOREWIND, NOUNLOAD
END
Else
BEGIN
set @name = convert(varchar(50),(getdate() - datepart(weekday,GETDATE()-2)),112)
set @name='E:\DB\db_'+@name+'_auto.bak'
--数据库差异备份脚本
BACKUP DATABASE [H3_DBTEST] TO
DISK = @name
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
END