How to backup remote SQL Server database

Using SQLBackupAndFTP you can run scheduled backups of a remote SQL server. 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.

Local SQL Server backup

Before getting into remote SQL backups, lets review the “local” SQL backups.

SQL servers on the same computer

When installed locally on the same computer where the SQL Server is (this is the recommended way of installation), SQLBackupAndFTP backs up databases using a standard BACKUP DATABASE command. For each database you get a single *.bak file that you can restore using the standard RESTORE DATABASE command.

SQL servers on the same network

If you run SQLBackupAndFTP on your computer and want to backup a SQL Server somewhere on the same network, SQLBackupAndFTP can still produce the standard *.bak file as long as both SQL Server and SQLBackupAndFTP can access the same shared network location – see details on how to make it happen.

Remote SQL server backups

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 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 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.

[Total: 1    Average: 5/5]