C/S架构软件开发平台 - 旗舰版V6.0 - 自动备份数据库计划

BEGIN TRANSACTION

	/***********************************************************************
	程序名称：自动创建数据库备份计划
	作者：www.csframework.com
	************************************************************************/

	DECLARE @ReturnCode INT
	SELECT @ReturnCode = 0

	DECLARE @ERROR_MSG VARCHAR(100);
	DECLARE @V_OWNER_LOGIN_NAME VARCHAR(100);
	DECLARE @V_JOB_NAME VARCHAR(100);
	DECLARE @V_CATEGORY_NAME VARCHAR(100);
	DECLARE @V_SCHEDULE_UID VARCHAR(100);
	DECLARE @V_CMD_DELETE_BAK VARCHAR(100);
	DECLARE @V_CMD_BACKUP_BAK VARCHAR(100);
	DECLARE @V_JOB_START_TIME VARCHAR(10);
	DECLARE @V_SYSDBNAME VARCHAR(50); --存储过程所在的数据库
	
	SELECT @V_JOB_START_TIME=CONVERT(VARCHAR,GETDATE(),112);--20240507,--执行计划开始日期
	SELECT @V_OWNER_LOGIN_NAME=CAST(SERVERPROPERTY('MachineName') AS VARCHAR)+'\Administrator';
	SELECT @V_CATEGORY_NAME='CartonERP Database Maintenance';
	SELECT @V_SCHEDULE_UID=NEWID();
	print @V_OWNER_LOGIN_NAME;

	SELECT @V_JOB_NAME='CartonERP自动备份数据库';
	SELECT @V_SYSDBNAME='CartonERP_System';

	--备份2个数据库
	SELECT @V_CMD_BACKUP_BAK='exec p_BackupDB ''CartonERP_System'',''c:\baks\'';'+
	                         'exec p_BackupDB ''CartonERP_Normal'',''c:\baks\'';';
    --清除30天前的备份文件
	SELECT @V_CMD_DELETE_BAK='exec p_DeleteBakFiles 30;';

	-- select * from msdb.dbo.sysjobs;
	-- select @@servername
	-- SELECT SERVERPROPERTY('MachineName') AS ComputerName;
	----------------------------------------------------------------------------------------------------

	IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=@V_CATEGORY_NAME AND category_class=1)
	BEGIN
		EXEC @ReturnCode = msdb.dbo.sp_add_category 
			@class=N'JOB', 
			@type=N'LOCAL', 
			@name=@V_CATEGORY_NAME
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
		BEGIN
			SET @ERROR_MSG='执行【msdb.dbo.sp_add_category】发生错误';
			GOTO QuitWithRollback;
		END;

	END 

	IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name=@V_JOB_NAME)
	BEGIN	
		SET @ERROR_MSG='自动备份计划已经存在';
		GOTO QuitWithRollback;
	END 

	DECLARE @jobId BINARY(16)
	EXEC @ReturnCode = msdb.dbo.sp_add_job 
		@job_name=@V_JOB_NAME,
  		@enabled=1,
  		@notify_level_eventlog=0,
  		@notify_level_email=0,
  		@notify_level_netsend=0,
  		@notify_level_page=0,
  		@delete_level=0,
  		@description=N'无描述。',
  		@category_name=@V_CATEGORY_NAME,
  		@owner_login_name=@V_OWNER_LOGIN_NAME, 
		@job_id = @jobId OUTPUT
	IF (@@ERROR <> 0 OR @ReturnCode <> 0)
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_add_job】发生错误';
		GOTO QuitWithRollback;
	END;
	
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
		@job_id=@jobId, 
		@step_name=@V_JOB_NAME,
  		@step_id=1,
  		@cmdexec_success_code=0,
  		@on_success_action=3,
  		@on_success_step_id=0,
  		@on_fail_action=2,
  		@on_fail_step_id=0,
  		@retry_attempts=0,
  		@retry_interval=0,
  		@os_run_priority=0, 
		@subsystem=N'TSQL',
  		@command=@V_CMD_BACKUP_BAK,
  		@database_name=@V_SYSDBNAME,
  		@flags=0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_add_jobstep】发生错误';
		GOTO QuitWithRollback;
	END;
	
	EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
		@job_id=@jobId, 
	    @step_name=N'删除备份历史文件',
	  @step_id=2,
	  @cmdexec_success_code=0,
	  @on_success_action=1,
	  @on_success_step_id=0,
	  @on_fail_action=2,
	  @on_fail_step_id=0,
	  @retry_attempts=0,
	  @retry_interval=0,
	  @os_run_priority=0, @subsystem=N'TSQL',
	  @command=@V_CMD_DELETE_BAK,
	  @database_name=@V_SYSDBNAME,
	  @flags=0
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_add_jobstep】发生错误';
		GOTO QuitWithRollback;
	END;

	EXEC @ReturnCode = msdb.dbo.sp_update_job 
		@job_id = @jobId, 
		@start_step_id = 1
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_update_job】发生错误';
		GOTO QuitWithRollback;
	END;

	--添加作业计划
	EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule 
		@job_id=@jobId, 
		@name=@V_JOB_NAME,
  		@enabled=1,
	  @freq_type=4,
	  @freq_interval=1,
	  @freq_subday_type=1,
	  @freq_subday_interval=0,
	  @freq_relative_interval=0,
	  @freq_recurrence_factor=0,
	  @active_start_date=@V_JOB_START_TIME,--执行计划开始日期
	  @active_end_date=99991231,
	  @active_start_time=20000,--凌晨2点开始执行
	  @active_end_time=235959,
	  @schedule_uid=@V_SCHEDULE_UID
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_add_jobschedule】发生错误';
		GOTO QuitWithRollback;
	END;

	--添加作业服务
	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver 
		@job_id = @jobId, 
		@server_name = N'(local)'
	IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	BEGIN
		SET @ERROR_MSG='执行【msdb.dbo.sp_add_jobserver】发生错误';
		GOTO QuitWithRollback;
	END;
	
