Remote SQL backups are complicated. We will explain why and show all the available options to make the backups anyway. More…
About Ruslan Sudentas
Posts by Ruslan Sudentas:
The question “How to automate SQL Server database backups” has several answers and here we will review all of the best options. But first let’s define what SQL Server database backup automation stands for. SQL Server backup automation is a process that includes at least the following steps:
- Run SQL Server backup for selected databases on schedule
- Compress & encrypt the backups
- Upload the backup to a remote destination – network, NAS, FTP on one of the cloud storages (Dropbox, AWS, OneDrive, SkyDrive, etc..)
- Send email notification on backup success or failure
The most popular SQL Server backup automation options that we review here are:
Microsoft SQL Server Management Studio & SQL Server Agent
Ola Hallengren script
or you can just jump to the Conclusion
If you are looking for the tutorial on how to backup SQL database automatically with SSMS just follow the instructions below. Note however that there are better ways to backup SQL Server database than SSMS. This is especially true when you are running MS SQL Server Express that lacks SQL Server Agent – just use SQLBackupAndFTP instead of the method below.
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 why remote SQL backups are such a pain. More…
SQLBackupAndFTP creates standard *.bak files when it runs locally on the same computer where SQL Server is. For remote SQL Server computer backups SQLBackupAndFTP creates backup script files (*.sql). Whenever possible, you should always opt for *.bak backups against *.sql backup files.
Here we will show you how to backup a SQL Server on the network to the standard *.bak file even when SQLBackupAndFTP runs on a different computer from where SQL server is. The main requirement in this case is that both SQL server and SQLBackupAndFTP have access to the same shared network folder.
After several years of development the completely reworked v11 is now live. If you have used the previous version – start by reading:
SqlBackupAndFtp v11 – What’s New?
As before, you have several editions including free, but there are a few differences. See:
How SqlBackupAndFtp 11 differs from the previous versions?
For upgrade options from the previous version see:
How to migrate to SqlBackupAndFtp 11 from the previous versions
If you need help – go to Support Forum