SQL Server : Copy backup files from one SQL Box to Another SQL Box using FTP

While working on a database migration project , we would require to transfer a huge backup file to new server which is running SQL Server 2012 but there was no trust enable between 2 different domain so normal copy operation was not allowed hence we thought to use FTP . I feel that there is always a need to FTP files to and from an SQL Server so I thought to make note of simple commands which will be useful in future.

FTP is an acronym for File Transfer Protocol. As the name suggests, FTP is used to transfer files between Servers/computers on a network.

Requirements:

1. FTP location name or IP @

2. User name & password ( In my case, I have used Anonymous FTP)

Anonymous FTP :

At times you may wish to copy files from a remote machine on which you do not have a loginname. This can be done using anonymous FTP. When the remote machine asks for your loginname, you should type in the word anonymous. Instead of a password, you should enter your own e-mail address. This allows the remote site to keep records of the anonymous FTP requests.

Note: However, you are only able to copy the files from the remote machine to your own local machine; you are not able to write on the remote machine or to delete any files there.

Step1:  Connect to server in which you want to copy the backup file ( Target server which is running SQL Server 2012)

Step2: Go to command prompt ( Run > cmd)

Step3 : C:\Users\Admin>FTP AB0010-SQL-FT

Connected to XXXXXXXXXXXXXXXXXXXXXXX

220 Microsoft FTP Service

User (AB0010-SQL-FT:(none)): anonymous

331 Anonymous access allowed, send identity (e-mail name) as password.

Password:

230 Anonymous user logged in.

ftp>

ftp> cd backup -- cd for change directory in remote server

250 CWD command successful.

ftp> pwd -- pwd means present working directory

257 "/backup" is current directory.

ftp> ls -- list all file and folder

ftp> lcd F:\  -- lcd to change directory in local server

Local directory now F:\.

ftp> lcd db_backup

Local directory now F:\DB_Backup.

ftp>

ftp> binary

200 Type set to I.

ftp> hash

Hash mark printing On  ftp: (2048 bytes/hash mark) .

ftp> get DB_Full_backup.bak

200 PORT command successful.

150 Opening BINARY mode data connection for DB_Full_backup.bak

ftp> bye

421 Timeout (120 seconds): closing control connection.

Last but not least, we can use put command instead get command to place backup file to source server.

Note: C:\Users\Admin>FTP -A AB0010-SQL-FT ( -A means Anonymous FTP)

Hope it will help you.

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)