How to backup remote SQL Server database using SQLBackupAndFTP

You can run scheduled backups of a remote SQL server using SQLBackupAndFTP. The standard BACKUP DATABASE command would not work on a remote SQL Server, so scripts would be the only option. Scripts are not as good as *.bak files because they are larger, do not support differential backups, and should be used only if you do not have an option to create *.bak files. If you want to know more – read a good explanation of why remote SQL backups are such a pain.

Local SQL Server backup is well documented and we won’t get into the details of it here.

For remote or hosted SQL Server instances you can not use the BACKUP DATABASE command since this command would create a *.bak file somewhere on the local drive. And you generally do not have access to that location so getting the backups file to your local PC is problematic.  So in the case of remote SQL server instances, SQLBackupAndFTP backs up databases by creating script *.sql files. The files consist of T-SQL statements like CREATE and INSERT that re-create database objects and the data wherever the script is run.

What hosting providers are supported? Basically, if they allow remote connection to SQL Server and you can connect to your hosted database from your local computer using SQL Server Management Studio – you should be able to back up your databases using SQLBackupAndFTP.

The most difficult task is to connect to your remote SQL Server. Check your hosting provider on how to connect remotely and use SSMS first to check that connection works.  Then run SQLBackupAndFTP on your local computer, click “Connect to Server”, select “Remote SQL Server” as a Server type and enter the same server name, user name, and password you’ve used in SSMS.

After successful connection, you can configure the rest of SQLBackupAndFTP options: what databases to backup, where to store the backups (network, NAS, FTP, Dropbox, Amazon S3, Google Drive, OneDrive, Azure, etc). Schedule when to run and where to send email confirmations. See the tutorial for other SQLBackupAndFTP options.

How to restore SQL server backup scripts?

Restoring scripts is very simple. To restore on the same server where the backups were taken, in the History & restore section of SQLBackupAndFTP select the backup you want to restore, click the dots button on that line, select “Restore from Backup…” and follow the steps.

To restore to any other SQL server, just run the script like you would run any on SQL command and it will recreate the database.

14 thoughts on “How to backup remote SQL Server database using SQLBackupAndFTP

  1. How well does remote restore work with databases with referencial integrety (RI)?
    If integrety can not be inforced which state is database left in? Date restored but RI disabled? Or just abort ?

    1. Hi Jesper,

      For a consistent import-export, it is recommended to create a snapshot of the database. You can find that option in the “Backup options” section.

  2. “So in the case of remote SQL server instances, SQLBackupAndFTP backs up databases by creating script *.sql files. The files consist of T-SQL statements like CREATE and INSERT that re-create database objects and the data wherever the script is run.”

    This does not work for me. The remote backup only creates a SQL script containing the database create statement. There are no scripts regarding tables and inserts generated.

    1. Hi Rob,

      Could you please go to the “Microsoft SQL Server Remote Backup Options” window (by clicking the “More Settings” link at the “Backup option” section at the bottom of your backup job’s setting) and make sure that all options are enabled or just click “Reset to default” link. Also, please make sure that the user who makes the backup has enough permissions to view the data in the tables.

  3. Hello,

    quick question: I want to create a restore procedure for MySQL db on a weekly basis. My question is: what will happen with the restore task if the destination database already has data on it? Will it overwrite them and write the new data or stop the restore task?

  4. I tryed a “local backup” using a (remote) servername. I can connect to the Mssql instance and the backup (full + diff+ log) were fine, except the programm cannot find the remote path. That’s exactly the behaviour expected.
    Now….I think that would be possible to add some options to grab the remote bck (via ftp, ssh, samba share ecc) from the machine running sqlbackup.

    Explanation
    SqlServer on machine 192.168.1.100
    SqlBackup on machine 192.168.1.200
    On SqlBackup I use “local” server and connect to “remote” (that’s fine, I can actually do with SqlBackup&Ftp)
    On SqlBackup I can do full backup and the file goes to SqlServer in /var/opt/mssql/data (it’s a linux machine)
    On SqlBackup there must be an option like “connect using ssh/ftp/whatever” and two fields like “user” and “password”, so SqlBackup&Ftp can connect to SqlServer via ssh (or other) and grab the bck and then move to the backup location (store backup in selected destination)

  5. Hello,

    We have different databases with different IaaS providers. It would not make sense to download 50 GB of database backups centrally every day, only to upload them directly to an S3 backup (100 GB Traffic). Is it possible to trigger the backup centrally and upload it directly from the remote server to the S3?

    1. Hello Miro,

      You can attempt to synchronize the backup job schedules to run simultaneously.

      If you have any further questions, please don’t hesitate to reach out.

      Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *