BACK UP SQL SERVER TO AMAZON S3
This tutorial walks through backing up SQL Server databases to Amazon S3 with SQL Backup Master – from setting up an S3 bucket and access keys, through creating and scheduling a backup job, to running it and verifying the results.
Prerequisites
Before starting, you'll need:
- SQL Backup Master installed on a Windows host that can connect to your SQL Server instance.
- An Amazon Web Services (AWS) account. If you don't have one yet, visit the AWS homepage and complete the sign-up and verification process.
- A SQL Server login (or Windows account) with sufficient permissions to back up your databases.
Step 1: Create an S3 bucket
Sign in to the AWS Management Console and open the S3 service. Create a new bucket to hold your database backups – choose a globally unique bucket name and a region close to your servers, and leave public access blocked (the default). The exact console layout changes over time, but bucket creation is always available from the S3 service's main bucket list.
Step 2: Create access keys
SQL Backup Master authenticates to S3 with an access key and secret key pair. Create these from the AWS security credentials area – ideally for a dedicated IAM user whose permissions are limited to the backup bucket, rather than for your root account. Record both the access key and the secret key; AWS only shows the secret key once, at creation time.
Step 3: Create a new backup job
In SQL Backup Master, open the Backup and Restore tab and click New Backup. The Database Backup Editor window will appear – this is where you'll configure the source databases, destination, and schedule for the job.
Step 4: Connect to SQL Server
Click Choose SQL Server to open the SQL Server connection dialog, then enter your SQL Server instance name in the Server name field. For example:
- (local)\SQLExpress – connects to a local SQL Express instance running under the default instance name.
- (local) – connects to a local SQL Server instance with no instance name.
- Server1\SQLExpress – connects to a remote SQL Express instance on a server named Server1.
- Server1 – connects to an unnamed remote SQL Server instance on a server named Server1.
You can also use the server name dropdown to detect available SQL Server instances automatically. Click Test SQL Connection to verify connectivity, then click OK.
Step 5: Select databases to back up
The Database Backup Editor now lists the databases available on your SQL Server instance. Check the boxes next to the databases you'd like to back up. Alternatively, enable the Back up all non-system databases option to have SQL Backup Master automatically include newly created databases in future backup runs.
Step 6: Add the Amazon S3 destination
In the Destinations section, click Add to open the list of available backup destinations, then double-click Amazon S3. In the Amazon S3 Destination Settings window, enter:
- Access key – the access key you created in Step 2.
- Secret key – the matching secret key.
- Bucket – the name of the S3 bucket you created in Step 1.
Click Test to verify the connection to Amazon S3, then click OK. Back in the Database Backup Editor, click the Save icon to store the backup job settings.
If SQL Backup Master runs on an EC2 instance, it can also authenticate to S3 with an IAM role instead of stored keys – see the Amazon S3 destination help page for details. S3-compatible storage services from other providers are supported as well – see the S3-compatible services overview.
Step 7: Schedule the backup job
To run backups automatically, configure a recurring schedule for the job – for example, a nightly full backup. Scheduled jobs run unattended through the SQL Backup Master service, so no one needs to be logged in. You can skip this step if you only want to run backups manually.
Step 8: Run and verify the backup
In the main SQL Backup Master window, locate the backup job you just created and click Back up now. SQL Backup Master will back up the selected databases and upload the results to your Amazon S3 bucket. When the job completes, review the job log to confirm success, and check the bucket in the AWS console to see the uploaded backup files. If anything goes wrong, the application logs contain detailed troubleshooting information.