Automatic data backup and upload

Source: Internet
Author: User

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.