A solution for database remote full backup

A solution for remote full database backup

--exec BackUPDatabase_MaJiatao 'pubs','\XZ154ABC$','16:50:00.000',1,'XZ154MaJiatao','MaJiatao'/***************************************************describe ：Database full backup and incremental backup Written by: Ma Jiatao Modified by Ma Jiatao: 2014-02-12: 1. Added the backup path to choose local and remote paths 2. Modified the way to save historical backup records, no longer requiring the local hard disk Text file on the file to be used as the storage medium****************************************** *********** /if object_id('BackUPDatabase_MaJiatao') is not nulldrop PRoc BackUPDatabase_MaJiatao

GO

alter proc BackUPDatabase_MaJiatao@database_name sysname,--The name of the database to be backed up@physical_backup_device_name sysname,--The backup file storage directory@all_backup_datetime char(17)=' 20:00:00.000',--Full backup time@IntDistance int=1,--Full backup time range (hours)@UserName varchar(100),--Remote server login name@PassWord varchar(100)=' '--Remote server login password with ENCRYPTION as

/*********************************declare @database_name sysname,--the name of the database to be backed up @physical_backup_device_name sysname,--backup File storage directory @all_backup_datetime char(17)select @database_name='test',@physical_backup_device_name='E: Backup file query server',@all_backup_datetime='16:00:00.000'

************ ********************************/

--Create backup history 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 file name @backup_name sysname

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

declare @physical_backup_device_name_now nvarchar(4000)

debackclare @physical_device_name backup nvarchar(4000)

if isnull( @database_name,'')='' or rtrim(@database_name)=''--The database name is empty set @database_name=db_name()--Back up the current database

if isnull(@physical_backup_device_name,'')='' or rtrim(@physical_backup_device_name)=''--The backup directory is empty, use the system default directory 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

--Determine whether the path is a network path or a local path 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:' + @UserName exec master.. 0 -- The directory does not exist, create beginselect @CommandText='Mkdir '+@physical_backup_device_name+'Full backup'exec @Return_Int=master..xp_cmdshell @CommandText, no_outputend

select @CommandText='dir '+@physical_backup_device_name+'Differential backup' exec @Return_Int=master..xp_cmdshell @CommandText, no_outputif @Return_Int<>0 --The directory does not exist, create beginselect @CommandText='Mkdir '+@physical_backup_device_name+'differential backup'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)

--Check if there is a full backup select @CommandText='dir '+@physical_backup_device_name+'Full backup*.bak'exec @Return_Int=master..xp_cmdshell @CommandText, no_output

if @Return_Int<>0 --No The full backup file exists, perform a full backup beginselect @backup_set_full='full backup'+@database_nameselect @physical_backup_device_namebackup=@physical_backup_device_name+'full backup'+@physical_backup_device_name_now

--Full backup, rewrite the media header BACKUP DATABASE @database_name to DISK=@ physical_backup_device_namebackup WITH FORMAT ,NAME = @backup_set_fullif @@error=0--Backup successful, delete all historical backup files before the full backup of the day begin--Write backup log 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<&g t;@physical_backup_device_name_now and is_all_backup='1' and file_is_exists='1'endendelsebegin--If there is a full backup, verify whether the full backup is the specified time of the previous day--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),':',''),'.','')--Proceed Full backup beginselect @backup_set_full='Full backup'+@database_nameselect @physical_backup_device_namebackup=@physical_backup_device_name+'Full backup'+@physical_backup_device_name_now--Full backup, rewrite the media header BACKUP DATABASE @database_name to DISK=@physical_backup_device_namebackup WITH FORMAT ,NAME = @backup_set_fullif @@error=0--Backup successful begin--Write backup log 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')- -Find historical backup files 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 --The current backup time is less than the specified full backup time, perform differential backup begin

select @backup_set_full='incremental backup'+@database_nameselect @physical_backup_device_namebackup=@physical_backup_device_name+'differential backup'+@physical_backup_device_name_now--differential backup, append media BACKUP DATABASE @database_name to DISK=@physical_backup_device_namebackup WITH NOINIT, DIFFERENTIAL,NAME = @backup_setif @@error=0--Backup successful begin--Write backup log 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')--Find historical backup files 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=@DelFileP athNameendendFETCH 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

The above is the database remote A solution for full backup. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!