資料庫遠端全備份的解決方案

資料庫遠端全備份的解決方案

--exec BackUPDatabase_MaJiatao 'pubs','\XZ154ABC$','16:50:00.000',1,'XZ154MaJiatao','MaJiatao'/***************************************************描述：資料庫全備份和增量備份編寫：馬加濤修改：馬加濤：2014-02-12：1.加入了備份路徑可以選擇本機和遠端路徑2.修正了保存歷史備份記錄的方式，不在需要本機硬碟上的文字檔案來做保存媒體****************************************** ********#*******************************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'

********* *****************************#&M*&M /if object_id('BackUPDatabase_MaJiatao') is not nulldrop PRoc BackUPDatabase_MaJiatao

GO

alter proc BackUPDatabase_MaJiatao@database_name sysname,back_name, 選項char(17)=' 20:00:00.000',--全備份的時間@IntDistance int=1,--全備份的時間範圍（小時）@UserName varchar(100),--遠端伺服器登入名稱@PassWord varchar(100)=' '--遠端伺服器登入密碼with ENCRYPTION as

/**&*/

--建立備份歷史記錄if not exists (select * from dbo.sysobjects where id = object_id(N'backup_recorder'PRO) and OBJECTPERPER'PROTY (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. 。 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)')))'))dull --備份檔案名稱@backup_name sysname

declare @Return_Int intdeclare @CommandText nvarchar(4000)declare @DelFilePathName nvarchar(4000)

declare @physical_backup_device_name_nowcfforenf; char(4000)

if isnull( @database_name,'')='' or rtrim(@database_name)=''--資料庫名稱為空set @database_name=db_name()--備份目前資料庫

if isnull(@physical_backup_device_name,'')=''或 rtrim(@physical_backup_device_name)=''--備份目錄為空，使用系統預設目錄beginSELECT @physical_backup_device_name=ltrim(rtrim(reverse(filename))) FROM master.dbo.sysdatabases where name=@database_nameset_physical_back substring(@physical_backup_device_name,charindex('',@physical_backup_device_name)+5,260))+'backup'end

--判斷路徑是網路路徑還是本機路徑if left(@physical_backup_device_name,2)='\' 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_Int0 --目錄不存在,建立beginselect @CommandText='Mkdir '+@physical_backup_device_name+'全備份'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputend

select @CommandText='dir '+physical_backup_Intsical_backup_Int_ ..xp_cmdshell @CommandText, no_outputif @Return_Int0 --目錄不存在,建立beginselect @CommandText='Mkdir '+@physical_backup_device_name+'差異備份'exec @Return_Int=master..xp_cmdup @phy, no_nows_phy, no_ =@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_recorder,CREATE TABLE #backup_recorder (backup_datetime date date not null,CREATE TABLE #backup_recorder (backup_datetime date dates not (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

if @Return_Int0 --備份檔案存在，進行全備份beginselect @backup_set_full='全備份'+@database_nameselect @physical_backup_device_namebackup=@physical_backup_device_name+'全備份'+@physical_backup_device_name_nowbid

３３３３３３３３＊３３３３３３＊３３３＊３３３３３＊３＊３＊３３. _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)valueues(get (),@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). 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--全備備份，重寫媒體頭BACK DATAB_back_backup_device_name_now--全備份，重寫媒體頭BACK 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_backup_backup_device_name_now,@physical_backup__device_0,1000,41'歷史備份檔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_existfrom backffre; ' and file_is_exists='1'endendelse--當前備份時間小於指定的全備份時間,進行差異備份begin

select @backup_set_full='增量備份'+@database_nameselect @physical_backup_device_namebackup=@physical_backup_device_name+'差異備份'+Qphysical_namebackup=@physical_backup_device_name+'差異備份'+Qphysical_backup_device_name_back to DISK=@physical_backup_device_namebackup WITH NOINIT , DIFFERENTIAL,NAME = @backup_setif @@error=0--備份成功begin--寫備份日誌insert into backup_recorder(backup_datetime,backup_name,back0up_exes_back_backl ( 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), back_exists. up_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 FROMgin Delupleconc​​id -xexTCH_Fidbid 4000 月_recorder where backup_path=@DelFilePathName and 。 into @DelFilePathNameendCLOSE DelFilePathNameDEALLOCATE DelFilePathName

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

if left(@physical_backup_device_name,2)=''rim and User1(Frim)( (rtrim(@Password))''beginselect @CommandText='net share '+@physical_backup_device_name+' /delete'exec master..xp_cmdshell @CommandText,no_outputend

以上就是資料庫遠端全備份的一種解決方案的內容，更多相關文章請關注PHP中文網（www.php.cn）！

🎜