数据库远程全备份的一种解决方案
–exec BackUPDatabase_MaJiatao ‘pubs’,’XZ154ABC$’,’16:50:00.000′,1,’XZ154MaJiatao’,’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
智慧车行预约小程序
智慧车行小程序，是一个专门为洗车/4S/车辆维修行业打造的小程序，前后端完整代码包括车行动态，养车常识，保养预约，维修预约，洗车美容预约，汽车检测预约等功能。采用腾讯提供的小程序云开发解决方案，无须服务器和域名预约管理：开始/截止时间/人数均可灵活设置，可以自定义客户预约填写的数据项预约凭证：支持线下到场后校验签到/核销/二维码自助签到等多种方式详尽的预约数据：支持预约名单数据导出Excel，打印
0 查看详情 
–确定目录是否存在select @CommandText=’dir ‘+@physical_backup_device_name+’全备份’exec @Return_Int=master..xp_cmdshell @CommandText, no_outputif @Return_Int0 –目录不存在,建立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_Int0 –目录不存在,建立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_Int0 –没有全备份文件存在，进行全备份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.php.cn）！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/803962.html
微信扫一扫 
支付宝扫一扫 
