1

I can connect to a remote SQL Server via Management Studio. But, when I create backups, they get created on the server's disk.

How can I transfer them to an FTP server?

I only have a login to the MS SQL Server & the FTP site where I need to save the backup file.

Improve this question
5
  • Seems like you are in the Old generation(SQL 2005) :). Create a shared folder on the server where your SQL server is installed, or you can create the shared folder anywhere it should accessible from the SQL server. Then give that network path as the backup location. Most of the DBAs recommend to use ola hallengren backup scripts
    – TheDataGuy
    Commented Dec 25, 2017 at 8:42
  • Yes, the Remote MS SQL Server is 2005. I only have SQL Server url, Username, Password. I cannot create shared folder, that is why i need to FTP the backup to a different site.
    – Shailesh
    Commented Dec 25, 2017 at 9:04
  • 1
    If your SQL user has sysadmin access, just give a try to create a shared folder using PowerShell with the help of xp_cmdshell. use xp_cmdshell and Share folder using powershell
    – TheDataGuy
    Commented Dec 25, 2017 at 9:08
  • what about this virtualobjectives.com.au/sqlserver/ftp_scripts.htm . Do you think this can work?
    – Shailesh
    Commented Dec 25, 2017 at 9:14
  • Not sure how it'll work., but give a try (if you have a dev or staging, then test it there first.)
    – TheDataGuy
    Commented Dec 25, 2017 at 9:16

2 Answers 2

Reset to default
1

You have two processes here that are discrete,

  1. Dumping the database.
  2. Moving the dump to an FTP server.

There are numerous ways to accomplish both of these. You'll have to ask a more specific question about either one.

FTP is an old antiquated transfer protocol that's highly insecure and you should never use it unless you absolutely must. Instead, consider using something like rsync over SSH. Microsoft has jumped on SSH with the latest version of Windows 10 providing an install option from the Windows Store, and it's likely the most secure method of doing what you're after: if you have control over both the server and the client, I would be using that method for transfer.

Improve this answer
0

Looks like, the only way is to create scripts and then upload them to your FTP server. It won't be a genuine database backup but at least you will be able to recreate the database on another machine.

Since you can connect via SSMS, you can do it right there as described in Microsoft's docs:

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, and then expand the database containing the object to be scripted.

  3. Expand the category of the object. For example, expand the Tables or Views node.

  4. Right-click the object, point to Script as, For example, point to Script Table as.

  5. Point to the script type, such as Create to or Alter to.

  6. Select the location to save the script, such as New Query Editor Window or Clipboard.

Another option is to use tools like SqlBackupAndFtp (disclosure: it's my product) that will script your database and send it to FTP server right away.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.