SQL Regular automatic backup, encryption and compression of backup files, and automatic download
Release date:
Author: knife tip customer QQ: 51978456 MSN: peters99@hotmail.com
========================================================== ======= */
AsProgramStaff or database maintenance personnel may be frequent The thing to do is to back up the database, and it is best to back up the database remotely for security reasons. However, it is very time-consuming and laborious to back up the database frequently and upload them locally, especially lazy programmers like me sometimes forget the backup Pai_^ So a better solution is SQL Server Automatic Backup and automatic download. In addition SQL Server Automatically backed up Bak Files are usually relatively large. It takes a long time to directly upload the files to the local device. Therefore, it is best to compress the files after backup to improve efficiency, and encrypt the downloaded files for security reasons. Is it possible SQL Automatic Backup, encrypted compression, and automatic download are all completed automatically. The answer is yes. The following describes the tools, methods, andCodeShared for your reference.
The first step is SQL Server Automatically back up the database.ArticleThere are many, the most common is to use SQL Server To regularly execute a "job ". The basic step is to write a stored procedure that executes the backup, and then SQL Server In the Enterprise Manager, select "manage -- > SQL Server Proxy -- > Create job, and then select the type in the new job step Transact-SQL And then enter the stored procedure to execute the backup in the Command (or do not create the stored procedure, enter the backup SQL Code. The second step is to encrypt and compress the backup database files. I tried it myself. 200 m Of SQL Backup file, WinRAR The size after compression is 18 m Therefore, compression is necessary to quickly upload data to the local device. To compress the data immediately after the backup, you can use it directly. SQL Command Execution compression, because WinRAR It can be called through the command line, so we can use SQL Server In Xp_mongoshell Command to execute the compression command, and specify the compression password through some of the parameters, so that the encryption compression is achieved. (To implement this function, install WinRAR ) At last, automatic download is required. To implement this function, you can use the Windows Task Plan and the built-in FTP command. The complete steps and code for implementing this function are as follows: 1, Create a stored procedure in the database to be backed up: Create procedure [do_backup] asdeclare @ prefix nvarchar (100), @ datefile nvarchar (100), @ bakfile nvarchar (100 ), @ rarfile nvarchar (100), @ delcmd nvarchar (100), @ rarcmd nvarchar (150) set @ prefix = 'e: \ Bak \ databasename \ '-- backup folder set @ datefile = 'fzjs' + Cast (Year (getdate () as varchar (4) + Cast (month (getdate ()) as varchar (2) + Cast (Day (getdate () as varchar (2) set @ bakfile = @ prefix + @ datefile + '. bak 'set @ rarfile = @ prefix + 'rar \ 'Folder @datefile='.rar 'backup database databasename2 to disk = @ bakfile with init, nounload, name = n' databasename data backup', noskip, stats = 10, noformat set @ delcmd = 'del' + @ prefix + 'rar \*. RAR' -- First clear the RAR backup folder set @ rarcmd = 'C: \ progra ~ 1 \ WinRAR \ winrar.exe A-pyourpw '+ @ rarfile + ''+ @ bakfile -- change yourpw to the password you want to set and encrypt and compress the exec master .. xp_mongoshell @ delcmdexec master .. xp_mongoshell @ rarcmd go 2, Database Manager -- manage --> job --> create job -- set the job execution program and running time, set according to your backup needs, and then enable the job. 3, Set the directory for data compression and backup in the FTP server. 4, Create a local FTP command text file: C: \ ftp.txt and enter the following content (replace it with your own content and remove []): open [your server IP address] [database backup compression directory FTP login user name] [database backup compression directory FTP login password] verbose offlcd [Local save path] prompt offmget *. rarbye 5, Create an autoftp. bat batch file and execute the FTP command. FTP-S: C: \ ftp.txt 6, In the task scheduler management on the control panel, add the Scheduled Automatic running time of the batch file, and note that it should be consistent with the backup time of the server database. In this way, after successful debugging, the entire process will be successful, and you will not need to do these tedious tasks frequently in the future! If you have any questions or better methods, please contact me.
