RDS MSSQL backup via lambda. Generate .bak file in S3.
Authorized users can request RDS SQL backup via a webpage URL.
- Steps
- 1. The user connect to URL and get available databases list
- 2. Then selects the database name to backup and submits the backup request
- 3. The backup is processed and .bak file is generated in S3
- 4. SNS topic (Email/SMS) is triggered once the file is created in S3 bucket
Front-End
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>RDS BACKUP</title>
<link rel="stylesheet" href="./style.css">
<link href='https://fonts.googleapis.com/css?family=Lato&subset=latin,latin-ext' rel='stylesheet' type='text/css'>
<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/abcd/m4=" crossorigin="anonymous"></script>
<script>
function getAPI(e) {
e.preventDefault();
$.ajax({
type: "GET",
url : "https://m1234***.execute-api.us-east-1.amazonaws.com/prod/gettable",
dataType: "json",
crossDomain: "true",
contentType: "application/json; charset=utf-8",
data:{},
success:function (data) {
var data1= JSON.parse(data)
var s = '<option value="-1">-- Select the database to backup --</option>';
for (var i = 0; i < data1.length; i++) {
s += '<option value="' + data1[i].name + '">' + data1[i].name + '</option>';
}
$("#mycar").html(s);
// $("#firstbtn").className = 'txt';
$("#firstbtn").style.color = "#4CAF50";
},
error: function () {
alert("UnSuccessfull !!!");
}});
}
function submitToAPI(e) {
e.preventDefault();
var table_name = $("#mycar").val();
var data = {
table_name : table_name
};
$.ajax({
type: "POST",
url : "https://m1234***.execute-api.us-east-1.amazonaws.com/prod/backup",
dataType: "json",
crossDomain: "true",
contentType: "application/json; charset=utf-8",
data: JSON.stringify(data),
success: function () {
$("#message").text("Successfull .. --- \
Backing up: " + table_name).fadeIn();
$("firstbtn").style.color = "#ff0000";
//location.reload();
},
error: function () {
// show an error message
alert("UnSuccessfull");
$("#message").text("Unsuccessfull ...\n " + table_name).fadeIn();
$("#firstbtn").style.color = "#888888"
}});
}
</script>
</script>
</head>
<body>
<form id="contact-form" method="post">
<h1><div class="txt">
<img src="images/XMPieLogo.svg" style="vertical-align:middle; width: 50px; height: auto;"> <span style="display: inline-block; margin-left: auto; margin-right: auto ">XMPie Utility</span></div></h1>
<BR><BR>
<fieldset class="fieldset">
<legend class="txt"> RDS Backup Tool - PRIVATE </legend>
<BR><BR>1. Click the refresh button to display available databases
<BR><BR>2. Select the database you want to backup and click the Submit button
<BR><BR>3. A processing message will be displayd and an Email Notification will be sent to you once the backup is complete
<BR><BR>4. Click the URL from the email to download your .bak file
<BR><BR><BR><BR>
<button type="button" onClick="getAPI(event)" class="btnrefresh"></button>
<select id="mycar" class="ddl">
</select>
<BR><BR>
Message: <div id="message" style="display:none;"></div>
<BR><BR>
<!-- <div class="g-recaptcha" data-sitekey="1234567890" class="form-control" style="width:100%;"></div> -->
<BR><BR><button type="button" onClick="submitToAPI(event)" id="firstbtn" class="btn">Submit</button>
<BR><BR>
</fieldset>
</form>
<BR><BR><BR><BR>
</body>
</html>
Lambda function RDS2
import boto3
import pyodbc
import json
import datetime
import os
from base64 import b64decode
s3 = boto3.client('s3')
rds = boto3.client('rds')
ENCRYPTED = os.environ['RDS_CONNECTION']
DECRYPTED = boto3.client('kms').decrypt(
CiphertextBlob=b64decode(ENCRYPTED),
EncryptionContext={'LambdaFunctionName': 'myRDS2'}
)['Plaintext'].decode('utf-8')
def lambda_handler(event, context):
# server = 'mika-rds1.cvfwlqirxxt9.us-east-1.rds.amazonaws.com'
conn = pyodbc.connect(DECRYPTED)
cursor = conn.cursor()
# TIMESTAMP
mydate1= datetime.datetime.utcnow() + datetime.timedelta(minutes=180)
mydate = mydate1.strftime("%Y-%m-%d--%H-%M-%S")
def myconverter(o):
if isinstance(o, datetime.datetime):
return o.__str__()
print(mydate)
mytable = '{}'.format(event['table_name'])
print('\n ')
print('Requested on: ' + mydate)
print('RDS Table Name: ' + mytable)
print('\n ')
formatedtable = mytable + "--"
#backup sp
count = cursor.execute("""
exec msdb.dbo.rds_backup_database @source_db_name="""
+ mytable +
""",@s3_arn_to_backup_to='arn:aws:s3:::mika-us-east-1/fromLAMBDA--"""
+ formatedtable + mydate +
""".bak',
@overwrite_S3_backup_file=1;
""")
conn.commit()
print('Rows inserted: ' + str(count))
a = 'XMPie received your backup request and is now processing the Table backup. /n'
b = 'Date: '
c = '/n RDS Table Name: '
return a + b + mydate + c + mytable
Lambda function RDS0
import boto3
import pyodbc
import json
import datetime
import os
s3 = boto3.client('s3')
rds = boto3.client('rds')
from base64 import b64decode
ENCRYPTED = os.environ['RDS_CONNECTION']
# Decrypt code should run once and variables stored outside of the function
# handler so that these are decrypted once per container
DECRYPTED = boto3.client('kms').decrypt(
CiphertextBlob=b64decode(ENCRYPTED),
EncryptionContext={'LambdaFunctionName': 'myRDS0'}
)['Plaintext'].decode('utf-8')
def lambda_handler(event, context):
conn = pyodbc.connect(DECRYPTED)
cursor = conn.cursor()
# cursor.execute("exec msdb.dbo.rds_backup_database @source_db_name='RDS2', @s3_arn_to_backup_to='arn:aws:s3:::mika-us-east-1/eee001.bak', @overwrite_S3_backup_file=1;")
# row = cursor.fetchone()
# while row:
# a = [row[0],row[1],row[2],row[5],row[6]]
# print(a)
# row = cursor.fetchone()
# return a
# DATE TIME
mydate1= datetime.datetime.utcnow() + datetime.timedelta(minutes=180)
mydate = mydate1.strftime("%Y-%m-%d--%H-%M-%S")
def myconverter(o):
if isinstance(o, datetime.datetime):
return o.__str__()
print(mydate)
# mytable = '{}'.format(event['table_name'])
print('\n ')
print('Requested on: ' + mydate)
print('\n ')
# Query ######################
b = 'Date: '
print('---- Get MSSQL query -- start ----')
cursor.execute("SELECT name FROM sys.databases WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource','rdsadmin')")
row = cursor.fetchone()
# while row:
# a = row[0]
# print(a)
# row = cursor.fetchone()
print('---- Get NEW ----')
myresult = cursor.fetchall()
for x in myresult:
print(x)
print('---- Get MSSQL query -- end ----')
return x