2种方法使SQL Server定期自动备份、自动删除过期备份文件并修改

因为特殊原因，今天检查了一下服务器备份文件，结果发现，原来备份文件只保留了三天，而我需要7天的备份文件，所以重新梳理了一下备份方法。具体有2种方法可以实现，第一种使用管理的维护计划向导创建，第二种使用SQL SERVER作业创建，

第一种方式：

要求是每日凌晨2点进行自动备份，删除一个月之前的备份数据库。

这是使用sql server 代理服务的作业功能实现的，所以

前提条件：

1）需要启动SQL SERVER 代理服务

2）备份的数据库的恢复模式是：完整

选择备份数据库，右键【属性】，选择页-->选项--恢复模式，设置恢复模式为完整（如已经是完整模式，则不需要操作）

1. 自动备份和删除数据库操作步骤

首先需要启动SQL Server Management Studio登录数据库

1、启动SQL SERVER 代理服务，选中SQL SERVER 代理，右键【启动】，开启服务

2、找到管理-->维护计划，选中维护计划，右键【维护计划向导】

2.1) 录入维护计划名称及说明

2.2) 设置维护计划的执行频率及时间点

2.3）选择下一步

2.4）选择执行任务类型，备份数据库（完整）和“清除维护”任务

2.5)选择执行任务顺序

2.6) 定义备份数据库（完整）--常规

2.7) 定义备份数据库（完整）--目标

2.8) 定义备份数据库（完整）--选项

2.9）定义“清除维护”任务

2.10）维护计划操作报告保存

2.11）维护计划配置查看，点击【完成】

2.12）维护计划执行

3、检查是否维护计划创建成功

3.1）管理-->维护计划，是否存在创建维护计划（备份CPAP数据库）

3.2) SQL Server代理--> 作业，是否存在维护计划的作业（备份CPAP数据库.Subplan_1）

4、检查是否有自动备份数据库

4.1）自动备份的数据库（这里我设置的3分钟执行一次，看具体业务情况）

4.2）自动备份的数据库报告（这里我设置的3分钟执行一次，看具体业务情况）

5、修改备份计划

维护计划右键“修改”可查看计划任务，右键可以再次设置相关参数。

5.1）在"维护计划"下我们新建的"自动备份数据库"上右键，再选择"修改"，如下图所示：

5.2）在打开的新标签页中，可以修改备份行为、备份的时间、备份日志存放位置等，如下图所示：

第二种方式：

使用SQL Server Management Studio 创建数据库备份作业

SQL Server 作业无非就是按照规定的时间执行指定的脚本，这里介绍如何用SSMS（SQL Sever 2008）创建作业备份数据库。

1. 假设在创建作业之前你所要备份的数据库已经存在；其次，你已经会启动SQL Sever 代理（一般是关闭的）开启代理服务，在服务里面修改启动类型改为自动。

在桌面找到计算机，右键点击【管理】，找到【服务和应用程序】，点击【服务】，找到Sql Server 代理(MSQLSERVER)，点击右键，启动，如下图

启动后，SqlServer代理就可用了

（1）创建SQL Server代理作业

（1.1）新建作业，输出常规信息

如上图：输入作业名称（如：BackupJobTest）,这里所有者和类别都是默认的，输入说明（就跟写代码要写注释一样，利人利己）

（1.2）设置作业执行步骤

点击左边“选择页”中的步骤属性选项卡，点击“新建”按钮

作业步骤属性窗口中，类型选择（T-SQL），选择要备份的数据库，并粘贴上调试好的SQL 备份脚本,由于我也是第一次写，贴一段示例在这里，读者可以自行参考修改：

--将SQL脚本赋值给变量

declare @SqlBackupDataBase as nvarchar(1000)

set @SqlBackupDataBase=N'BACKUP DATABASE YourDataBaseName TO DISK = ''E:\DBBackup\YourDataBaseName-'+

CONVERT(varchar(11),GETDATE(),112)+REPLACE(CONVERT(varchar(12),GETDATE(),108),':','')+'.bak'''

--备份文件格式：YourDataBaseName-20140626233410.bak

print @SqlBackupDataBase --打印出来（为了方便调试，可省略）

exec sp_executesql @SqlBackupDataBase --调用系统存储过程，执行SQL

注：CONVERT()函数的第三个参数是时间样式ID；用REPLACE()函数替换掉了时间中的冒号（因为Windows中文件名不能包含该字符）

