sqlserver2005 自动备份脚本

 --清空日志
 USE Master
GO
backup log [数据库名称] with no_log
DUMP TRANSACTION [数据库名称] WITH NO_LOG
DBCC SHRINKFILE (2)
--DBCC SHRINKFILE (1)
GO

--完整备份,每天一次
USE Master
GO
declare @str varchar(100)
set @str='D:\ChaoXingBaiLianPortal\databasebk\FullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.bak'
--print @str
BACKUP DATABASE 备份数据库名称 TO DISK=@str
WITH RETAINDAYS=15,NOFORMAT,NOINIT,
NAME=N'完整备份',SKIP,NOREWIND,
NOUNLOAD,STATS=10
GO

--删除15天之前的备份文件,每天两次
declare @str varchar(100),@dir varchar(100),@fileName varchar(30)
set @dir='del D:\ChaoXingBaiLianPortal\databasebk\'
set @filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),' ',''),':',''),8)
set @str=@dir+'FullBak'+@filename+'*.bak'
--print @str
exec xp_cmdshell @str
GO

转载于:https://www.cnblogs.com/weipeng/archive/2012/11/16/2773261.html