开启权限

 

 (注：因为xp_cmdshell是高级选项，所以这里启动xp_cmdshell，需要先将 show advanced option 设置为 1，便可显示高级配置选项。

EXEC sp_configure 'show advanced options', 1    
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure'xp_cmdshell',1      
GO

 

 

 

备份数据库  

backup命令

set @sql1='backup database '+@Store +' to disk='''+@backupfile1+''' with retaindays='+convert(varchar(10),@retaindays)

删除数据库

del命令

set @dir1='del D:\databak\'

删除N天以前的

(replace(replace(replace(convert(varchar,getdate()-@retaindays,20),'-',''),' ',''),':',''),10)+

完整代码：

EXEC sp_configure 'show advanced options', 1    
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure'xp_cmdshell',1      
GO
RECONFIGURE  
GO
declare @sql1 varchar(4000)
 
declare @backupfile1 varchar(2000) 
 
declare @retaindays int 
declare @Store varchar(2000) 
 
declare @str varchar(100),
@dir1 varchar(100),
 
@bakfile1 varchar(30)
 
set @Store='msddata'--数据库名
 
set @retaindays=12      --要保留备份的天数
 
set @backupfile1='D:\databak\'+@Store+ '_bak_'+ replace(replace(replace(convert(varchar,getdate(),20),'-',''),' ',''),':','')+'.BAK'  
 
set @sql1='backup database '+@Store +' to disk='''+@backupfile1+''' with retaindays='+convert(varchar(10),@retaindays)
exec (@sql1) 
 
--自动删除备份文件
set @dir1='del D:\databak\'
set @bakfile1=left(replace(replace(replace(convert(varchar,getdate()-@retaindays,20),'-',''),' ',''),':',''),10)+'*.BAK'
set @str=@dir1 + @Store + '_bak_'  + @bakfile1 
exec  xp_cmdshell  @str
 
EXEC sp_configure'xp_cmdshell',0   
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0   
GO
RECONFIGURE WITH OVERRIDE