數據庫遠程全備份的一種解決方案
–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
–確定目錄是否存在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）！
