sql server2008数据库备份作业

今天药忘吃喽~ 2022-08-02 14:38 265阅读 0赞

/***\*\*\*\*\*\*\*\*\****完整备份作业***\*\*\*\*\*\*\*\*\****/  
–完整备份,每周一次

USE Master
    GO
    declare @str varchar(100)
    set @str='D:\DBtext\jgj\DBABak\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak' BACKUP DATABASE [Master] TO DISK=@str WITH RETAINDAYS=15,NOFORMAT,NOINIT, NAME=N'Demo完整备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO

/***\*\*\*\*\*\*\*\*\****差异备份作业***\*\*\*\*\*\*\*\*\****/  
–截断日志

USE Master
    GO
    BACKUP LOG Demo  WITH NO_LOG
    GO
    --收缩日志文件
    USE Demo
    GO
    DBCC SHRINKFILE (N'Demo_log',0,TRUNCATEONLY)
    GO
    --差异备份,每天一次
    USE Master
    GO
    declare @str varchar(100)
    set @str='D:\DBtext\jgj\DBABak\DiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.diff' BACKUP DATABASE [Demo] TO DISK=@str WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT, NAME=N'Demo差异备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO

/***\*\*\*\*\*\*\*\****日志备份作业***\*\*\*\*\*\*\*\*\****/  
–日志备份,每小时一次

USE Demo
    GO
    declare @str varchar(100)
    set @str='D:\DBtext\jgj\DBABak\logbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.trn' BACKUP LOG [Demo] TO DISK=@str WITH RETAINDAYS=3,NOFORMAT,NOINIT, NAME=N'Demo日志备份',SKIP,NOREWIND, NOUNLOAD,STATS=10 GO

–删除过期的备份文件,每天两次

declare @str varchar(100),@dir varchar(100),@fileName varchar(30)
    set @dir='del D:\DBtext\jgj\DBABak\' set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8) set @str=@dir+'fullbak'+@filename+'*.bak' exec xp_cmdshell @str set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) set @str=@dir+'diffbak'+@filename+'*.diff' exec xp_cmdshell @str set @filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),' ',''),':',''),8) set @str=@dir+'logbak'+@filename+'*.trn' exec xp_cmdshell @str