SQL Server Backup to/Restore from AWS S3 Storage
Posted by Rob Risetto on December 05, 2015
The use of AWS S3 storage to store SQL Server backups makes sense, firstly it is significantly cheaper than EBS, secondly you can encrypt what’s stored and thirdly you can manage backup file retention and lifecycle using inbuilt functionality.
Whether you decide to backup to local EBS disk first for performance then copy to S3 storage or backup directly to the S3 storage is dependent on whether you can easily and securely access the S3 storage from your SQL Server.
Wouldn’t it be good if you could just map drive to a S3 storage bucket and make it act like any other type of drive from Windows. Well you can. There are number of utilities that facilitate this function, in particular, I decided to take Cloudberry Lab’s Drive (server edition – US$ 59.99) for a test run and see if I can easily backup to an S3 storage bucket and then restore a database from it.
First you need to download the free trail from Cloudberry Lab Drive and install it on your SQL Server. The download site can be found (here). Note you will have to reboot the server after the installation, well at least I had to.
Next rather than using the primary (Root) account to access the S3 storage bucket I decided to create an IAM user and assign permissions to access the S3 bucket. So in the IAM option in the AWS console I created the DBBackup user and assigned two policies, one for the parent container and one for all containers (sub directories). I could have combined the policies but in my trial and error to get the permissions right I ended up with two policies.
I have a S3 bucket called dbinsightsqlbackup, so I granted the IAM user DBBackup full access to this bucket (parent container)
The second policy grants the user DBBackup full access to any sub directories.
Note I granted full access since I wanted the DBBackup user to create/delete/modify folders and files as required.
On the SQL Server we now have to configure the mapped drive. First you have to invoke the CloudBerry Drive configuration utility. Click on the show hidden icons on the task bar, right click on the CloudBerry icon and choose Options.
Click on the Storage Accounts and enter the Access Key for the DBBackup IAM user and click OK.
Next click the Mapped Drives tab, select a Drive letter, Label, and the S3 bucket name. Select Server Side Encryption and Mount this account as a virtual disk at system startup. Also select Mount as a Network Mapped Drive and click OK.
Click OK to exit the utility and then verify that the mapped drive is viewable.
I then ran a backup command using the Ola Hallengren backup utility, the command looked like this, I included the Verify option just to be safe.
@Databases = ‘ALL_DATABASES’,
@Directory = ‘Z:\’,
@BackupType = ‘FULL’,
@Verify = ‘Y’,
@Compress = ‘N’,
@CheckSum = ‘Y’,
@CleanupTime = 24
The command created the sub folders for the database backups and then the backup files as per normal, without error.
Restoring from a database backup on the S3 mapped drive is the same as standard drives, using the Restore Database function in SSMS, you can navigate to the backup file to select it for the restore.
You can also perform a restore using the TSQL RESTORE DATABASE command without issue.
restore database test from disk = ‘z:\sqlrds\test\full\SQLRDS_test_FULL_20140523_071206.bak’ with replace
A subsequent database integrity check for the test database came back clean.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘test’.
I would expect that backing up directly to S3 to work fine for small databases (< 10 GB) but to maximise performance I would recommend backing up to one or more EBS disk/s first and then use a PowerShell script to copy the backup files to the mapped S3 drive to maintain a reasonable file retention.
Like always, you should perform your own backup and restore tests on your data sets to ensure that the solution meets your data protection and performance requirements.