部分要执行的命令（局域网）：

execmaster..xp_cmdshell 'net use Y: \\计算机名称（ip地址）\共享文件夹 "计算机密码" /user:ip地址\计算机用户'

declare@sql varchar(500)

select@sql='\\计算机名称（ip地址）\共享文件夹\数据库备份名称.bak'

backupdatabase 数据库名称to disk=@sql

 

删除5天前的备份数据库

select@sql='del'+'\\192.168.0.22\BookDB\BooKDB'+convert(varchar(10),dateadd(day,-5,getdate()),112)+substring(convert(varchar(10),dateadd(day,-5,getdate()),108),1,2) +'.bak'

execmaster..xp_cmdshell @sql

 

-------------------------------完整示例------------------------------

局域网

execmaster..xp_cmdshell 'net use Y: \\192.168.0.22\BookDB "xn56.com"/user:192.168.0.22\administartor'

declare @sqlvarchar(500)

select@sql='\\192.168.0.22\BookDB\BookDB'+convert(varchar(10),getdate(),112) +substring(convert(varchar(10),getdate(),108),1,2) +'.bak'

backupdatabase BookDB to disk=@sql

 

select@sql='del'+'\\192.168.0.22\BookDB\BooKDB'+convert(varchar(10),dateadd(day,-5,getdate()),112)+substring(convert(varchar(10),dateadd(day,-5,getdate()),108),1,2) +'.bak'

execmaster..xp_cmdshell @sql

 

本地计算机

use master

go

declare @sql varchar(500)

select @sql='D:\data\backup\wms'+'_db_'+convert(varchar(10),getdate(),112) +'.bak'

backup database wms to disk=@sql with init

go

declare @sql varchar(500)

select @sql='del '+'D:\data\backup\wms'+'_db_'+convert(varchar(10),dateadd(day,-7,getdate()),112) +'.bak'

exec master..xp_cmdshell @sql

go

时间格式及ID对照表参考：W3school

（1.3）设置作业执行计划

在计划选项卡中，点击“新建”按钮新建计划：

设置完成后，点击”确定“按钮，另外三个属性“警报”、“通知”、“目标”如果需要可以自行设置，这里不再讲述。

完成计划设置后，点击上级的“确定”按钮，作业就创建完成了。

（2）手动验证作业执行的正确性

右键单击作业节点下刚建立的作业（图中的“BackupJobTest”），选择‘作业开始步骤(T)’

执行结果如下，表示成功：

点击“查看历史记录(V)”,可查看日志：

 

资源下载

此资源仅限注册用户下载，请先登录

免责声明：

根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理，通过安装、显示、传输或者存储软件等方式使用软件的，可以不经软件著作权人许可，不向其支付报酬。”您需知晓本站所有内容资源均来源于网络，仅供本站会员用户交流学习与研究使用，版权归属原版权方所有，版权争议与本站无关，用户本人下载后不能用作商业或非法用途，禁止分享或传播。需在24个小时之内从您的电脑中彻底删除上述内容，否则后果均由用户承担责任；如果您访问和下载此文件，表示您同意只将此文件用于参考、学习而非其他用途，否则一切后果请您自行承担 。警告：本站仅服务站内会员用户，禁止在中国大陆用户下载使用、分享或传播，违者后果自负。同意本条款你方可捐赠购买下载本站资源，不同意请离开。如果您喜欢该程序，请支持正版软件，购买注册，得到更好的正版服务。

服务条款：

本站为个人博客非盈利性站点，所有软件信息均来自网络，所有资源仅供学习参考研究目的，并不贩卖软件，不存在任何商业目的及用途，网站会员捐赠是您喜欢本站而产生的赞助支持行为，购买充值 即代表你对本站的无偿捐助，非购买本站资源。仅为维持服务器的开支与维护，全凭自愿无任何强求。

为防止盗链，本站所有资源均需登陆后下载，即使是免费的。如果链接失效或者需要安装密码，可通过右上方菜单“私信本站”联系管理员补链或者提供安装密码；注意安装密码仅免费提供给本站VIP会员和捐赠用户。本站下载头条极速版并关注，还可以领取现金红包哦！

如需解压密码，关注官方微信号“ 心语家园“或扫描下面的微信公众号二维码，发送解压密码获取。默认解压密码即最新密码，如密码无效，可尝试其他密码。