Automatic data backup and upload

I have been working on a group project. The data of each subsidiary needs to be uploaded to the group server every day, so I wrote an automatic backup.ProgramAnd upload through FTP (Or network ing) To the service stored procedures, with backup also hope to help everyone.CodeAs follows:

 

Automatic data backup and automatic data backup and upload

--------------------------------------------- Back up and upload the database to the Ftb server ----------------------------------------
Create proc proc_backup
As
Begin
Declare @ yyyymmdd char (8)
Declare @ upyyyymmdd char (8)
Declare @ cmd1 varchar (200) -- compress the backup file and delete the original file.
Declare @ cmd2 varchar (200)
Declare @ cmd3 varchar (200) -- delete a data file a month ago
Declare @ sfolder nvarchar (1000) -- folder, which stores the compressed file after backup, that is, the backup path
Declare @ scommandtext varchar (255)

Declare @ filename varchar (50)
Declare @ backname varchar (50)
Declare @ copypath varchar (200) -- Temporary Folder

Set @ backname = n' Database Backup'
Set @ sfolder = 'e: \ backup '-- folder to be determined
Set @ yyyymmdd = convert (varchar (8), getdate (), 112) -- current date
Set @ upyyyymmdd = convert (varchar (8), dateadd (M,-2, getdate (), 112) -- date before a month
Create Table # temp (A int, B INT, C INT) -- temporary table

Insert # temp exec master .. xp_fileexist @ sfolder
If not exists (select * from # temp where B = 1)
Begin
Set @ scommandtext = 'mkdir' + @ sfolder
Exec xp_cmdshell @ scommandtext
End
Drop table # temp

If (substring (@ upyyyymmdd, 7,2) = '02 ') -- perform full backup on the 2nd of every month
begin
set @ filename = @ sfolder + '\ filiale. bak'
backup log database with no_log -- truncate log files
DBCC shrinkdatabase (' database ') -- shrink database
backup database database to disk = @ filename -- backup the database
with init, nounload, name = @ backname, noskip, stats = 10, noformat
set @ cmd1 = '"C: \ Program Files \ WinRAR \ rar.exe" a-DF-EP1 E: \ backup \ filiale_'{@yyyymmdd}'_0.rar E: \ backup \ filiale. bak'
set @ cmd2 = 'e: \ backup \ filiale_'{@yyyymmdd}'_0.rar '

set @ cmd2 = 'Copy E: \ backup \ filiale_'{@yyyymmdd}'_0.rar Z: \ filiale.rar '-- Use Network ing
set @ cmd3 = 'del E: \ backup \ filiale _ '+ @ upyyyymmdd +' * '-- delete the data file a month ago
exec master .. xp_mongoshell @ cmd3
end
else
begin
set @ filename = @ sfolder + '\ filiale. cha '
backup database database to disk = @ filename -- completes differential Database Backup
with differential, format
set @ cmd1 = '"C: \ Program Files \ WinRAR \ rar.exe" a-DF-EP1 E: \ backup \ filiale_'{@yyyymmdd}'_1.rar E: \ backup \ filiale. cha '
set @ cmd2 = 'e: \ backup \ filiale_'{@yyyymmdd}'_1.rar'

Set @ cmd2 = 'Copy E: \ backup \ filiale_'{@yyyymmdd}'_1.rar Z: \ filiale.rar '-- Use Network ing
End
Exec master .. xp_mongoshell @ cmd1 -- compress the backup file
Set @ copypath = 'copy' + @ cmd2 + 'e :\~ Temp \ filiale.rar '---------------------
Exec xp_cmdshell 'mkdir E :\~ Temp'
Exec master .. xp_mongoshell @ copypath

Set @ scommandtext = 'echo open FTP Server IP Address > '+ 'E: \ a.txt' -- specifies the FTP server ---------------------
Exec master .. xp_mongoshell @ scommandtext
Set @ scommandtext = 'echo Login Name > '+ 'E: \ a.txt' -- Logon Name --------------------------------
Exec master .. xp_mongoshell @ scommandtext
Set @ scommandtext = 'echo Password > '+ 'E: \ a.txt' -- logon password ------------------------
Exec master .. xp_mongoshell @ scommandtext
Set @ scommandtext = 'echo LCD E :\~ Temp> '+ 'e: \ a.txt' -- local path (directory of the backup file)
Exec master .. xp_mongoshell @ scommandtext
Set @ scommandtext = 'echo bin> '+ 'e: \ a.txt' -- convert to binary transfer, improving the speed
Exec master .. xp_mongoshell @ scommandtext
Set @ scommandtext = 'echo put E :\~ Temp \ filiale.rar> '+ 'e: \ a.txt' -- transfer to the FTP Server Directory --------------------
Exec master .. xp_mongoshell @ scommandtext
Exec master .. xp_mongoshell 'echo bye' -- end
Exec master .. xp_mongoshell 'echo quit' -- exit

Set @ scommandtext = 'ftp-S: e: \ a.txt'
Exec master.. xp_mongoshell @ scommandtext -- execute the generated batch file
Exec master.. xp_cmdshell 'del E: \ a.txt '-- delete temporary files
Exec master .. xp_mongoshell 'del E :\~ Temp \ *. rar '-- delete a Temporary Folder
Exec master .. xp_mongoshell 'RD E :\~ Temp \ '-- delete a Temporary Folder

Exec xp_cmdshell @ cmd1
Exec xp_cmdshell 'net use Z: // 192.168.2.5/databak"Ytsoft"/User: 192,168, 2.5 \ admin' -- create a network ing
Exec xp_cmdshell 'del Z: \ gahs.rar '-- first delete the files on the server
Exec xp_cmdshell @ cmd2 -- copy to the target server
Exec xp_cmdshell 'net use Z:/delete' -- delete network ing

End

Go

 

After the stored procedure is completed, call it in the job.

I am at a limited level. Please make a picture and hope to help you! Next, we will continue to restore the database.