Creating a Database Backup in Amazon S3
A database backup and recovery plan is always important to prevent data loss against system and media failure. In addition to storing a database backup on a network share or on another disk, SAP SQL Anywhere (SQLA) database administrators can use stored procedures to copy that backup to any cloud storage service, such as Amazon S3. Doing so allows the creation of automated backup plans that can be part of a cloud computing strategy.
This document describes how to create and run a SQLA stored procedure that stores a backed up database in Amazon S3. The stored procedure takes advantage of SQLA's external environment runtime that enables the database server to execute an external function that copies the database backup into a bucket in Amazon S3. This external function uses the Amazon Web Services (AWS) Software Development Kit (SDK) for .NET, although it's possible to write the function using other programming languages, such as Java or PHP.
- SAP SQL Anywhere - click here to download a free Developer Edition
- .NET SDK for Amazon Web Services
- Visual Studio to create the AWS .NET assembly
The procedure described in this document was tested using SQL Anywhere 16 running under Windows x64, but is also applicable to later SQLA versions and other operating systems.
The first step is to create an assembly for the .NET function. This function uploads all the files in a given directory, excluding files in sub-directories, to a specified bucket in Amazon S3.
- Open Visual Studio and create a new C# Class library. Add the Amazon Web Services assembly "AWSSDK.dll" as a reference. Type the following code and replace "YOUR-AWS-ACCESS-KEY" and "YOUR-AWS-SECRET-ACCESS-KEY" with your actual AWS access key and secret key.
public static void UploadDirectory(string directoryPath, string bucketName, string prefix)
string awsAccessKeyId = "YOUR-AWS-ACCESS-KEY";
string awsSecretAccessKey = "YOUR-AWS-SECRET-ACCESS-KEY";
TransferUtility transferUtility = new TransferUtility(awsAccessKeyId, awsSecretAccessKey, Amazon.RegionEndpoint.USEast1);
string bucketPath = bucketName + "/" + prefix;
- Build your class library.
- Move both "AWSSDK.dll" and the library you just compiled to the SQLA binary folder (default is %SQLANY16%\Bin32).
You now have a simple .NET assembly that copies a file from your computer to an S3 bucket.
Create a CLR Stored Procedure
SQL Anywhere supports Common Language Runtime (CLR) stored procedures and functions. A CLR stored procedure allows code written in a .NET programming language to run as a normal SQL stored procedure. All references included in your .NET assembly must either be in your path or in the Global Assembly Cache (GAC).
For more information on CLR external environment in SQL Anywhere, please refer to the SQL Anywhere documentation.
- Launch Sybase Central (click Start > SQL Anywhere 16 > Administration Tools > Sybase Central). Ensure you are using 'Sybase Central' and not 'Sybase Central (64-bit)'.
- Switch to the "Folders" view by clicking Folders from the View menu.
- Right click on "SQL Anywhere 16" and select Connect from the popup menu.
- The Connect dialog appears.
- Select the action "Connect with an ODBC Data Source".
- Type 'SQL Anywhere 16 Demo' as the data source name and click Connect. We will be doing a backup of the sample database, but you can use any database you like.
- Sybase Central is now connected to the sample database. In the Folders view, right-click on the demo database and select Open Interactive SQL.
- The Interactive SQL window appears. Type the following SQL code.
CREATE PROCEDURE clr_backup_directory_AWS(
IN directoryPath LONG VARCHAR,
IN bucketName LONG VARCHAR,
IN bucketPrefix LONG VARCHAR
EXTERNAL NAME 'AWSProject.dll::AWSProject.Program.UploadDirectory(string, string, string)'
- Click the Execute icon on the toolbar to create the procedure.
You now have a CLR stored procedure that uploads all files in a directory to an Amazon S3 bucket. The procedure takes in three parameters: (1) the location of the backed up database in your machine, (2) the bucket name in S3 that will store the backup, and (3) the folder name inside the S3 bucket where the backup files will be copied to.
Create a Maintenance Plan
You can now use the CLR stored procedure in your SQL code. You call it inside an EVENT statement, or use a maintenance plan as described below.
- Ensure you are connected to the SQL Anywhere 16 Demo database (as in previous section).
- In the Folders view, right-click on "Maintenance Plans" and select New > Maintenance Plan from the popup menu.
- The Create Maintenance Plan Wizard appears. Name the maintenance plan "backup_to_aws". Make sure the two boxes are NOT selected. Click Next to continue.
- Specify the start date of the initial backup (e.g. Wednesday at 9:00 AM). Click Next to continue.
- Check "Run the maintenance plan on the following" and choose "Days of the week". Select Sunday to schedule the maintenance plan to run every Sunday (you can choose any day you like). Click Next to continue.
- It's good practice to validate the database before backing it up.
- Check "Include a validation of the database in this maintenance plan".
- Check "Validate database pages".
- Select "Full Check" and click Next to continue.
- Perform a full-backup of the database.
- Check "Include a backup of the database in this maintenance plan".
- Select "Back up to disk".
- Select "Full image backup".
- Specify a directory to save the backup database and log. Click Next to continue.
- Optionally, you can add a script to validate the backup database every time the maintenance plan is run. For the purpose of this demo, we will not validate the database, but it's important to make sure your backup is valid. Click Next to continue.
- Let's save the result of the maintenance plan every time it runs.
- Choose Save only the last 5 reports.
- Check "Report the maintenance plan status to the server's console". Click Next to continue.
- We will not email the report when the maintenance plan is run, although it is something you may want to consider. Click Finish to close the maintenance plan wizard.
- In Folders View, expand Event and select backup_to_aws_event.
- Locate the Backup section (starts with "// Backup") and modify the script as follows. Notice how we are calling our CLR stored procedure.
UPDATE dbo.maint_plan_status SET "status" = 'BACKUP' WHERE plan_id = @PLAN_ID;
SET @MSG = 'Backup started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
SET @REPORT = @REPORT || @MSG || '\n';
DECLARE @FOLDERNAME LONG VARCHAR;
SET @FOLDERNAME = 'D:\\SQL Anywhere Demos\\BackupAWS';
SET @MSG = 'Backing up to image: ''' || @FOLDERNAME || '''';
SET @REPORT = @REPORT || @MSG || '\n';
BACKUP DATABASE DIRECTORY @FOLDERNAME
WAIT BEFORE START;
// Upload the Backup to Amazon S3
CALL clr_backup_directory_aws(@FOLDERNAME, 'sqla-backuptest', 'backup');
SET @MSG = 'Backup finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
SET @REPORT = @REPORT || @MSG || '\n';
- Click the Save icon on the toolbar to save the changes to the event. You now have a maintenance plan that performs a full backup of the demo database and uploads it to AWS.
Summary and Additional Resources
It's important to have a solid backup plan ready in case of media corruption. SAP SQL Anywhere provides tools to create events and stored procedures, as well as perform database validation, backup and recovery. A maintenance plan simplifies the process of scheduling the backup of a database and uploading it to the cloud. Remember to test your backup and recovery plan to ensure it executes as expected.
- SQL Anywhere CLR external environment: http://dcx.sybase.com/index.html#sa160/en/dbprogramming/pg-extenv-clr.html
- SQL Anywhere Backup and Data Recovery: http://dcx.sybase.com/index.html#sa160/en/dbadmin/da-new-backup.html
- SQL Anywhere Maintenance Plan: http://dcx.sybase.com/index.html#sa160/en/dbadmin/help-maintplan-task.html
- SQL Anywhere Community: http://scn.sap.com/community/sql-anywhere