数据库远程全备份的一种解决方案

--exec BackUPDatabase_MaJiatao 'pubs','\\XZ154\ABC$','16:50:00.000',1,'XZ154\MaJiatao','MaJiatao'
/***************************************************
描述：数据库全备份和增量备份
编写：马加涛
修改：
马加涛：
2014-02-12：
1.加入了备份路径可以选择本机和远程路径
2.修正了保存历史备份记录的方式，不在需要本机硬盘上的文本文件来做保存
介质
***************************************************/
if object_id('BackUPDatabase_MaJiatao') is not null
drop proc BackUPDatabase_MaJiatao

GO

alter proc BackUPDatabase_MaJiatao
@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)
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)')
else
begin
if not exists(select * from syscolumns where name='file_is_exists' and ID=object_id
(N'backup_recorder'))
begin
drop table backup_recorder
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
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

--判断路径是网络路径还是本机路径
if left(@physical_backup_device_name,2)='\\' and ltrim(rtrim(@UserName))<>'' and ltrim(rtrim
(@Password))<>''
begin
select @CommandText='net use '+@physical_backup_device_name+' "'+@Password+'"
/user:'+@UserName
exec 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 @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