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
