E8-完善数据库备份的存储过程

起因

我们公司E8的数据用的是SQL Server 2012在我把从生产环境下复制来的数据库备份文件恢复到本机测试环境里时，被提示硬盘空间不足。备份文件3G多，硬盘剩余空间10G左右。于是我去生产环境看了一下数据库的物理文件，就是扩展名是mdf文件（以下简称数据库文件）和ldf文件（以下简称是日志文件）的那两个文件。不看不知道，一看下一跳，数据库文件文件3G左右，日志文件18G左右。

备份是用的存储过程，SQL Server代理每天定时执行这个存储过程，实现自动备份，存储过程的写法网上也有现成的案例，后面我也会把它放到代码里。至于SQL Server代理的设置方法不是本文的重点，需要具体操作方法的小伙伴请自行百度。

经过

先不管数据库文件，先来说说日志文件，网上都说它是个日志文件的大小，是随着数据库的执行会不断增大的。它主要用来支持事务回滚，差异备份的恢复等操作提供支持的。清理日志文件的方法有很多，目前我能熟练操作的有两种方法，第一种方法是分离数据库，删除日志文件，再附加数据库，用这种方法让DBMS去重建日志文件。第二种方法是用”收缩“功能去收缩日志文件，但要在收缩之前，把数据库的恢复模式，详细步骤不是本文的重点，需要具体操作方法的小伙伴可以自行百度。

思路是这样式儿的，我想每天在备份完数据库之后，清理一下日志文件。

由于备份是在存储过程中实现的，于是我就想，能不能把清理日志文件的操作也一并放到存储过程里。结合自己掌握的知识，上面说的第一种方法我实现不出来。第二种方法，在修改数据库的恢复模式和收缩数据库的操作步骤中，都是可以获取到相应的SQL语句的，倒是可以尝试一下。

在经过测试了完整备份了一个收缩过的日志文件的数据库，再进行还原，确认了日志文件在做完整备份的时候几乎没有影响之后，便尝试着修改存储过程实现以上目标。

结果

USE [ecology] GO /****** Object:  StoredProcedure [dbo].[BackupDB]    Script Date: 2024/3/6 9:54:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[BackupDB] AS  begin  declare  @filename nvarchar(100)--文件名  -- 备份数据库部份 begin set @filename='e:\ecologyBackup\ecology'+ CONVERT(varchar(100), GETDATE(), 23) --Sunday = 1、Saturday = 7 print @filename BACKUP DATABASE ecology TO DISK = @filename WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT  -- 备份数据库部份 end -- 尝试在存储过程中收缩数据库开始 20240306 begin ALTER DATABASE [ecology] SET RECOVERY SIMPLE WITH NO_WAIT --把恢复模式调成简单 DBCC SHRINKFILE (N'ecology_log' , 0, TRUNCATEONLY) -- 收缩日志文件 DBCC SHRINKFILE (N'ecology' , 0, TRUNCATEONLY) --收缩数据库文件 ALTER DATABASE [ecology] SET RECOVERY FULL WITH NO_WAIT --把恢复模式调成完整 -- 尝试在存储过程中收缩数据库开始 20240306 end end GO

其中，以前我没注意到的地方是，在存储过程体里，不能有USE语句和GO关键字，在SSMS里操作调整恢复模式和收缩数据库文件和收缩日志文件这些步骤中，生成的SQL语句中，是包含USE语句和GO关键字的，去掉就好了。虽然在调整恢复模式的步骤中，写的是USE [master]，没关系，同样删除USE语句就好了。

这种方法适合SQL Server的数据库。

喜欢的小伙伴们请关注、点赞、评论。大家的鼓励是我持续创作的动力。感谢！