[译文] 使用 SQL Server 代理编写基本备份脚本

最满意的一餐是你自己做的，数据库备份工作也不例外。

许多 DBA 使用维护计划作为维护其 SQL Server 数据库的“一站式服务点”。毕竟，他们进行备份、完整性检查、索引维护……您还想要什么？

但问题的真相是，维护计划很像快餐：是的，您掌握了基础知识，但是如果您的环境中有更多“基础知识”无法涵盖的特定需求，该怎么办？您收到的 SQL Server 相当于一个油腻的、过度劳累的 16 岁孩子，这让您感到恶心，因为他们无论如何都将“基础知识”扔到了您的包里。

您只有两个选择：使用第三方工具备份数据库，或者使用 SQL Server 代理设置自己的备份作业。今天，我们将讨论后者。

“但是 SQL Server 代理需要这些叫做‘脚本’的东西！我要去哪里弄这些？”

那里有很多预制的维护脚本（Ola Hallengren 是最著名和强烈推荐的脚本之一），但我们不是在这里谈论这些。我们在这里制作我们自己的自制备份解决方案。

我即将提供的脚本是一个起点。它们绝不是为了最大性能（或任何其他 DBA 流行语）而完全优化的；这些只是一个例子，说明即使是初级或偶然的 DBA 也可以使用基本的 T-SQL 和编程逻辑（我使用 WHILE 循环而不是游标，看在 Pete 的份上！）将自动备份脚本组合在一起。当然，考虑到不同的数据库可能有不同的备份需求，您的里程可能会有所不同——这些只是用于完整备份和日志备份的脚本，是备份领域的最低要求。有很大的定制空间，尤其是改进空间，但在大多数情况下，这些应该可以帮助您开始。

/*
Name: Basic full backup script
Author: Faryl O'Neil Hoover - XTIVIA
Date: 2/28/2021
Can be used in a SQL Server Agent job that runs daily, at minimum.
*/

-- Variables

DECLARE @DBCount int 

SELECT @DBCount = count(*) from sys.databases;

DECLARE @myCount int = 0;

DECLARE @dbName nvarchar(max);

DECLARE @SQL nvarchar(max);


WHILE (@myCount <= @DBcount)		-- Basic WHILE loop cycling through databases on instance
BEGIN
	IF EXISTS			-- Just in case databases are skipped (like tempdb, since it can't be backed up)
	(SELECT name FROM sys.databases 
	WHERE database_id = @myCount 
	AND name != 'tempdb'		-- Can't back up tempdb
	AND state = 0				-- Make sure databases are online
	AND is_read_only = 0)		-- Make sure databases aren't read-only
	BEGIN
		SELECT @dbName = name FROM sys.databases WHERE database_id = @myCount;		-- Grab database name to plug into backup script

		SET @SQL = 'BACKUP DATABASE ' + @dbname + 	-- Database backup statement
		' TO DISK = ''' + @dbName + '_' + 			-- Name is automatically plugged in
		CONVERT(char(8), getdate(), 112) + 			-- Date is automatically generated from GETDATE()
		'.BAK'' WITH COMPRESSION, CHECKSUM, STATS = 10';

		PRINT @SQL;		-- For general debugging

		EXEC sp_executesql @SQL;		-- Backup statement is executed
	END

	SET @myCount = @mycount + 1		-- Increment the counter
END



/*
Name: Basic log backup script
Author: Faryl O'Neil Hoover - XTIVIA
Date: 2/28/2021
Can be used in a SQL Server Agent job that runs hourly, for instance.
*/

-- Variables

DECLARE @DBCount int 

SELECT @DBCount = count(*) from sys.databases;

DECLARE @myCount int = 0;

DECLARE @dbName nvarchar(max);

DECLARE @SQL nvarchar(max);


WHILE (@myCount <= @DBcount)		--Basic WHILE statement for cycling through databases
BEGIN
	IF EXISTS
	(SELECT name FROM sys.databases 
	WHERE database_id = @myCount 
	AND name != 'tempdb'
	AND recovery_model = 1		-- Main difference from full backup script; only grab databases we can take log backups for
	AND state = 0				-- Make sure databases are online
	AND is_read_only = 0)		-- Make sure databases are not read-only
	BEGIN
		SELECT @dbName = name FROM sys.databases WHERE database_id = @myCount;

		SET @SQL = 'BACKUP LOG ' + @dbname + ' TO DISK = ''' + @dbName + 
		'_LOG_' + 
		CONVERT(char(8), getdate(), 112) + '_' + 
		CONVERT(char(2), datepart(hour, getdate())) + '_' +	-- Another main difference from full backup script: Automatically generate hour/minute for file name, since log backups should be taken at least hourly
		CONVERT (char(2), datepart(minute, getdate())) +
		'.TRN'' WITH COMPRESSION, CHECKSUM, STATS = 10';

		PRINT @SQL;

		EXEC sp_executesql @SQL;
	END
	
	SET @myCount = @mycount + 1
END

最后一点：有时，“备份不足”会变成“备份过多”——即在磁盘上。根据您的个人情况（例如数据库大小和数据恢复目标），您可能只需要将最新备份保存在磁盘上，以便快速轻松地恢复，同时将旧备份放入长期存储中，例如磁带或爷爷的棚。同样，您的里程可能会有所不同（甚至可能不需要长期存储，具体取决于您的环境），但值得一提的是，不受控制的备份可能会很快填满磁盘空间。无论如何，请务必注意您的磁盘空间并在需要时删除旧备份。