快速开发平台企业版教程：SQL Server数据库自动备份及控制日志增长

在本教程中，主要向大家讲解企业版平台使用过程中，常见但很重要的两个问题：

• 怎么实现 SQL Server 数据库的自动备份
• 怎么解决 SQL Server 数据库的无限增大

关于 SQL Server 数据库的自动备份

基于数据库的管理软件，最有价值的东西是什么？当然是数据！而备份是防止因为操作失误、系统故障造成数据损坏、丢失的最有效容灾手段，没有之一！

在 SQL Server 为我们提供了强大灵活的自动备份功能。在本教程中将为大家讲解如何实现每天3点自动备份。为什么要放在半夜3点？当然是为了避免影响正常使用啦。

关于 SQL Server 数据库日志的无限增大

    每个 SQL Server 数据库会有2个文件，一个数据文件(.mdf)，一个日志文件(.ldf)。使用一段时间之后，我们会发现日志文件会增长的非常快！可能数据库不到100M，而日志文件却超过了10G！再加上每日的备份，服务器上的硬盘空间很快就被耗尽了！

使用 SQL Server 数据库的 “任务 -> 收缩” 功能，无效，因为该操作主要是释放未使用的空间；限制日志文件大小，依然无效，甚至当日志写满后，数据库都不能正常写入数据了。

将这2个问题结合起来，解决的思路是，通过维护计划，定期执行以下操作：

1. 备份数据库：任务对整个数据库的操作都是有一定危险性的，所以一定得是很备份。
2. 截断日志：只保留指定大小的日志，超出的进行截断，这个是解决日志无限增大的关键步骤。
3. 收缩数据库。释放未使用的空间。
4. 重新组织索引：因为各种故障可能造成索引异常，而索引异常会导致一些让人摸不着头脑的奇怪的问题，而重新组织索引，则可以有效解决。

注意：在接下来的步骤中，如果有点击右键通过快捷菜单打开某个界面的操作，绝大部分都可以直接使用双击的方式来打开，本文中为了大家观看时更直观明了，统一使用右键快捷菜单的方式。

SQL Server 数据库自动备份同时控制日志大小的详细操作步骤

1. 登录服务器，启动 SQL Server 管理工具 SSMS（Microsoft SQL Server Management Studio）。一般建议用Windows身份验证登录，避免后面进行一些操作时，由于权限不足而造成问题。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311455269682.png

   
   

2. 进入SSMS后，在对象资源管理器窗格中，展开节点【(local)】->【管理】，然后在【维护计划】节点上点击右键，在弹出的快捷菜单中选择【新建维护计划(N)...】菜单项。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311456227572.png

   
   

3. 在弹出的新建维护计划对话框中，输入新的名称“每天3点自动备份数据库”，然后点【确定】按钮。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311456494482.png

   
   

4. 接下来就自动进入到维护计划设计界面了，注意工具箱窗格一般会默认打开，如果没有默认打开，可以通过菜单“视图(V) -> 工具箱(X)”（或快捷键Ctrl+Alt+X）来打开。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311457129832.png

   http://www.accessoft.com/article_attached/2019/image/201905/201905311457401122.png

   
   

5. 从【工具箱】中选中【“备份数据库”任务】，按住鼠标左键不放，将其拖放到右侧空白处松开左键，此时会自动创建任务。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311458002663.png

   
   

6. 在新添加的【“备份数据库”任务】上点击右键，在弹出的快捷菜单中选择【编辑(E)...】菜单项。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311458328363.png

   
   

7. 在【“备份数据库”任务】明细界面，点击【数据库(D)】组合框，注意这里的组合框和平时的不太一样，它不只是一个简单的值列表，而是一个弹出的对话框。在其中根据需要选择数据库，这里我们用来做演示的数据库名称是“BLOG”，因此就只选择这一个数据库即可，然后再点【确定】按钮返回。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311459066717.png

   
   

8. 接着在【“备份数据库”任务】明细界面，进行以下操作：

   • 选中【为每个数据库创建子目录(U):】复选框。
   • 【文件夹(L):】输入框中修改为“D:\MSSQL_DB_BACKUP”。这里默认是 SQL Server 安装目录下的子目录，一般是在操作系统所在的 C 盘中。为了数据库的安全和方便管理，这里我们最好换一个其它文件夹（该文件夹不需要事先创建好，备份时如果不存在会自动创建的）。
   • 选中【验证备份完整性(Y)】复选框。
   • 【设置备份压缩(M):】选择“压缩备份”。
   • 点【确定】按钮完成任务的设置。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311459404998.png

   
   

9. 参照第5步的操作，通过从工具箱中拖放的方式，添加一个【“执行 T-SQL 语句”任务】。

   http://www.accessoft.com/article_attached/2019/image/201905/201905311500418600.png

   
   

10. 在新添加的【“执行 T-SQL 语句”任务】上点击右键，在弹出的快捷菜单中选择【编辑(E)...】菜单项。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311501088305.png

    
    

11. 在【执行 T-SQL 语句”任务】明细界面，复制以下代码粘贴到【T-SQL 语句(T):】输入框，再点【确定】按钮完成任务的设置。

    --将数据库恢复模式改为“简单”
    USE [master]
    GO
    Alter DATABASE BLOG SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    Alter DATABASE BLOG SET RECOVERY SIMPLE
    GO
    
    --收缩日志文件为10MB大小
    USE BLOG
    GO
    DBCC SHRINKFILE (N'BLOG_LOG', 10, TRUNCATEONLY)
    GO
    
    --将数据库恢复模式恢复为“完整”
    USE [master]
    GO
    Alter DATABASE BLOG SET RECOVERY FULL WITH NO_WAIT
    GO
    Alter DATABASE BLOG SET RECOVERY FULL
    GO
    

    http://www.accessoft.com/article_attached/2019/image/201905/201905311501419079.png

    
    

