MSSQL作业备份

MSSQL Server维护计划异常，使用作业脚本配合存储过程进行定时备份脚本示例

USE [WRGGXX_ULearning]
GO
/****** Object:  StoredProcedure [dbo].[usp_backup_WRGGXX_ULearning]    Script Date: 07/15/2021 11:09:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Yangrf>
-- Create date: <2020-3-12>
-- Description:	<系统故障，无法自动备份，使用手动备份>
-- =============================================
ALTER PROCEDURE [dbo].[usp_backup_WRGGXX_ULearning]
AS
BEGIN
	SET NOCOUNT ON;

    declare @dbName varchar(50); 
	declare @pathName varchar(50);  
	declare @filename varchar(200);  
	declare @filename2 varchar(200);  
	declare @datetime varchar(50);  
	declare @weekname varchar(50);  
	declare @weeknametmp varchar(4);  
	declare @weekday int;  
	declare @backupSetId int;
	  
	set @dbName='WRGGXX_ULearning';  
	set @pathName='D:\db_backup\';  
	  
	set @weekname=datename(weekday,getdate());  
	set @datetime =  convert(varchar(20),getdate(),112);  
	set @weeknametmp=substring(@weekname,3,1);  
	  
	set @filename=@pathName+@dbName+'\'+@dbName+'_backup_'+DateName(year,GetDate())+'_'+DateName(month,GetDate())+'_'+DateName(day,GetDate())+'_'+DateName(hour,GetDate())+DateName(minute,GetDate())+DateName(second,GetDate())+'_'+DateName(MILLISECOND,GetDate());  
	set @filename2=@filename+'.bak';  
	 
	if (@weeknametmp='一')  
		set @weekday=1;  
	else if (@weeknametmp='二')  
		set @weekday=2;  
	else if (@weeknametmp='三')  
		set @weekday=3;  
	else if (@weeknametmp='四')  
		set @weekday=4;  
	else if (@weeknametmp='五')  
		set @weekday=5;  
	else if (@weeknametmp='六')  
		set @weekday=6;  
	else if (@weeknametmp='日')  
		set @weekday=0;  
	  
	if (@weekday=0)  
		begin  
			BACKUP DATABASE [WRGGXX_ULearning] TO  DISK = @filename2 WITH NOFORMAT, NOINIT,  NAME = @filename, SKIP, REWIND, NOUNLOAD,  STATS = 10 ;

			
			select @backupSetId = position from msdb..backupset where database_name=@dbName and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@dbName )
			if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“WRGGXX_ULearning”的备份信息。', 16, 1) end
			RESTORE VERIFYONLY FROM  DISK =@filename2 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
		end  
	else  
		begin  
			BACKUP DATABASE [WRGGXX_ULearning] TO  DISK = @filename2 WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = @filename, SKIP, REWIND, NOUNLOAD,  STATS = 10

			select @backupSetId = position from msdb..backupset where database_name=@dbName and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@dbName )
			if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“WRGGXX_ULearning”的备份信息。', 16, 1) end
			RESTORE VERIFYONLY FROM  DISK = @filename2 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

		end
END