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