sqlserver 数据库备份

目标：实现数据库定时备份和删除

效果图如下

1.数据备份

declare @filename varchar(255)
select @filename='E:\MES-BAK\MES'+left(replace(replace(REPLACE(convert(varchar(100), GETDATE(),120),':',''),'-',''),' ',''),12)+'.BAK'
backup DATABASE PersonDB to disk=@filename WITH NOFORMAT, NOINIT,  SKIP, REWIND, NOUNLOAD, COMPRESSION,STATS = 10
go

 

2.删除备份

DECLARE @date DATETIME 
--select @date= dateadd(minute,-30,GETDATE())
SELECT @date= getdate()-5
execute master.dbo.xp_delete_file 0,N'E:\MES-BAK',N'bak',@date,1
go

3.添加计划