sql server 脚本创建备份数据库的存储过程及利用前述存储过程自动备份数据库的作业

本文链接：https://blog.csdn.net/hejisan/article/details/61196490

一、创建备份数据库的存储过程

/****** Object: Procedure [dbo].[SP_BackUp_DataBase_Log]   Script Date: 3/10/2017 2:17:36 PM ******/
USE [master];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE proc [dbo].[SP_BackUp_DataBase_Log]
(
	@FileRoot varchar(1024)='D:\BackUp',
	@DataBaseName varchar(128)='D_x64_3.02',
	@BackDay int=1,
	@BackCount int=7
)
as
begin
	exec sp_configure 'show advanced options',1;
	reconfigure with override;
	exec sp_configure 'xp_cmdshell','1';
	reconfigure with override;

	declare @FileLoad varchar(512)
	declare @FileDirectory varchar(512)
	CREATE TABLE #tmp ([File Exists] BIT, [File is a Directory] BIT, [Parent Directory Exists] BIT)

	set @FileLoad=@FileRoot
	INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
	EXEC master.dbo.xp_fileexist @FileLoad
	select @FileDirectory=[File is a Directory] from #tmp
	if(@FileDirectory=0)
	begin
		set @FileLoad='mkdir '+@FileLoad
		exec xp_cmdshell @FileLoad
	end
	
	truncate table #tmp
	set @FileLoad=@FileRoot+'\'+@DataBaseName
	INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
	EXEC master.dbo.xp_fileexist @FileLoad
	select @FileDirectory=[File is a Directory] from #tmp
	if(@FileDirectory=0)
	begin
		set @FileLoad='mkdir '+@FileLoad
		exec xp_cmdshell @FileLoad
	end

	truncate table #tmp
	set @FileLoad=@FileRoot+'\'+@DataBaseName+'\DY_'+convert(varchar(10),getdate(),112) 	
	INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
	EXEC master.dbo.xp_fileexist @FileLoad
	select @FileDirectory=[File is a Directory] from #tmp
	if(@FileDirectory=0)
	begin
		set @FileLoad='mkdir '+@FileLoad
		exec xp_cmdshell @FileLoad
	end

	declare @NoBackLogFlag int --第一次执行完全备份，不执行日志备份
	set @NoBackLogFlag=0
	declare @pd_filetime varchar(1024)
	declare @nowDay bigint
	set @nowDay=datediff(dd,'2013-08-23',Getdate())
	if(@nowDay%@BackDay=0)
	begin
		set @pd_filetime=@FileRoot+'\'+@DataBaseName+'\DY_'+convert(varchar(10),getdate(),112)+'\'+@DataBaseName+'.bak'
		truncate table #tmp
		INSERT INTO #tmp ([File Exists], [File is a Directory], [Parent Directory Exists])
		EXEC master.dbo.xp_fileexist @pd_filetime
		select @FileDirectory=[File Exists] from #tmp
		if(@FileDirectory=0)
		begin
			set @NoBackLogFlag=1;
			if(@BackCount<>0)
			begin
				declare @sql varchar(1024)
				set @sql='backup database ['+@DataBaseName+'] to disk='''+@pd_filetime+''' with NOINIT'
				exec(@sql)
			end
		end
		if(@NoBackLogFlag=0)
		begin
			set @pd_filetime=@FileRoot+'\'+@DataBaseName+'\DY_'+convert(varchar(10),getdate(),112)+'\'+@DataBaseName+'_'+convert(varchar(12),getdate(),112)+convert(varchar(2),DATEPART(hh,getdate()))+'.bak'
			set @sql='backup database ['+@DataBaseName+'] to disk='''+@pd_filetime+''' with DIFFERENTIAL,NOFORMAT, NOINIT'
			exec(@sql)
		end
	end

	declare @nowcount int
	set @pd_filetime='dir '+@FileRoot+'\'+@DataBaseName
	create table #tb(f_name varchar(200))
	insert into #tb exec master..xp_cmdshell @pd_filetime

	select id=IDENTITY(int,1,1),substring(f_name,charindex('DY_',f_name),len(f_name)-charindex('DY_',f_name)+1) as f_name
	into #T_name from #tb where f_name like '%DY_%'
	order by f_name asc

	set @nowcount=@@rowcount
	declare @temp_id int
	declare @temp_name varchar(128)
	declare @cmd varchar(1024)
	while(@nowcount>@BackCount)
	begin
		select top 1 @temp_id=id,@temp_name=f_name from #T_name
		order by f_name asc
		set @pd_filetime=@FileRoot+'\'+@DataBaseName+'\'+@temp_name
		set @cmd='del /Q '+@pd_filetime+'\*.*'
		exec master..xp_cmdshell @cmd
	
		SET @cmd = 'RD '+@pd_filetime
		EXEC master..xp_cmdshell @cmd

		delete from #T_name where id=@temp_id
		set @nowcount=@nowcount-1
	end
	
	exec sp_configure 'show advanced options',1;
	reconfigure with override;
	exec sp_configure 'xp_cmdshell','0';
	reconfigure with override;
	
	/*
	RESTORE DATABASE [GAMEDB_20120301]（数据库名） FROM  DISK = N'E:\DBA\Bak2FTP\GameDB\GAMEDB[20120301_1220].bak' WITH  FILE = 1,
	MOVE N'GAMEDB' TO N'E:\DBA\DB\GameDB\GAMEDB[20120302_1220].mdf'（文件组名）,
	MOVE N'GAMEDB_log' TO N'E:\DBA\DB\GameDB\GAMEDB[20120302_1220].ldf'（文件组名）,replace,NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG [GAMEDB_20120301] FROM  DISK = N'E:\DBA\Bak2FTP\GameDB\GAMEDB[20120301_1320].trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG [GAMEDB_20120301] FROM  DISK = N'E:\DBA\Bak2FTP\GameDB\GAMEDB[20120301_1420].trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG [GAMEDB_20120301] FROM  DISK = N'E:\DBA\Bak2FTP\GameDB\GAMEDB[20120301_1520].trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE DATABASE [GAMEDB_20120301]（数据库名） WITH RECOVERY
	
	restore filelistonly from disk =N'D:\BackUp\zjg\DY_20130922\zjg.bak'
	
	
	RESTORE DATABASE zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg.bak' WITH  FILE = 1,
	MOVE N'BaiBaoWan' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\zjgbak.mdf',
	MOVE N'BaiBaoWan_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\zjgbak_log.ldf',replace,NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309221.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309222.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309223.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309224.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309225.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309226.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309227.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309228.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_201309229.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_2013092210.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE LOG zjgbak FROM  DISK = N'D:\BackUp\zjg\DY_20130922\zjg_2013092211.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
	RESTORE DATABASE zjgbak WITH RECOVERY
	*/
end
GO

调用方式：exec SP_BackUp_DataBase_Log "D:\BackUp","dbname",1,7 --这样是一天内第一次做全备，然后后面的都是做增量备份

二、利用前述存储过程自动备份数据库的作业

USE [msdb]
GO

DECLARE @JobID BINARY(16)

EXECUTE msdb.dbo.sp_add_job @job_id = @JobID OUTPUT, @job_name = N'backup_nnclub1', --作业名称
   @enabled = 1,
   @owner_login_name = N'NT SERVICE\SQLSERVERAGENT',--一般选个执行的时候才有权限
   @description = N'无描述。',
   @category_name = N'[Uncategorized (Local)]',
   @notify_level_eventlog = 2,
   @notify_level_email = 0,
   @notify_level_netsend = 0,
   @notify_level_page = 0,
   @delete_level = 0;

EXECUTE msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'步骤一',
   @command = N'exec SP_BackUp_DataBase_Log "D:\BackUp","nnclub",1,7',--这里要上述备份存储过程的使用语句，注意修改数据库名和备份存储位置
   @database_name = N'nnclub',--这里注意修改数据库名
   @subsystem = N'TSQL',
   @flags = 0,
   @retry_attempts = 0,
   @retry_interval = 0,
   @on_success_step_id = 0,
   @on_success_action = 1,
   @on_fail_step_id = 0,
   @on_fail_action = 2;
EXECUTE msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;
EXECUTE msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
EXECUTE msdb.dbo.sp_add_jobschedule @job_id = @JobID,
        @name = N'第一个调度',
        @enabled = 1,
        @freq_type = 4,--重复循环时间单位，4代表（多少）天
        @active_start_date = 20170310,--开始时间
        @active_end_date = 99991231,--结束时间
        @freq_interval = 1,--按天计算，@freq_type = 4即为日，即每隔1天执行上述备份语句
        @freq_subday_type = 8,--每一天执行频率，8为按小时计算
        @freq_subday_interval = 2,--按小时计算，@freq_subday_interval=2,每隔两小时执行上述备份语句
        @active_start_time = 2500,--每天开始时间00:25:00
        @active_end_time = 235959;--每天结束时间23:59:59
GO