If you run Microsoft SQL Server, you may wish to back up your databases to a cloud like Amazon Web Services (AWS). Doing so not only provides you with a virtually infinite amount of backup storage space in the cloud, but it can also make recovery easy in cases where you decide to restore your database using a cloud-based server.
In this article, we explain the main options available to backup SQL Server to Amazon S3. In particular, we’ll discuss backing up your data manually from a SQL Server database into S3, backing up data to AWS RDS and, finally, how to perform an image-based backup with the help of MSP360 Backup.
Table of Contents
Manually Backing Up SQL Server Databases to S3
One way to back up SQL Server to Amazon S3 is to perform a manual backup directly from Microsoft SQL Server to the AWS S3 storage service by following these steps:
- Create an S3 storage bucket on AWS.
- Configure a user account and permissions to access your storage bucket using IAM.
- In SQL Server, export the database that you want to back up into a file.
- Upload the exported file to S3. You can do this through the S3 interface, or you could use a tool like PowerShell (see here for details on connecting to an S3 bucket via PowerShell) or MSP360 Explorer, which lets you move data between local storage and S3 easily.
To connect to Amazon S3 with MSP360 Explorer, use these guidelines:
Further reading How to Sign Up for Amazon S3 Service and Connect to It with MSP360 Explorer
Pros and Cons
The approach described above is a relatively simple and easy way to backup SQL Server to Amazon S3. It is also one that you can achieve using only SQL Server and native Windows tools.
However, manually backing up SQL server databases to S3 storage has some downsides. The most obvious is that it requires significant manual effort, because you have to export your database and upload it manually. And while you could write a scheduled task in Windows to perform the backups automatically at a regular interval using PowerShell, you can’t automate this task using SQL Server alone.
Another significant disadvantage of the approach above is that recovering your data from S3 requires you to download the backed-up database file manually, then import the data back into SQL Server (or another database server). That process takes time and requires significant manual effort.
Manually Backing Up SQL Server Databases to AWS RDS
Although RDS and Amazon S3 are different services, migration of SQL Server databases to AWS RDS is another approach you might be interested in. RDS is a cloud-based relational database service where you can create databases whose structure closely resembles that of SQL Server databases.
AWS offers a detailed guide to this approach, so we won’t regurgitate their steps here. We will note, however, the pros and cons of this approach.
Pros and Cons
The pros are that this is a SQL Server database backup solution that anyone can perform relatively simply, provided they are familiar with both SQL Server and RDS. The cons are that this approach, like the one described further above, requires a significant amount of manual effort in order to perform the database backup. Further manual effort is necessary to restore data from an RDS database back to Microsoft SQL Server.
Recurring SQL Server Database Backups to Amazon S3 with MSP360 Backup
So far, the backup options we’ve looked at would have to be performed manually each time you want to backup SQL Server to Amazon S3. If you prefer to back up SQL Server databases automatically on a routine basis, MSP360 Backup can help you.
For full details check our article on Microsoft SQL Server Database Backup. The gist of it, however, is that you simply run the wizard and select S3 as your backup storage, as in the screenshot below.
In this way, MSP360 Backup makes it easy to backup SQL Server to Amazon S3 on a regular basis, and in a way that will allow you to recover the databases quickly if needed.
Configure the rest of the options suggested by the wizard and finish creation of the backup plan for your SQL Server backup to S3.
Image-Based SQL Server Backup to S3 with MSP360 Backup
MSP360 Backup also allows you to implement a cloud disaster recovery solution by performing an image-based backup. With this option, you back up your database to an EC2 virtual machine instance in the AWS cloud. Then, if you need to recover the data, you can restore it almost instantly by starting the instance, with just a few clicks inside MSP360 Backup.
To use image-based backup for SQL Server databases, follow the steps described below (or check our article Image-Based Backup and Recovery with MSP360 Backup for more details if necessary).
1In MSP360 Backup, create an image-level backup for the server with Microsoft SQL Server and point to AWS S3 as a target.
2In the next step, select the previously created S3 storage account as a target, click Next and then select Image Based Backup option.
3The Select Partitions step allows you to determine whether MSP360 Backup should create a backup of only selected partitions, only system ones, or a backup of all partitions present. Configure as desired.
We suggest accepting the Backup All Drives option only in situations where SQL data is stored on the separate partition. This will protect you from mistakes in the future.
4Configure the rest of the options suggested by the wizard and finish creation of the backup plan for your image-based backup of SQL Server to Amazon S3. For example, you may wish to take advantage of the block-level backup feature which lets you back up only modified image blocks, instead of backing up an entire database. This way, there is less data to transfer, so backups are completed faster.
SQL Server Restore to EC2
If you use MSP360 Backup to create an image-based backup of your Microsoft SQL Server databases to S3, you can quickly and easily restore the data by running the Restore to EC2 wizard within MSP360 Backup.
Follow the wizard steps as described in the guide called How to do a Full Server Restore to Amazon EC2 Using MSP360 Backup to complete the restoration.
Conclusion
As we’ve seen, there are several approaches available to backup SQL Server to Amazon S3. The main differences between them is the amount of effort they require to perform backups and to restore data. If you are looking for a simple way of SQL Server backup to S3 - and the one that will allow you to restore data fastest during disaster recovery - check a 15-day free trial of MSP360 Backup for MS SQL Server.
- Full, differential and transaction log backup
- On-site and off-site backup options
- Image-based and system state backup
