Sqlserver remote backup (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