Sqlserver remote backup (ftp Transfer)

Source: Internet
Author: User
Tags ftp transfer
Sqlserver remote remote backup Problem description the customer's sqlserver database server automatically backs up the T1 database every morning, and now requires that the backup files be automatically uploaded to a storage server (with FTPSERVER) at the same time solution 1. develop a window service program to copy the backup files of the Local Machine (Database Server) to the FTP

Sqlserver remote remote backup Problem description the customer's sqlserver database server automatically backs up the T1 database every morning, and now requires that the backup files be automatically uploaded to a storage server (with FTPSERVER) at the same time solution 1. develop a window service program to copy the backup files of the Local Machine (Database Server) to the FTP

Sqlserver Remote Backup

Problem description

The customer's sqlserver database server automatically backs up the T1 database every morning, and now requires that the backup files be automatically uploaded to a storage server (with FTPSERVER) at the same time.

Solution

1. Develop a window service program to upload the backup files of the Local Machine (Database Server) to the storage server through FTP every morning.

2. Use sqlserver's xp_mongoshell tool to call the FTP command to upload the backup file to the storage server.

Method

Solution 1 is not described

The second solution is implemented as follows:

1. confirm that the Storage Server FTP service is available and that the user name is confidential.

2. Use the following SQL code to enable the xp_cmdshell permission (sqlserver 2005 is disabled by default)

Sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
Sp_configure 'xp _ Your shell', 1
GO
RECONFIGURE
GO

3. Create the ftpstep file ftpconfig.txt under the SQL Server database server C: \ dbbackup \. The contents are as follows. The second line of the first line of username is confidential.

123
123
Put C: \ dbbackup \ *. tempBAK
Close
Quit

4. Test the following SQL code in the SQL Server database query analyzer. Assume that the database name is testdb backup location C: \ dbbackup Storage Server IP address 192.168.0.44.


Declare @ tempfilename varchar (100)
Set @ tempfilename = CONVERT (varchar (8), GETDATE (), 112) + CONVERT (varchar (2), DatePart (Hour, GETDATE ())) + CONVERT (varchar (2), DatePart (Minute, GETDATE () + CONVERT (varchar (2), DatePart (second, GETDATE ()))
Print @ tempfilename
Declare @ tempfilename1 varchar (100)
Set @ tempfilename1 ='C: \ dbbackup\ Testdb '+ @ tempfilename +'. tempBAK'
Backup databaseTestdbTo disk = @ tempfilename1
EXEC xp_mongoshell 'ftp-s: c: \ dbbackup \ ftpconfig.txt 192.168.0.44 '-anonymous
Declare @ temp varchar (100)
Set @ temp = 'Ren C: \ dbbackup \ testdb' + @ tempfilename + '. tempBAK' + 'testdb' + @ tempfilename + '. Bak'
Print @ temp
EXEC xp_cmdshell @ temp

5. Create a maintenance plan on the sqlserver database and automatically execute part 4 of the SQL

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.