ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 备份数据库脚本

备份数据库脚本

原创 Linux操作系统 作者:hwtong 时间:2011-06-24 14:52:07 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 [demo] 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-700667/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-06

  • 博文量
    116
  • 访问量
    286237