Remote SQL backups are complicated. We will explain why and show all the available options to make the backups anyway.

Remote SQL Server is a server where you have limited access to the server’s file system like in a shared hosting environment.
Local SQL Server is a server where you have full access to the file system like a local server you can physically access or a dedicated/virtual server that you have full control of.

What is the problem with Remote SQL Server backups?

The main issue with remote SQL servers is that you can run SQL scripts, including BACKUP DATABASE command, but accessing the resulting *.bak file is problematic. The *.bak file is placed somewhere on the drive local to that SQL server. And if you don’t access to that location, you can not copy the backup file for further processing like compression, encryption, storage, etc. (more details on what SQL backup automation means)  Then your options are limited to creating a script file.

BAK (*.bak) file vs Script (*.sql) file for SQL Server backups

BAK files are the backups created using Microsoft’s standard BACKUP DATABASE command (or through SSMS or SQLBackupAndFTP). They usually get *.bak extension. If you have an option to create *.bak files – always prefer it to the alternatives because it allows you to store not only Full, but Differential and Transaction Log backups as well. And the format is the most common and not proprietary to any 3rd party. You can restore from (*.bak) file using standard RESTORE DATABASE command and many 3rd party tools.

Script (*.sql) file is basically a set of SQL commands that re-create the database objects and insert the data into the tables. The benefit of using a script file is that most hosting environments let you upload script files and run them by using a hosting administration control panel. Then you can restore a database without requiring anything to be installed or configured by the hoster.

However, Script (*.sql) method has the following limitations:

1. It takes much longer to generate a script than to create a standard BAK type of SQL Server backup
2. Such script takes more space since it is a textual (not binary) representation of your database, though it is much more compressible than a binary backup.
3. It takes more time to restore a database from a script that from a regular backup
4. Unlike standard backups, scripts do not contain transaction log information so you can’t apply point-in-time recovery
5. Scripting doesn’t always take dependencies into account, so you may face problems during the database recreation

Local SQL Server backups

Local SQL Server backup is well documented and we won’t get into the details of it here. Basically, you run BACKUP DATABASE command, use SQL Server Management Studio or 3rd party tools. This creates a *.bak file on Server’s local file system. Then you usually compress the file, encrypt it, upload it to a network drive, FTP, or a cloud, etc. You can build your custom scripts to do it or get it all in a product like SQLBackupAndFTP.

Remote SQL Server backups to a *.bak file on a network share 

If your remote SQL server is on the same network as you and both SQL Server and you have access to the same network share (like \\servername\path), then on the SQL server you can backup to this location with the command like:

BACKUP DATABASE dbname TO DISK = N’\\servername\path\dbname.BAK’

And from your own computer you would be able to access the same location, take the *.bak file and do what you want with it.

You can write your own scripts to automate it or backup your network SQL Server using SQLBackupAndFTP.

The benefit of backups to UNC path is that you get the backup in the same coveted *.bak format. The downside is basically related to the need to configure access rights correctly. Visit the link above for details on permissions.

Remote SQL Server backups to a script (*.sql) file

A generated SQL Server script (*.sql) backup file contains the information that is necessary to re-create the database on a remote computer. The script includes commands to re-create the database schema (tables, views, stored procedures, triggers, full-text catalogs, roles, rules, and so on) and the data. You have several options to generate a script (*.sql) backup file

Scripting database with SQLBackupAndFTP

This is by far the simplest method to backup your remote databases. Just select “Remote SQL Server” as a server type set login credentials:

Then configure compression, encryption, where to send backups, and where to send email notifications. See more details in How to backup remote SQL Server database using SQLBackupAndFTP

Scripting database with SQL Server Management Studio (SSMS)

Note that this works as an ad hoc “backup”, but you can not schedule SSMS scripting. To generate a database script with SSMS:

1. Open your SSMS
2. Connect to your remote SQL server
3. Right-click on the database you wish to backup and select Tasks -> Generate Scripts. This opens a Wizard
4. Hit Next on the Introduction screen
5. Leave the default “Script entire database and database objects” selected and hit Next
6. Click button Advanced and change “Types of data to script” from “Schema only” to “Schema and data”. Hit OK
7. Select the option to “Save to a new query window” and hit Next, Next, and Finish

Copy the script to your local file and store or run it wherever needed.

Other options for remote SQL Server backup

If using the word “backup” in regards to scripting was quite a stretch, the options discussed in this section are even further from what is considered to be a “database backup”. Still, it allows you to get some copy of your data, that is why we will review these options as well

Moving data with SQL Server Import and Export Wizard in SSMS

You can use SQL Server Import and Export Wizard to copy the data from the remote SQL server to a local SQL server. Note that it will NOT copy all database objects, but only the data. We can not imagine using it as a main backup method. Anyway here are the instructions:

1. Open your SSMS
2. Connect to your local SQL server
3. Right-click on the database where you want the backups to be restored to and select Tasks -> Import data. This opens a Wizard
4. Choose “SQL Server Native Client” as a data source and your remote SQL server as a server name.
5. Select the database to copy the data from. Next.
6. Choose “SQL Server Native Client” as a data source and your local SQL server as a server name.
7. Select the database to copy the data to. Next.
8. Select the tables to copy. Next, Next, Finish

 

Conclusion

If you can, take local backups as *.bak files. Otherwise to automate remote SQL backups, use SQLBackupAndFTP.