SQL Server Database backup method

A tool curl must be prepared in advance to place it on the C drive. Then, install 7z on the server where the database resides. Finally, the 2 stored procedures are executed, and a new job is created in the agent of SQL Server to implement the backup operation.

--Backing up the specified database to a local and remote specified location (7-zip compression) Create PROCEDURE [dbo]. [backupdb7z] @dbname sysname--database name asdeclare @backupfile nvarchar ( $) --Local backup file name declare @backuplogfile nvarchar ( $) --Local Log backup file name declare @archivefile nvarchar ( $) --Local Compressed file name declare @archivelogfile nvarchar ( $) --Local Compressed file name declare @target_localpath nvarchar ( -) --Local backup path declare @target_backupfile nvarchar ( +) --Local backup file full path declare @target_backuplogfile nvarchar ( +) --Local BACKUP LOG file full path declare @target_archivefile nvarchar ( +) --Local compressed file full path declare @target_archivelogfile nvarchar ( +) --Local Log compressed file full path declare @descr nvarchar ( -) --backup File Description declare @date datetimedeclare @date_str varchar ( -) --Date Time string declare @cmd nvarchar (4000) --the command to execute declare @log_file nvarchar ( $) --declare @cmd_7z nvarchar ( $) declare @7z_opt nvarchar ( +) declare @cmd_tool nvarchar ( $)='C:\curl\curl-T'declare @remotepath nvarchar ( -)="'--Remote backup path declare @ftp_user nvarchar ( -)="'--FTP account declare @ftp_pw nvarchar ( $)="'--ftp password declare @ftp_opt nvarchar ( +)='ftp://'declare @localpath nvarchar ( -)="'--Local backup path--parameter SettingsSelect@log_file ='D:\shellcmd_log.txt'Select@cmd_7z ='7z'Select@7z_opt='A-t7z-mx=9-mmt=on' Select@date =getdate ()Select@[email Protected]+cast (Year (@date) asnvarchar) +'years'+cast (Month (@date) asnvarchar) +'Month'+cast (Day (@date) asnvarchar) +'Daily Full backup'Select@[email protected]+'. bak'Select@[email protected]+'. Log.bak'Select@date_str =convert (varchar ( -), @date, -)Select@date_str =replace (@date_str,'-',"')Select@date_str =replace (@date_str,' ','_')Select@date_str =replace (@date_str,':',"')Select@[email protected]+'_'[Email protected]_str+'. 7z'Select@[email protected]+'_'[Email protected]_str+'. log.7z'if(@localpath <>"'And Right (@localpath,1) <>'\ ')Select@[email protected]+'\ 'ElseSelect@target_localpath =@localpathSelect@[email protected]_localpath+@backupfileSelect@[email protected]_localpath+@backuplogfileSelect@[email protected]_localpath+@archivefileSelect@[email protected]_localpath+@archivelogfile--Shrinking a database--DUMP Transaction @dbname with No_log--DBCC Shrinkdatabase (@dbname,0, Truncateonly)--backing up a database backup @dbname to disk[Email protected]_backupfile with FORMAT, description=@descr--backing up logs backup log @dbname to disk=@target_backuplogfile with formatexec [Cleardblog] @dbname--Compress DatabaseSelect@[email protected]_7z+' '[Email protected]_opt+' '[Email protected]_archivefile+' '+@target_backupfile--execute command exec xp_cmdshell @cmd--Compress LogSelect@[email protected]_7z+' '[Email protected]_opt+' '[Email protected]_archivelogfile+' '+@target_backuplogfile--execute command exec xp_cmdshell @cmd--transferring compressed files to a remote serverif@remotepath <>"'beginSelect@[email protected]_tool+' '[Email protected]_archivefile+'- u'[Email protected]_user+':'[Email protected]_pw+' '[Email protected]_opt+"'+@remotepath--print (@cmd)--execute command exec xp_cmdshell @cmdSelect@[email protected]_tool+' '[Email protected]_archivelogfile+'- u'[Email protected]_user+':'[Email protected]_pw+' '[Email protected]_opt+"'+@remotepath--print (@cmd)--execute command exec xp_cmdshell @cmdend--Delete Local backup bak fileSelect@cmd ='del'+@target_backupfile--print (@cmd)--execute command exec xp_cmdshell @cmdSelect@cmd ='del'+@target_backuplogfile--print (@cmd)--execute command exec xp_cmdshell @cmd--Delete a local backup 7z fileSelect@cmd ='del'+@target_archivefile--print (@cmd)--execute command exec xp_cmdshell @cmdSelect@cmd ='del'+@target_archivelogfile--print (@cmd)--execute command exec xp_cmdshell @cmd

--clear the database log create PROCEDURE [dbo]. [Cleardblog] @DataBase sysname asbegin--SET NOCOUNT on added to prevent extra result sets from--interfering with SELECT statements.        SET NOCOUNT on; DECLARE @sql2005 varchar (max) DECLARE @sql2008 varchar (max)Set@sql2005 ='DUMP TRANSACTION'[Email protected]+'With no_log; DBCC Shrinkdatabase ('[Email protected]+',0, truncateonly); '        Set@sql2008 ='ALTER DATABASE'[Email protected]+'SET RECOVERY Simple; DBCC Shrinkdatabase ('[Email protected]+',0, truncateonly); ALTER DATABASE'[Email protected]+'SET RECOVERY full; '--print (@sql); EXEC (@sql2008); Endgo

SQL Server Database backup method