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.
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 ?
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.
what is version of mysql or phpmyadmin support?
Hello,
We support versions 4.6, 5.2, but, in general, we try to support other versions. Before the purchase, you can test the 14-day Professional Trial and check if it works for you https://sqlbackupandftp.com/download
“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.
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.
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?
Hi Leonidas,
Yes, the data will be overwritten, the old data will be removed.
Thank you!
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)
Hi DaSCoS,
Thank you for the details.
To backup SQL Server on Linux we recommend you to use SqlBak on Linux, please find more details at https://blog.sqlbak.com/how-to-backup-and-restore-sql-server-on-linux
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?
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!
Hola, quisiera consultar si la licencia sirve para un servidor o se pueden uar en mas maquinas.
saludos
Hi Jaime Rojas,
Could you please reach out to our support team https://sqlbackupandftp.com/support for more details?
Thank you!