--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