12. 参照第5步的操作，通过从工具箱中拖放的方式，添加一个【“收缩数据库”任务】。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311502158248.png

    
    

13. 在新添加的【“收缩数据库”任务】上点击右键，在弹出的快捷菜单中选择【编辑(E)...】菜单项。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311502389147.png

    
    

14. 在【“收缩数据库”任务】明细界面，点【数据库(D)】组合框，选择要备份的BLOG数据库，然后点【确定】按钮返回到任务明细界面，再点【确定】按钮完成任务的设置。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311503171775.png

    
    

15. 参照第5步的操作，通过从工具箱中拖放的方式，添加一个【“重新组织索引”任务】。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311504015415.png

    
    

16. 在新添加的【“重新组织索引”任务】上点击右键，在弹出的快捷菜单中选择【编辑(E)...】菜单项。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311504280550.png

    
    

17. 在【“重新组织索引”任务】明细界面，点【数据库(D)】组合框，选择要备份的BLOG数据库，然后点【确定】按钮返回到任务明细界面，再点【确定】按钮完成任务的设置。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311504595066.png

    
    

18. 由于这里我们总共添加了4个任务，接下来还需要指定这4个任务的执行顺序。点击选中【“备份数据库”任务】，这时它周围会出现较粗的虚框，并且下面会出现一个箭头，用鼠标指针将这个箭头拖动到【“执行 T-SQL 语句”任务】上，将这两个任务连接到一起，拖动过程中箭头会变成虚线，拖动完成后箭头会将这2个任务矩形连接到一起。
    这里的最后顺序应该是：【“备份数据库”任务】 --> 【“执行 T-SQL 语句”任务】 --> 【“收缩数据库”任务】 --> 【“重新组织索引”任务】

    http://www.accessoft.com/article_attached/2019/image/201905/201905311506146074.png

    http://www.accessoft.com/article_attached/2019/image/201905/201905311506142988.png

    http://www.accessoft.com/article_attached/2019/image/201905/201905311506144933.png

    http://www.accessoft.com/article_attached/2019/image/201905/201905311506142929.png

    
    

19. 维护计划的任务及执行步骤我们已经添加完成，接下来需要指定何时来执行计划。点击上方第1个子计划（默认名称是“Subplan_1”）右侧的日历图标按钮，打开作业计划属性界面。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311507134339.png

    
    

20. 在打开的【作业计划属性】界面中，将【频率】-【执行(C):】设为每天，再将【每天频率】 - 【执行一次，时间为(A):】设为 03:00，再点【确定】按钮完成设置。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311508192266.png

    
    

21. 至此整个作业计划设置完成，点击工具栏中的【保存】按钮（或按 Ctrl+S保存），然后关闭作业计划设计界面。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311508541509.png

    
    

22. “每天3点自动备份数据库”这个维护计划创建完成，我们就能在资源管理器窗格的“(local) -> 管理 -> 维护计划”下面看到它了。到这里还没完，我们还需要执行一下看看是否能正常执行。

    http://www.accessoft.com/article_attached/2019/image/201905/201905311554040969.png

    
    

23. 最后执行状态显示为成功，那么就大功造成啦！

    http://www.accessoft.com/article_attached/2019/image/201905/201905311555305259.png

    
    

可能遇到的相关问题解决办法

事情不可能总是一帆风顺，在这这个过程中，可能遇到一些问题，怎么办？解决它！

执行维护计划时，可能会出现错误。点击对话框中消息列中对应的超链接。

http://www.accessoft.com/article_attached/2019/image/201905/201905311603388992.png

这错误消息框看得人一头雾水。

http://www.accessoft.com/article_attached/2019/image/201905/201905311605165795.png

怎么办？别慌，我们去看看错误日志。在对象资源管理器窗格中展开节点“(local) - SQL Server 代理 - 错误日志”，双击打开“当前”日志。

http://www.accessoft.com/article_attached/2019/image/201905/201905311606387203.png

发现日志中有这样的错误消息：无法获取windows用户的信息。这时我们就有谱了，因为我们是用Windows身份验证登录的，那么问题多半是出在以这个用户的身份执行维护计划没有相应的权限上面。

http://www.accessoft.com/article_attached/2019/image/201905/201905311609468121.png

维护计划中好像没有地方指定以什么用户身份来执行怎么办呢？别急，其实维护计划中，包含了一个个子计划，而这些子计划，其实就是一个个的作业。

在对象资源管理器窗格中展开节点“(local) - SQL Server 代理 - 作业”，找到我们刚才创建的维护计划对应的作业“每天3点自动备份数据库.Subplan_1”，双击该作业节点打开【作业属性】界面，我们可以看到所有者即是刚才错误提示信息中的Windows用户名。

http://www.accessoft.com/article_attached/2019/image/201905/201905311622561708.png

我们将其改为 SQL Server 的默认 sa 用户，确定后，再尝试去执行维护计划。

http://www.accessoft.com/article_attached/2019/image/201905/201905311626467926.png

没有问题了！

http://www.accessoft.com/article_attached/2019/image/201905/201905311555305259.png

http://www.accessoft.com/article_attached/2019/image/201902/201902281121065954.jpg