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.