SQLSERVER 差异备份、全备份

--exec BackUPDatabase_LeeHG语句参数说明：
--				示例：exec BackUPDatabase_LeeHG  ‘参数一‘,‘参数二‘,‘参数三‘,‘参数四‘,‘参数五‘,‘ 参数六‘
--				参数一：需要备份数据库的名称
--				参数二：备份文件存放路径，可以是网络路径
--				参数三：全备份时间
--				参数四：全备份时间误差范围（小时）
--				参数五：参数三为网络路径时，访问网络路径的用户名，参数三为本地路径时可输入任意字符。
--				参数六：参数三为网络路径时，访问网络路径的密码，参数三为本地路径时可输入任意字符。

create proc BackUPDatabase
	@database_name sysname,						--要备份的数据库名称
	@physical_backup_device_name sysname,				--备份文件存放目录
	@all_backup_datetime char(17)=‘20:00:00.000‘,			--全备份的时间
	@IntDistance int=1,					--全备份的时间范围（小时）

@UserName varchar(100), --远程服务器登录名称

  @Password varchar(100)=‘‘ --远程服务器登录密码
  with ENCRYPTION

as

/*********************************
declare @database_name sysname,						--要备份的数据库名称
	@physical_backup_device_name sysname,				--备份文件存放目录
	@all_backup_datetime char(17)
	
select  @database_name=‘test‘,
	@physical_backup_device_name=‘E:\备份文件\查询服务器‘,
	@all_backup_datetime=‘16:00:00.000‘

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



--建立备份历史记录
if not exists (select * from dbo.sysobjects where id = object_id(N‘backup_recorder‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)
	begin
		exec(‘CREATE TABLE backup_recorder (backup_datetime datetime not null,backup_name varchar (500) PRIMARY KEY,backup_path varchar (500)  NOT NULL ,is_all_backup char(1) not null default 0,file_is_exists char(1) not null default 0)‘)
	end



declare @backup_set_full sysname,
	@backup_set sysname,			--备份文件名称
	@backup_name sysname

declare @Return_Int int
declare @CommandText nvarchar(4000)
				
declare @DelFilePathName nvarchar(4000)

declare @physical_backup_device_name_now nvarchar(4000)

declare @physical_backup_device_namebackup nvarchar(4000)


if isnull(@database_name,‘‘)=‘‘ or rtrim(@database_name)=‘‘		--数据库名称为空
	set @database_name=db_name()					--备份当前数据库

if isnull(@physical_backup_device_name,‘‘)=‘‘ or rtrim(@physical_backup_device_name)=‘‘	
	--备份目录为空，使用系统默认目录
	begin
		SELECT @physical_backup_device_name=ltrim(rtrim(reverse(filename))) FROM master.dbo.sysdatabases where name=@database_name
		set @physical_backup_device_name=reverse(substring(@physical_backup_device_name,charindex(‘\‘,@physical_backup_device_name)+5,260))+‘backup‘
	end



--确定目录是否存在
select @CommandText=‘dir ‘+@physical_backup_device_name+‘\全备份‘
exec @Return_Int=master..xp_cmdshell @CommandText, no_output
if @Return_Int<>0 
	--目录不存在,建立
	begin
		select @CommandText=‘Mkdir ‘+@physical_backup_device_name+‘\全备份‘
		exec @Return_Int=master..xp_cmdshell @CommandText, no_output
	end

select @CommandText=‘dir ‘+@physical_backup_device_name+‘\差异备份‘
exec @Return_Int=master..xp_cmdshell @CommandText, no_output
if @Return_Int<>0 
	--目录不存在,建立
	begin
		select @CommandText=‘Mkdir ‘+@physical_backup_device_name+‘\差异备份‘
		exec @Return_Int=master..xp_cmdshell @CommandText, no_output
	end

select @physical_backup_device_name_now=@database_name+‘_‘+
	ltrim(rtrim(REPLACE(REPLACE(REPLACE(REPLACE(convert(char(23),getdate(),21),‘-‘,‘‘),‘:‘,‘‘),‘.‘,‘‘),‘ ‘,‘‘)))+
	‘.bak‘

if object_id(‘tempdb..#backup_recorder‘) is not null
	drop table #backup_recorder
CREATE TABLE #backup_recorder (backup_datetime datetime not null,backup_name varchar (500) PRIMARY KEY,backup_path varchar (500)  NOT NULL ,is_all_backup char(1) not null default 0,file_is_exists char(1) not null default 0)

--检查是否有全备份存在
select @CommandText=‘dir ‘+@physical_backup_device_name+‘\全备份\*.bak‘
exec @Return_Int=master..xp_cmdshell @CommandText, no_output

if @Return_Int<>0 			--没有全备份文件存在，进行全备份
	begin
		select @backup_set_full=‘全备份 ‘+@database_name
		
		select @physical_backup_device_namebackup=@physical_backup_device_name+‘\全备份\‘+@physical_backup_device_name_now

		--全备份，重写媒体头
		BACKUP DATABASE  @database_name to DISK=@physical_backup_device_namebackup WITH FORMAT ,NAME = @backup_set_full
		
		if @@error=0	--备份成功,删除当天全备份之前的所有历史备份文件
			begin
				--写备份日志
				insert into backup_recorder
					(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)
				values
					(getdate(),@physical_backup_device_name_now,@physical_backup_device_namebackup,‘1‘,‘1‘)
				insert into #backup_recorder	
					(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)
				select backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists
				from backup_recorder	
				where backup_name<>@physical_backup_device_name_now and is_all_backup=‘1‘ and file_is_exists=‘1‘				
			end				
	end
else
	begin
		--有全备份，验证全备份是否为上一天得指定时间之后
		--select @all_backup_datetime=REPLACE(REPLACE(@all_backup_datetime,‘:‘,‘‘),‘.‘,‘‘)
		
		if right(left(right(@physical_backup_device_name_now,21),17),9) between REPLACE(REPLACE(@all_backup_datetime,‘:‘,‘‘),‘.‘,‘‘) and REPLACE(REPLACE(substring(convert(char(23),dateadd(hh,@IntDistance,@all_backup_datetime),21),12,12),‘:‘,‘‘),‘.‘,‘‘)
			--进行全备份
			begin
				select @backup_set_full=‘全备份 ‘+@database_name
				
				select @physical_backup_device_namebackup=@physical_backup_device_name+‘\全备份\‘+@physical_backup_device_name_now
		
				--全备份，重写媒体头
				BACKUP DATABASE  @database_name to DISK=@physical_backup_device_namebackup WITH FORMAT ,NAME = @backup_set_full
				
				if @@error=0	--备份成功
					begin
						--写备份日志
						insert into backup_recorder
							(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)
						values
							(getdate(),@physical_backup_device_name_now,@physical_backup_device_namebackup,‘1‘,‘1‘)	
						--查找历史备份文件
						insert into #backup_recorder	
							(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)	
						select backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists
						from backup_recorder	
						where backup_name<>@physical_backup_device_name_now and is_all_backup=‘1‘ and file_is_exists=‘1‘				
					end
			end
			
		else		
			--当前备份时间小于指定的全备份时间,进行差异备份
			begin

				select @backup_set_full=‘增量备份 ‘+@database_name
				
				select @physical_backup_device_namebackup=@physical_backup_device_name+‘\差异备份\‘+@physical_backup_device_name_now
				
				--差异备份，追加媒体
				BACKUP DATABASE  @database_name to DISK=@physical_backup_device_namebackup WITH NOINIT , DIFFERENTIAL,NAME = @backup_set
		
				if @@error=0	--备份成功
					begin
						--写备份日志
						insert into backup_recorder
							(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)
						values
							(getdate(),@physical_backup_device_name_now,@physical_backup_device_namebackup,‘0‘,‘1‘)	
						--查找历史备份文件
						insert into #backup_recorder	
							(backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists)
						select backup_datetime,backup_name,backup_path,is_all_backup,file_is_exists
						from backup_recorder	
						where backup_name<>@physical_backup_device_name_now and is_all_backup=‘0‘ and file_is_exists=‘1‘
					end	
			end
			
	end	


			
DECLARE DelFilePathName CURSOR FORWARD_ONLY  FOR  select backup_path From #backup_recorder 
								
OPEN DelFilePathName
FETCH NEXT FROM DelFilePathName into @DelFilePathName
WHILE @@FETCH_STATUS = 0
	begin
		if exists(select *from backup_recorder 	where backup_path=@DelFilePathName and backup_name<>@physical_backup_device_name_now)
			begin
				select @CommandText=‘del ‘+@DelFilePathName
				execute @Return_Int=master..xp_cmdshell @CommandText,no_output
				if @Return_Int=0 
					begin
						update backup_recorder set file_is_exists=0 where backup_path=@DelFilePathName
					end
			end
		FETCH NEXT FROM DelFilePathName into @DelFilePathName
	end
CLOSE DelFilePathName
DEALLOCATE DelFilePathName


if object_id(‘tempdb..#backup_recorder‘) is not null
	drop table #backup_recorder

if left(@physical_backup_device_name,2)=‘\\‘ and ltrim(rtrim(@UserName))<>‘‘ and ltrim(rtrim(@Password))<>‘‘
	begin
		select @CommandText=‘net share  ‘+@physical_backup_device_name+‘ /delete‘
		exec master..xp_cmdshell @CommandText,no_output
	end