C/S架构软件开发平台 - 旗舰版V6.0 - 自动备份数据库计划
C/S架构软件开发平台 - 旗舰版V6.0 - 自动备份数据库计划
目录
一、创建数据库备份计划
启动SqlServer代理服务
打开MSSMS,执行SQL脚本
C# 全选
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;
注意更改的内容:
执行SQL成功:
测试作业
二、备份数据库存储过程(p_BackupDB)
C# 全选
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
三、删除历史备份文件
C# 全选
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;
四、客户端维护备份计划及备份数据库
功能路径:系统管理 -> 数据备份
创建备份计划
计划名称:用户自定义名称。
备份脚本:备份数据库的脚本。调用 p_BackupDB存储过程备份数据库。
一次执行作业可备份一个或多个数据库:
exec p_BackupDB "CartonERP_System","c:\baks\";
exec p_BackupDB "CartonERP_Normal","c:\baks\";
删除备份:执行备份计划同时删除历史备份文件。默认删除30天前的备份数据。
脚本所在数据库:是指备份数据库的存储过程所在数据库,默认在系统数据库,如:CSFrameworkV6.System库。
创建备份计划成功:
如果创建自动作业报错:
解决方案:
https://www.cscode.net/archive/csframeworkv6/548021875929093.html#mcetoc_1hu5guj092d
删除备份计划
在MSSMS中删除备份计划。
备份数据库
五、创建备份作业报错解决方案
如CSFrameworkV6开发框架,或用户开发的ERP/MES系统,使用普通用户连接数据库(非sa用户或Windows验证登录),因此创建自动备份计划会失败,需要给用户添加数据库权限。
本文以cartonerp用户登录为例:
1、添加 msdb 数据库角色成员身份 db_owner
2、添加服务器角色 sysadmin
3、创建备份目录
如:c:\bak\,d:\bak\ ,不可直接备份到c:\盘。
六、其他参考
查询用户创建的作业
SQL 全选
select * from msdb.dbo.sysjobs;
执行作业失败,日志查询
备份历史记录表(sys_BackupHistory)
SQL 全选
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]
版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
CSFrameworkV6 C/S框架网