File transfer using SFTP

-

In most of the organization SMB port (445) which is used for file sharing is disable for security reasons. But file sharing over the network is one of the important factor withing infrastructure. (Ex: DR Sync, Remote file backup Etc.)

In this blog we will see how to transfer files from Local path to remote server over SFTP.

Secure File Transfer Protocol (SFTP) is a secure version of File Transfer Protocol (FTP), which facilitates data access and data transfer over a Secure Shell (SSH) data stream.

Ensure “WinSCP” is installed on system from where file will get copied.

Please find below script which established connection to destination file server.

 

#Script Name : script.txt

# Connect to SFTP server using a password

open sftp://<Username>:<Passowrd>@<IPAddress>/ -hostkey="<SSL Thumbprint>"

# Ex: open sftp://Devendra:Techni@123@10.256.85.45/-hostkey="ssh-ed4587 256ejfh5874HRJFO542545dgdgjsdkkd45588RALE555FHKA="

# Upload file

synchronize remote <SourcePath>\ /<SFTP_Path>/

# Ex: synchronize remote D:\SQL_Logs\ /SFTP/SQL/Daily/DB1/

# Exit WinSCP

Exit

 

Note: In above script we have used “synchronize remote” option instead of “put” Which will be synced local data with remote server.

“put” option copy all data from source to destination. Whereas, “synchronize” option only copy new / modified data. 

Ex:

Consider there are 5 files on local server which already have been copied on remote server. If new file is created or any exiting file is updated on local server, only those files will be replaced on remote server.

Above script need to be called from “*.bat” file which need to be placed on path where “winscp.com” utility is available.

Ex: C:\Program Files (x86)\WinSCP\SFTP_Copy.bat 

  

Code:

-- SFTP_Copy.bat

winscp.com /ini=nul /script= D:\Copy_Utility\script.txt /log=" D:\Copy_Utility\SFTP_logs\SFTPLog_!D-!M-!Y_!@.log" /loglevel=0 /logsize=5*10M


Above code will call “script.txt” file and logged the output in “SFTP_logs” folder.

Logs have been configured in such a way that it will limit log file size to 10 MB and will allow up to 5 archived logs. 

At the end, we need to schedule windows job to execute “SFTP_Copy.bat”. 

 

Ref :

https://winscp.net/eng/docs/commandline#logging

https://winscp.net/eng/docs/guide_automation

https://winscp.net/eng/docs/scriptcommand_put

https://winscp.net/eng/docs/scripting#commands

 

 

 

 


Comments

Post a Comment

Popular posts from this blog

What is DACPAC and how to use it?

-
Image
Few days before someone asked me a question about DACPAC and BACPAC. To be honest I was really not aware of it and I replied that I have heard these terms for the first time. That person replied DACPAC is used for deploying and upgrading database objects. Even after the clear statement, my reply was like developers would answer this better. Even though DBAs are responsible for migration and deployments. After few days, I got a requirement to migrate database schema (Only Schema) to another instance. In such a case most of us would have used the traditional way to script out the database objects and run the script on another instance. But what if there are many objects? I was exploring the easiest way to migrate the objects and got to know about DAC. After reading about DAC, I felt very sorry myself that I was not aware of such a simplest way of migrating and deploying database objects. I went to my friends who are senior developers and asked this question. None of them was aware ...
Read more

Backdoor in SQL server. Is this hacking, loop hole or Feature ?

-
Image
DBAs should always be ready to face any kind of disaster in SQL environment. That doesn't matter whether it is PROD or Non- PROD.  Recently I have been asked regarding recovering the sysadmin password. I couldn't understand the question hence investigated more to understand actual problem. Then I came to know that there is one SQL Server for which admin login and password is lost. I was Like seriously ? How one can be that much of irresponsible? But rather than getting into that kind of discussion I quickly got ready to help the team.  In SQL Server there is a way to access SQL Instance without login. Lets check that out. Steps : 1) Open SQL Server Configuration  Manager. 2) Right click on SQL Server service and go to Properties. 3) Go to "Startup Parameters" Tab and Add -m.   This will Start the server in Single user mode.  4) Click Apply and Ok. This change would require Service restart.  5) Once Service is restarted then, open CMD as Administrator. ...
Read more

Always on setup end to end - Part 1 || Pre-requisites

-
Image
Hello readers,  Welcome to the new blog after a long time. In this blog I would like to discuss complete end to end always on setup guide for beginners. I will be posting couple of blogs regarding always on in SQL server. I believe that would be helpful to understand Always on in SQL server in easy way. Always on setup involved multiple steps. Hence one may find it difficult. However, if you could plan it well then you may find that always on is much easier to configure and handle. So, without wasting the time lets proceed further. Firstly, we will see what is Always on in SQL Server ? Always on is the high availability and disaster recovery solution introduced in SQL server 2012. As the name suggest, Always on means availability of database all the time even in case of disaster. We can say it is the alternative of mirroring. Only thing is windows failover cluster service also required to be configured as a pre-requisite for always on. In this blog, we will only d...
Read more