--提交事务
COMMIT TRANSACTION	
	SELECT 'SUCCESS' AS Result,'提交事务成功.' AS Message;
	RETURN;

--错误标签
QuitWithRollback:
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION	
	SELECT 'FAILED' AS Result,@ERROR_MSG+'，操作失败!' AS Message;
	RETURN;
	

ALTER PROCEDURE [dbo].[p_BackupDB] 
	@DBNAME SYSNAME,          --要备份的数据库名称,不指定则备份当前数据库 
	@BKPATH NVARCHAR(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录 
	@BKFNAME NVARCHAR(260)='',--备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间 
	@BKTYPE NVARCHAR(10)='DB',--备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份 
	@APPENDFILE BIT=1         --追加/覆盖备份文件 
AS 
BEGIN

/****************************************************************************
功能：备份数据库到指定目录,并写入备份日志
作者：www.csframework.com

--测试：备份1个数据库
exec p_BackupDB 'CartonERP_System';
exec p_BackupDB 'CartonERP_System','C:\JonnysDBs\'
exec p_BackupDB 'CartonERP_System','C:\JonnysDBs\','555.bak'
exec p_BackupDB 'CartonERP_System','C:\JonnysDBs\','555.bak','DB'
exec p_BackupDB 'CartonERP_System','C:\JonnysDBs\','555.bak','DF'
exec p_BackupDB 'CartonERP_System','C:\JonnysDBs\','555.bak','LOG' --注意：日志备份文件巨大，速度慢，不建议!!!

--测试：同时备份2个数据库
exec p_BackupDB 'CartonERP_System';exec p_BackupDB 'CartonERP_Normal';
exec p_BackupDB 'CartonERP_System','c:\JonnysDBs\';exec p_BackupDB 'CartonERP_Normal','c:\JonnysDBs\';

*****************************************************************************/

	DECLARE @SQL VARCHAR(8000) 

	IF ISNULL(@DBNAME,'')=''  SET @DBNAME=DB_NAME()--当前数据库 	
	--文件格式模板：\CartonERP_System_20240517_234607.BAK
	IF ISNULL(@BKFNAME,'')='' SET @BKFNAME='\DBNAME\_\DATE\_\TIME\.BAK' 
	IF ISNULL(@BKPATH,'')=''  
	BEGIN
		declare @path varchar(1000)
		select @path=rtrim(filename) from master..sysfiles where name='master'
		SET @BKPATH=replace(@path,'master.mdf','')		
		print @BKPATH;
	END;

	--生成bak文件名称
	SET @BKFNAME=REPLACE(REPLACE(REPLACE(@BKFNAME,'\DBNAME\',@DBNAME) 
	,'\DATE\',CONVERT(VARCHAR,GETDATE(),112)) 
	,'\TIME\',REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')) 

	--生成备份SQL脚本：
	--BACKUP DATABASE [CartonERP_System] TO DISK='c:\JonnysDBs\CartonERP_System_20240517_235042.BAK' WITH NOINIT
	SET @SQL='BACKUP '+CASE @BKTYPE WHEN 'LOG' THEN 'LOG ' ELSE 'DATABASE ' END 
	+'['+@DBNAME+']'
	+' TO DISK='''+@BKPATH+@BKFNAME 
	+''' WITH '+CASE @BKTYPE WHEN 'DF' THEN 'DIFFERENTIAL,' ELSE '' END 
	+CASE @APPENDFILE WHEN 1 THEN 'NOINIT' ELSE 'INIT' END 

	PRINT @SQL 
	EXEC(@SQL) 

	--保存备份历史记录
	IF @@ERROR=0
	BEGIN
		PRINT '备份日志'
		INSERT INTO dbo.sys_BackupHistory(isid,DBName,BackupFileName,BackupPath,BackupTime) VALUES 
		(NEWID(),@DBNAME,@BKFNAME,@BKPATH+@BKFNAME,GETDATE())
		-- select * from sys_BackupHistory
	END

END

ALTER procedure [dbo].[p_DeleteBakFiles]
	@Days int --天数，7：删除7天前的备份文件
as
begin
/************************************************************
功能：删除数据库备份文件
作者：www.csframework.com
说明：
	1、该存储过程依赖【sys_BackupHistory】表，获取备份文件位置。
	2、备份文件必须是用户自定义目录，比如：d:\bak_files\，如果是系统目录删除会失败，提示找不到文件。
	   系统目录：C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
-------------------------------------------------------------

SELECT * FROM sys_BackupHistory

p_DeleteBakFiles 0 --删除当天
p_DeleteBakFiles 1 --删除1天前的备份

*************************************************************/
	DECLARE @CMD VARCHAR(1000)

	--exec sp_configure 'show advanced options', 1;
	reconfigure;
	exec sp_configure 'xp_cmdshell', 1; ---1代表开启 xp_cmdshell 0代表停用xp_cmdshell
	reconfigure;
	PRINT 'CONFIG'
	------------------------------------------------------------------
	declare @name varchar(500)
	declare cursor1 cursor for         --定义游标cursor1
	SELECT BackupPath FROM sys_BackupHistory where GETDATE()-BackupTime>@Days
	open cursor1                       --打开游标
	fetch next from cursor1 into @name  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中
	while @@fetch_status=0           --判断是否成功获取数据
	begin
		print @name
		
		SELECT @CMD='del '+@name --组合命令
		exec master.dbo.xp_cmdshell @CMD  --删除文件
		
		--删除备份记录
		if (@@rowcount=1) delete sys_BackupHistory where BackupPath=@name 

		fetch next from cursor1 into @name  --将游标向下移1行
	end

	close cursor1 --关闭游标
	deallocate cursor1

end;

CREATE TABLE [dbo].[sys_BackupHistory](
	[isid] [varchar](50) NOT NULL,
	[DBName] [varchar](50) NULL,
	[BackupFileName] [nvarchar](100) NULL,
	[BackupPath] [nvarchar](250) NULL,
	[BackupTime] [datetime] NULL,
 CONSTRAINT [PK_sys_BackupHistory] PRIMARY KEY CLUSTERED 
(
	[isid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]