SQL Server differential backup, full backup

--EXEC BACKUPDATABASE_LEEHG Statement Parameter Description:--Example: exec backupdatabase_leehg  ' parameter one ', ' parameter two ', ' parameter three ', ' parameter four ', ' parameter Five ', ' parameter six '-- Parameter one: Need to back up the name of the database-parameter two: backup file storage path, can be network path-parameter three: Full backup time-parameter four: Full backup time error range (hours)-parameter five: parameter three is the network path, access the network path user name, parameter three is a local path when any character can be entered. --Parameter Six: parameter three is the network path, access the network path password, parameter three is a local path, you can enter any character. create proc backupdatabase@database_name sysname,--The name of the database to be backed up @physical_backup_device_name sysname,--backup file directory @all_ Backup_datetime char = ' 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 directory @all_backup_datetime char () Select @database_name = ' Test ', @physical_backup_device_name = ' e:\ Backup file \ Query server ', @all_backup_datetime = ' 16:00:00.000 ' ***************************************/--establish backup history if not EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id (n ' backup_recorder ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1) beginexec (' CREATE TABLE backup_recorder (backup_datetime datetime not null,backup_name varchar ($) PRIMARY Key,backup_path Varch AR (+) not NULL, Is_all_backup char (1) is not null default 0,file_is_exists char (1) is not null default 0) ') enddeclare @backu p_set_full sysname, @backup_set sysname,--backup file name @backup_name sysnamedeclare @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) = '--the database name is empty set @database_name =db_name ()--Backs up the current database if IsNull (@physical_backup_device_name, ") =" or RTrim (@physical_backup_device_name) = "--The backup directory is empty, Beginselect @physical_backup_device_name =ltrim using the system default directory ( RTrim (reverse (filename))) from master.dbo.sysdatabases where [Email protected]_nameset @physical_backup_device _name=reverse (substring (@physical_backup_device_name, charindex (' \ ', @physical_backup_device_name) +5,260) + ' Backup ' end--determine if the directory exists select @CommandText = ' dir ' [email protected]_backup_device_name+ ' \ Full backup ' exec @Return_Int = Master.. xp_cmdshell @CommandText, No_outputif @Return_Int <>0--Directory does not exist, establish beginselect @CommandText = ' Mkdir ' [email  protected]_backup_device_name+ ' full backup ' exec @Return_Int =master. xp_cmdshell @CommandText, No_outputendselect @CommandText = ' dir ' [email protected]_backup_device_name+ ' differential backup ' EXEC @Return_Int =master. xp_cmdshell @CommandText, No_outputif @Return_Int <>0--Directory does not exist, establish beginselect @CommandText = ' Mkdir ' [email  ProtectEd]_backup_device_name+ ' \ Differential backup ' exec @Return_Int =master. xp_cmdshell @CommandText, No_outputendselect @[email protected]_name+ ' _ ' +ltrim (RTrim (replace (replace (REPLACE (Convert (char (), GETDATE (), +), '-', ') ', ': ', '), '. ', '), ', ') ') + '. Bak ' if object_id (' tempdb. #backup_recorder ') is not a Nulldrop table #backup_recorderCREATE table #backup_recorder (backup_datetime datetime NOT NULL , Backup_name varchar (+) PRIMARY Key,backup_path varchar ($) NOT NULL, Is_all_backup char (1) is not NULL default 0,file_ Is_exists char (1) NOT NULL default 0)--check if a full backup exists select @CommandText = ' dir ' [email protected]_backup_device_name+ ' \ Full backup \*.bak ' exec @Return_Int =master. xp_cmdshell @CommandText, No_outputif @Return_Int <>0-No full backup file exists, full backup beginselect @backup_set_full = ' full backup ' [ Email protected]_nameselect @[email protected]_backup_device_name+ ' \ Full backup \ ' [email protected]_ backup_device_name_now--full backup, overriding media header backup DATABASE @database_name to [email protected]_backup_device_namebackup With FORMAT, NAME = @backup_set_fullif @ @error =0--Backup succeeded, delete all historical backup files prior to 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<> @physical_backup_device_name_now and is_all_backup= ' 1 ' and file_ is_exists= ' 1 ' endendelsebegin--has a full backup to verify that the full backup is--select @all_backup_datetime =replace after the specified time of the previous day (REPLACE (@all_backup_ DateTime, ': ', '), '. ', ') if Right (left (@physical_backup_device_name_now, 9), between replace (replace (@ All_backup_datetime, ': ', '), '. ', ') and replace (replace (substring (char), DATEADD (hh, @IntDistance, @all_ Backup_datetime), 21), 12,12), ': ', ')--for full backup beginselect @backup_set_full = ' full backup ' [email protected]_nameselect @[email protected]_backup_device_name+ ' \ Full backup \ ' [email protected]_backup_device_name_ now--full backup, overriding media header backup DATABASE @database_name to [Email protected]_backup_device_namebackup with FORMAT, name = @ Backup_set_fullif @ @error =0--backup successfully 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, differential backup beginselect @backup_set_full = ' incremental backup ' [Email protected]_nameselect @[email  protected]_backup_device_name+ ' differential backup \ ' [email protected]_backup_device_name_now--differential backup, append media backup DATABASE @databasE_name to [Email protected]_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 the historical backup file 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 ' endendenddeclare DelFilePathName CURSOR Forward_only for select Backup_path from #backup_recorder OPEN delfilepathnamefetch NEXT from Delfilepathname into @DelF Ilepathnamewhile @ @FETCH_STATUS = 0beginif exists (select *from backup_recorder where [email protected] and Backup_ name<> @physical_backup_device_name_now) Beginselect @CommandText = ' del ' [Email proTected]execute @Return_Int =master. xp_cmdshell @CommandText, no_outputif @Return_Int =0 beginupdate backup_recorder set file_is_exists=0 where [email  Protected]endendfetch NEXT from Delfilepathname to @DelFilePathNameendCLOSE delfilepathnamedeallocate Delfilepathnameif object_id (' tempdb.. #backup_recorder ') is not a nulldrop table #backup_recorderif left (@physical_backup_device_name, 2) = ' \ \ ' and LTrim (RTrim ( @UserName) <> "and LTrim (RTrim (@Password)) <> ' beginselect @CommandText = ' net share ' [email  protected]_backup_device_name+ '/delete ' exec master. xp_cmdshell @CommandText, No_outputend