MSSQL 自动备份数据库并自动删除老备份文件

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