T-SQL Recipes之Database Backups
The Problem
在DBA和T-SQL码奴日常工作中,比如常规检查,服务管理,数据库管理, 是其中最具挑战性的一个领域。
在相似任务中,比如索引碎片管理,统计管理,数据库备份是异常重要的,对任何程序而言。
今天的重点就是讲动态SQL数据库备份
假设我们的需要是根据一个时间点来决定三种不同类型的备份:Full backups, differential backups, transaction log backups.
我们可以假定
- Full backups: 一个星期备份一次
- differential backups: 每隔一天运行一次
- transcation log backups:每天运行一次
The Solution
问题描述的差不多了,现在咱们开动吧。
IF OBJECT_ID('dbo.backup_plan', 'P') IS NOT NULL BEGIN DROP PROCEDURE dbo.backup_plan; END GO CREATE PROCEDURE dbo.backup_plan @differential_and_full_backup_time TIME = '00:00:00', -- Default to midnight @full_backup_day TINYINT = 1, -- Default to Sunday @backup_location NVARCHAR(MAX) = 'D:\SQLBackups\', -- Default to my backup folder @print_output_only BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @current_time TIME = CAST(CURRENT_TIMESTAMP AS TIME); DECLARE @current_day TINYINT = DATEPART(DW, CURRENT_TIMESTAMP); DECLARE @datetime_string NVARCHAR(MAX) = FORMAT(CURRENT_TIMESTAMP , 'MMddyyyyHHmmss'); DECLARE @sql_command NVARCHAR(MAX) = ''; DECLARE @database_list TABLE ( database_name NVARCHAR(MAX) NOT NULL , recovery_model_desc NVARCHAR(MAX) ); INSERT INTO @database_list ( database_name , recovery_model_desc ) SELECT name , recovery_model_desc FROM sys.databases WHERE databases.name NOT IN ( 'msdb', 'master', 'TempDB', 'model' ); -- Check if a full backup is to be taken now. IF (@current_day = @full_backup_day) AND (@current_time BETWEEN @differential_and_full_backup_time AND DATEADD(MINUTE, 10, @differential_and_full_backup_time)) BEGIN SELECT @sql_command = @sql_command + ' BACKUP DATABASE [' + database_name + '] TO DISK = ''' + @backup_location + database_name + '_' + @datetime_string + '.bak'';' FROM @database_list; END -- Check if a differential backup is to be taken now. ELSE IF (@current_day <> @full_backup_day) AND (@current_time BETWEEN @differential_and_full_backup_time AND DATEADD(MINUTE, 10, @differential_and_full_backup_time)) BEGIN SELECT @sql_command = @sql_command + ' BACKUP DATABASE [' + database_name + '] TO DISK = ''' + @backup_location + database_name + '_' + @datetime_string + '.dif'' WITH DIFFERENTIAL;' FROM @database_list; END -- If neither full or differential, then take a transaction log backup ELSE BEGIN SELECT @sql_command = @sql_command + ' BACKUP LOG [' + database_name + '] TO DISK = ''' + @backup_location + database_name + '_' + @datetime_string + '.trn''' FROM @database_list WHERE recovery_model_desc = 'FULL'; END IF @print_output_only = 1 BEGIN PRINT @sql_command; END ELSE BEGIN EXEC sp_executesql @sql_command; END END
从代码中可以看出,我们通过天数和时间点来判断三种不同类型的备份。我们还增加了一个只打印不运行的标志,现在我们来看看这三种不同类型的输出吧。
Full backups
EXEC dbo.backup_plan @differential_and_full_backup_time = '11:16:00', @full_backup_day = 2, @backup_location = 'D:\SQLBackups', @print_output_only = 1;
differential backups
EXEC dbo.backup_plan @differential_and_full_backup_time = '11:16:00', @full_backup_day = 3, @backup_location = 'D:\SQLBackups', @print_output_only = 1;
transcation log backups
EXEC dbo.backup_plan @differential_and_full_backup_time = '17:16:00', @full_backup_day = 3, @backup_location = 'D:\SQLBackups', @print_output_only = 1;
有了这个存储过程,以后我们备份数据库就方便许多了,当然你可以通过执行计划来备份的。