1
点击"SQL Server 外围应用配置器", 打开"功能的外围应用配置器"在里面启用xp_cmdshell,重启SQL服务,使其生效
2
在windows目录下的system32目录里新建"del_oldfiles.vbs" 内容如下:
strDrive = left(WScript.Arguments(0), 2)
strPath = replace(right(WScript.Arguments(0), len(WScript.Arguments(0))-2),"\","\\") & "\\"
strExtension = WScript.Arguments(1)
intDays = WScript.Arguments(2)
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2")
Set objConvertedDate = CreateObject("WbemScripting.SWbemDateTime")
Set colFiles = objWMIService.ExecQuery("select * from CIM_DataFile where drive='" &_
strDrive & "' and path='" & strPath & "'")
For Each objFile in colFiles
objConvertedDate.Value = objFile.CreationDate
dtCreationDate = objConvertedDate.GetVarDate(LOCAL_TIME)
If DateAdd("d", intDays, dtCreationDate) < Now and objFile.Extension = strExtension Then
' Wscript.Echo objFile.Name & ", " & DateDiff("d", dtCreationDate, Now)
objFile.delete()
End If
Next
3
在维护计划里新建"备份数据库",跟正常的应用开发拉控件一样,不在此说明!设置好
4
在SQL server 代理内新建作业"删除备份文件"其步骤内容如下:(代码内的F:\backup替换成自己计划维护设定的目录)
exec xp_cmdshell 'cscript.exe del_oldfiles.vbs F:\backup bak 3'
此方法取于http://bbs.csdn.net/topics/340135388 xman_78tom
用法:del_oldfiles.vbs fullpath ext days
fullpath -- 文件所在的完全路径;ext -- 文件的扩展名;days -- 早于多少天
在 SQL Server 中,可以通过 exec xp_cmdshell 'cscript.exe del_oldfiles.vbs d:\backup rar 3' 调用。
5
然后在维护计划内加入"执行SQL server代理作业"任务!