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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了数据库远程全备份的一种解决方案，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8217字，纯文字阅读大概需要12分钟。

内容图文

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

以上就是数据库远程全备份的一种解决方案的内容，更多相关文章请关注PHP中文网（www.gxlcms.com）！

内容总结

以上是互联网集市为您收集整理的数据库远程全备份的一种解决方案全部内容，希望文章能够帮你解决数据库远程全备份的一种解决方案所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端