How to backup and restore SQL databases in remote locations

Due to the threat of data corruption or loss through human error or malicious means, as well as hardware failure or even natural disasters such as fire or weather related situations, it is imperative that I.T. professionals ensure the safety of their data by saving and backing up their vital information in multiple locations using more than one method.

Every business either has been or has the potential to be affected by data loss whether this occurs as the result of a human error, such as opening email attachments infected with viruses or ransomware, as well as seeing any data saved locally destroyed in the event of hardware failure or environmental situations that include tsunamis, earthquakes or wildfire (all of which have caused widespread damage around the world in recent years). All of these will devastate any business that does not create backups of their vital business data in a remote location away from any local issues that can cause a loss or corruption of data.

Creating a remote backup:

The vast majority of business data is stored in Relational Database Management Systems such as Microsoft SQL Server or the open source MySQL and both of these systems offer a number of options for to backup SQL databases both locally and to remote locations using in-house systems and third party options.

SQL Server, which has recently been upgraded to a new 2016 version, allows a remote SQL backup to be created using SQL Server Management Studio, while Transact-SQL and Powershell can also be used for this backup process too.

There are a number of ways to ensure that the SQL Server backup is completed correctly and in the manner that you wish using these tools and Microsoft have provided a full breakdown of each of these methods here.

With MySQL being an open source software there are far more varieties for backing up to remote locations with options available such as the shell script AutoMySQLBackup that allows an SQL backup to be created via the mysqldump command, queries created using PHP and via SSH amongst a number of options available.

All of the above options are available with add-ons or within the database software themselves, but there are also third party options such as the Iperius backup software which allows you to create both local and remote MySQL and SQL Server backups in a simple and straightforward manner whilst allowing the backup files to be saved to a wide variety of remote locations. The details on both of these features can be read below:

SQL Server backup – MySQL backup

On top of these options there is also the option to create remote backups whilst being in a remote location too using the innovative Iperius Web Console.

Restoring a remote backup:

As with creating a remote SQL backup (either SQL Server or MySQL), there are multiple ways of restoring SQL databases from where they have been remoted saved.

With regards to a SQL Server backup there are three general ways of doing this with the two most commonly used manual methods being the restoration of the database from its remote location and also saving copying the .bak file to your local machine to complete the restoration process. The first of these two options is more fraught with danger as it can take a longer time to complete and the link is, in general, not as stable which can cause connection issues, thus corrupting the data, while users must also ensure that their SQL Server account has the correct permissions to allow this process.

The second of the two manual processes is the safer and quicker method of ensuring that your database is restored, but there is also an automated third option available too. Users are able to create an SSIS (SQL Server Integration Services) package to create this process and there is an excellent article available on how to create this package that can be read here.

For MySQL databases, once again the mysqldump command is king as this can be used in two ways to restore an SQL backup in the event of data loss. Using the command [mysql -u root -p[root_password] [database_name] < dumpfilename.sql] will restore the full database, while there is also quicker way of restoring data, although it will render the remote backup information as read-only. Users are able to view the tutorial on how to enact these two processes here.
Of course, Iperius also offers an even easier way of restoring MySQL databases that have been backed up using the software and details on how to do this can be read here.

(Italian, French, German, Spanish, Portuguese (Brazil))