--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)‘)	enddeclare @backup_set_full sysname,	@backup_set sysname,			--备份文件名称	@backup_name sysnamedeclare @Return_Int intdeclare @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_outputif @Return_Int<>0 	--目录不存在,建立	begin		select @CommandText=‘Mkdir ‘+@physical_backup_device_name+‘\全备份‘		exec @Return_Int=master..xp_cmdshell @CommandText, no_output	endselect @CommandText=‘dir ‘+@physical_backup_device_name+‘\差异备份‘exec @Return_Int=master..xp_cmdshell @CommandText, no_outputif @Return_Int<>0 	--目录不存在,建立	begin		select @CommandText=‘Mkdir ‘+@physical_backup_device_name+‘\差异备份‘		exec @Return_Int=master..xp_cmdshell @CommandText, no_output	endselect @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_recorderCREATE 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_outputif @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					endelse	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 DelFilePathNameFETCH NEXT FROM DelFilePathName into @DelFilePathNameWHILE @@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	endCLOSE DelFilePathNameDEALLOCATE DelFilePathNameif object_id(‘tempdb..#backup_recorder‘) is not null	drop table #backup_recorderif 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