資料庫遠端全備份的解決方案
--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
