1、备份数据库并自动删除老备份SQL语句
创建sql文件,内容如下
exec expressmaint
@database = '数据库名称',
@optype = 'DB',
@backupfldr = 'D:\databak', --备份目录
@reportfldr = 'D:\databak\reports', --报表目录
@verify = 1,
@dbretainunit = 'weeks', --备份保留周
@dbretainval = 1, --保留周数
@rptretainunit = 'weeks', --报表保留周
@rptretainval = 1, --保留周数
@report = 1
这里的weeks可以改成days代表需要保留备份的天数
注意:这里的@backupfldr @reportfldr 的目录需要手动创建,否则会报错。
2、开启expressmaint命令需要的权限
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO
3、创建windows计划任务
1)win键+R打开运行,输入Taskschd.msc 打开任务计划程序
2)点操作->创建任务
选择触发器创建一个执行计划
我这里选择的是每天一次,点确认,接着选操作
程序或脚本:填写SQLCMD.EXE 的路径,如:"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
添加参数:刚刚创建的sql文件的路径,如:-S .\ -i"D:\databak\databak.sql"
点确认输入你的管理员密码
可能的错误:
无法删除老的备份:
打开Microsoft SQL Server Management Studio 验证SQL语句报错:
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.
修复方法开启 Ole Automation Procedures权限:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
继续验证报错:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
修复方式开启xp_cmdshell权限:
sp_configure 'show advanced options', '1'
RECONFIGURE
GO
sp_configure 'xp_cmdshell', '1'
RECONFIGURE
GO