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

--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 nulldrop 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)')elsebeginif not exists(select * from syscolumns where name='file_is_exists' and ID=object_id(N'backup_recorder'))begindrop table backup_recorderexec('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)')endend

declare @backup_set_full sysname,@backup_set sysname,--备份文件名称@backup_name sysname

declare @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)=''--备份目录为空，使用系统默认目录beginSELECT @physical_backup_device_name=ltrim(rtrim(reverse(filename))) FROM master.dbo.sysdatabases where name=@database_nameset @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))<>''beginselect @CommandText='net use '+@physical_backup_device_name+' "'+@Password+'" /user:'+@UserNameexec master..xp_cmdshell @CommandText,no_outputend

--确定目录是否存在select @CommandText='dir '+@physical_backup_device_name+'/全备份'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputif @Return_Int<>0 --目录不存在,建立beginselect @CommandText='Mkdir '+@physical_backup_device_name+'/全备份'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputend

select @CommandText='dir '+@physical_backup_device_name+'/差异备份'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputif @Return_Int<>0 --目录不存在,建立beginselect @CommandText='Mkdir '+@physical_backup_device_name+'/差异备份'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputend

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

if @Return_Int<>0 --没有全备份文件存在，进行全备份beginselect @backup_set_full='全备份 '+@database_nameselect @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_fullif @@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_existsfrom backup_recorderwhere backup_name<>@physical_backup_device_name_now and is_all_backup='1' and file_is_exists='1'endendelsebegin--有全备份，验证全备份是否为上一天得指定时间之后--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),':',''),'.','')--进行全备份beginselect @backup_set_full='全备份 '+@database_nameselect @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_fullif @@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_existsfrom backup_recorderwhere backup_name<>@physical_backup_device_name_now and is_all_backup='1' and file_is_exists='1'endendelse--当前备份时间小于指定的全备份时间,进行差异备份begin

select @backup_set_full='增量备份 '+@database_nameselect @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_setif @@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_existsfrom backup_recorderwhere backup_name<>@physical_backup_device_name_now and is_all_backup='0' and file_is_exists='1'endendend

DECLARE DelFilePathName CURSOR FORWARD_ONLY FOR select backup_path From #backup_recorder OPEN DelFilePathNameFETCH NEXT FROM DelFilePathName into @DelFilePathNameWHILE @@FETCH_STATUS = 0beginif exists(select *from backup_recorder where backup_path=@DelFilePathName and backup_name<>@physical_backup_device_name_now)beginselect @CommandText='del '+@DelFilePathNameexecute @Return_Int=master..xp_cmdshell @CommandText--,no_outputif @Return_Int=0 beginupdate backup_recorder set file_is_exists=0 where backup_path=@DelFilePathNameendendFETCH NEXT FROM DelFilePathName into @DelFilePathNameendCLOSE DelFilePathNameDEALLOCATE DelFilePathName

if object_id('tempdb..#backup_recorder') is not nulldrop table #backup_recorder

if left(@physical_backup_device_name,2)='//' and ltrim(rtrim(@UserName))<>'' and ltrim(rtrim(@Password))<>''beginselect @CommandText='net share '+@physical_backup_device_name+' /delete'exec master..xp_cmdshell @CommandText,no_outputend