SqlBak Blog

How to backup remote SQL Server database using SqlBak

With the help of SqlBak, you can backup your remote SQL Server databases according to your schedule. A standard BACKUP DATABASE command doesn’t work on a remote SQL Server — only scripts can be generated. If there is a choice between script generation or performing backups in *.bak files, it is always recommended to run the standard BACKUP DATABASE command to get *.bak files. Scripts have some drawbacks: they are larger and do not support differential and transaction log backups. A more detailed explanation about remote backups can be found in the following blog post.

If you have to backup databases on your hosted or remote SQL Serves instances, then the BACKUP DATABASE command cannot be used, as it creates a *.bak file somewhere on the local drive. It can be a real problem to get the backup file from that location as, generally, users do not have access to that storage. So in that case, SqlBak creates script *.sql files to backup remote SQL Server instances. The script file consists of T-SQL statements, like CREATE and INSERT, which re-create data and database objects wherever the script is run.

What hosting providers are supported? That is a reasonable question. As a rule, if you can set a remote connection to the SQL Server and connect to your hosted databases from your local computer via SSMS (SQL Server Management Studio), you’ll be able to backup your databases via SqlBak.

Connecting to your remote SQL Server is the most difficult task. Before connecting via SqlBak, check with your hosting provider about the way to establish a remote connection and test it via SQL Server Management Studio to see if it works.

Run the SqlBak app, click on the “Add” button and choose the “Microsoft SQL Server (remote)” server type. At the opened window, specify the same server’s name, user’s name and password you use to connect via SSMS.

After the connection is established, you can create and configure your backup job, where you can choose the databases that should be backed up, destination places for storing the backups (network, NAS, FTP, Azure, Google Drive, Dropbox, OneDrive, Amazon S3, etc.), create a schedule, set the email notification, and perform many other tasks.

How to restore SQL server backup scripts?

The scripts can be restored very easily. To restore the last backup on the same server, just click on the “restore” link on your “Dashboard” page in the “LAST RUN” column

or you can choose the required one from the backup job settings page in the “Backup history” section.

 

If you need to restore on any other SQL Server manually, just run the script the same way you run any SQL command and your database will be recreated.

Leave a Comment