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