But you'll need to have a way to back up your data, as it's crucial to ensuring the functionality of the production site and the stability of everyday workflows. So creating Azure SQL backups can help you and your team avoid data loss emergencies and have the shortest possible downtime while maintaining control over the infrastructure.

Another reason to have a current Azure database backup is Microsoft’s policy. Microsoft uses the shared responsibility model, which makes the user responsible for data integrity and recovery while Microsoft only ensures the availability of its services. Microsoft directly recommends using third-party solutions to create database backups.

In case you run a local SQL Server, you'll need to prepare for the possibility of hardware failures that may result in data loss and downtime. An SQL database on Azure helps mitigate that risk, although it's still prone to human errors or cloud-specific threats like malware.

These and other threats make enabling Azure SQL database backups necessary for any organization using Microsoft’s service to manage and process data.

In this tutorial, you'll learn about backing up Azure databases and restoring your data on demand with native instruments provided by Microsoft, including methods like:

• Built-in Azure database backup functionality
• Cloud archiving
• Secondary database and table management
• Linked server
• Stretch Database

Why Backup Your SQL Azure Database?

Although I covered this briefly in the intro, there are many reasons to back up your SQL Azure database data.

Disaster Recovery

Data centers can be damaged or destroyed by planned cyberattacks, random malware infiltration (check out this article to discover more on ransomware protection), and natural disasters like floods or hurricanes, among others. Backups can be used to swiftly recover data and restore operations after various disaster cases.

Data Loss Prevention

Data corruption, hardware failure, and accidental or malicious deletion lead to data loss and can threaten an organization. Backup workflows set up to run regularly mean you can quickly recover the data that was lost or corrupted.

Compliance and Regulations

Compliance requirements and legislative regulations can be severe regardless of your organization’s industry. Mostly, laws require you to keep up with security and perform regular backups for compliance.

Testing and Development

You can use backups to create Azure database copies for development, troubleshooting, or testing. Thus, you can fix, develop, or improve your organization’s workflows without involving the production environment.

How to Back Up Your Azure SQL Database

Backing up your Azure SQL database can be challenging if you go through the process without preparation. So that's why I wrote this guide – to help you be prepared. Here's what we'll cover in the following sections:

• Requirements for SQL Azure database backup
• How to configure database backups in Azure with native tools
• Cloud archiving
• Backup verification and data restoration

SQL Azure Database Backup Requirements

Before backing up your SQL Azure databases, you need to create and configure Azure storage. Before you do that, you'll need to go through the following steps:

First, open the Azure management portal and find Create a Resource.

Then, go to Storage > Storage account. Provide the information, including the location and names of a storage account and resource group according to your preferences. After you enter the information, hit Next.

Storage account config

Then go to the advanced section for additional settings. The optimal choice is to set _"_Secure transfer required" as Enabled and "Allow access" from All networks. For more resilience in case of human error, you can set "Blob soft delete" as Enabled. With that setting, you can quickly correct accidental deletions in the storage account.

After that, specify the tags you need to simplify navigating through your infrastructure.

Azure backup storage tags

Check the settings once more. If everything is configured correctly, hit Create. Your new storage account is now created.

Once the storage volume is created, it's time to configure a backup data storage container.

Go to the storage account, find Containers, then hit the + Container tab there. After that, specify a name for the new container and switch Public access level to Private (no anonymous access).

Container Azure storage account

You can then use the container as a backup storage (.bak files will be stored there in that case).

Azure Database Backup Configuration

Now, everything is set up for you to back up your SQL Azure database. Do the following to create a database backup:

First, go to SQL Management Studio, and establish a connection with the SQL server. After that, right-click the database that should be backed up. The context menu appears, so go to Tasks there. Then hit Back Up….

SQL server tasks backup

Then find the Destination tab, and set Back up to line to URL there. After that, hit New container.

Next, sign in to Azure. Pick the container you created before. Provide your credentials, then hit OK.

You’ll see a message asking you to sign in to Azure subscription. Then, choose the container and hit OK.

Now, you'll see the configured backup destination URL listed. To start the workflow to back up your Azure data, hit OK once again.

When your SQL Azure database backup is completed, the message shows up: "The backup of database ‘your database name’ completed successfully."

The backup file in the target container should now be visible from the Azure portal.

Keep in mind that, when uploading backups to any cloud storage, you may face issues if your network connection is not fast enough.

In case that’s true for you, you can reorganize your backup workflows: send backup data to a physical storage drive first, and then send another copy to the cloud. Thus, you can prevent operational challenges that might appear due to network bandwidth deficiency.

Cloud Archiving for Azure Database Backups

Databases tend to grow in volume as the organization grows. This means that the storage space required to fit the data and that data's backup increases significantly. Also, the original data volume prolongs the duration of full backup workflows, posing another challenge.

Of course, the first way to get more storage space is to revise your data regularly and erase records that are irrelevant, outdated, or unnecessary otherwise. Still, it's sometimes difficult to determine if data will be or become unnecessary or irrelevant, especially when dealing with issues of compliance.

To keep your organization compliant in any case, data archiving can help you solve two problems at once: you can ensure data accessibility on one hand, and save storage space on the other hand.

To archive your SQL database in the cloud, you should first save that database copy to an Azure blob container. Then, to move a newly created blob to the archive tier in the Azure portal, do the following:

1. Go to the required container where the SQL database is stored.
2. Choose the blob that you need to move.
3. Hit Change tier.

Azure blob container change tier

4. In the Access tier dropdown menu, choose Archive.

Azure blob change tier

5. Hit Save.

Additionally, the Archive storage tier is the most affordable one in Azure, meaning that you can reduce your database data TCO with it.

Secondary Database and Table Management

There exist several workflows that can help you set up Azure database backup archiving for your organization. When you need the data to stay in the initial database, for instance, creating a separate table and moving that data there can be your choice. However, the filegroup of that table should stay apart from the main database and be moved to a separate disk whenever possible.

Most probably, you’ll want to let users access the data you send to a separate table. To make that happen, you can create a view merging the relevant tables and redirect the requests to that view, not to the original table. Doing things that way, you can keep the data accessible while dealing with maintenance faster.

SQL Server Linking

If you can’t move the data to another database for internal reasons such as special Azure backup policies, you can consider maintaining your primary database accordingly.

Here, the outcome is likely to be that of the previous case, but you need to link the SQL servers or configure apps so they can send direct requests to your second server.

The downside here is that your SQL database, which was supposed to be a backup one, becomes a production database and gains appropriate importance for an organization.

There are two ways to create linked servers via SQL Server Management Studio (SSMS):

• sp_addlinkedserver (Transact-SQL) system stored procedure that creates a linked server
• SSMS GUI

After you've ensured that you have appropriate access rights on both server instances you need to link, the network is configured appropriately to access them, and SSMS is installed, you'll need to go through the following steps:

First, open SSMS.

Microsoft SSMS

Connect to the instance where you need to establish a linked server. Then find Object Explorer > Server Objects, then right-click Linked Servers.

Pick New Linked Server from the dropdown:

New linked server SSMS

Then configure the server properties, including name, server type, provider and product name:

Linked server configuration SSMS

Then you'll just need to complete the security configuration, set up the server options, and complete connection testing.

Original Data Deletion

When you don’t need 24/7 data availability but need the data stored due to internal policies or compliance requirements, you can choose what's probably the simplest solution to increase storage space efficiency. Just back up the data that can stay unavailable and then delete the originals from the main database. Accessing any records you may need will still be possible via the backup.

Stretch Database

Aiming to make data management of organizations’ databases simpler, Microsoft implemented a Stretch Database feature in SQL Server 2016. With this feature, you can get an SQL backup to Azure after you send the data from the hosted database to an Azure SQL database. The method enables you to increase overall infrastructure cost-efficiency by simplifying backup workflows.

To enable this workflow in your environment, develop the policy specifying the data on a hosted server to send to Azure. You don’t need to introduce any changes in applications that use the production database: SQL Server can independently get the records from the Azure SQL Database.

Azure Database Backups Verification and Restoration

During an SQL Azure database backup, you can choose to create such backups WITH CHECKSUMS or without them. When the workflow is complete, I recommend you use the following command: RESTORE VERIFYONLY. This command enables you to check the recoverability of backup files.

To access the data, you can restore records from a backup to a different database. With Azure Automation scripts on backups, you can accelerate the restoration process, thus minimizing downtime and increasing the overall resilience of your Azure infrastructure.

You need to follow only a few steps to restore an Azure SQL database to a required recovery point from a backup. Still, keep in mind that your subscription can define the available retention period which can vary from 7 to 35 days. A native tool for backup restoration to SQL servers is Server Management Studio.

To Conclude

The critical nature of Azure SQL database data makes Azure SQL backups obligatory for any organization that uses this Microsoft solution. In this guide, we reviewed the process of creating SQL Azure database backup using native Microsoft tools.

These tools provide data backup, backup verification, and recovery functionality along with some automation.

You can also implement a specialized all-in-one data protection solution, such as NAKIVO, the company where I work. It can help you make your data backup workflows more efficient.

But manually backing up a database can be time-consuming and error-prone, especially if you have multiple databases to back up.

In this article, we will explore how to automate SQL database backups using Python, making the process faster, easier, and less error-prone.

Prerequisites

Before we get started, you will need to have the following installed:

• Python 3.x

• pip

• The pyodbc package (for connecting to SQL databases)

• The pandas package (for working with data)

• A SQL database to back up

Step 1: How to Connect to the SQL Database

The first step in automating SQL database backups is to connect to the database using Python. We will use the pyodbc package to connect to the database and execute SQL commands.

Here is an example code snippet that connects to a SQL Server database:

import pyodbc

# Connection parameters
server = 'localhost'
database = 'mydatabase'
username = 'myusername'
password = 'mypassword'

# Create a connection object
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)

# Create a cursor object
cursor = conn.cursor()

In this code, we create a connection object using the pyodbc.connect() method and pass in the connection parameters. We then create a cursor object using the conn.cursor() method, which allows us to execute SQL commands on the database.

Step 2: How to Create a Backup

Once we have connected to the database, we can create a backup using the BACKUP DATABASE SQL command.

Here is an example code snippet that creates a full backup of a SQL Server database:

import os

# Backup directory
backup_dir = 'C:/backup'

# Backup file name
backup_file = 'mydatabase_backup_' + str(datetime.now().strftime('%Y%m%d_%H%M%S')) + '.bak'

# Backup command
backup_command = 'BACKUP DATABASE mydatabase TO DISK=\'' + os.path.join(backup_dir, backup_file) + '\''

# Execute the backup command
cursor.execute(backup_command)

In this code, we specify the backup directory and file name and use the os.path.join() method to create a full file path. We then create the backup command using the BACKUP DATABASE SQL command and execute it using the cursor object.

Step 3: How to Save Backup Details

After creating a backup, it's a good idea to save some information about the backup, such as the backup file name, backup date and time, and the database name. We can save this information to a CSV file using the pandas package.

Here is an example code snippet that saves backup details to a CSV file:

import pandas as pd

# Backup details
backup_details = {'database': [database], 'backup_file': [backup_file], 'backup_datetime': [datetime.now()]}

# Create a DataFrame object from the backup details
backup_df = pd.DataFrame(data=backup_details)

# Backup details file
backup_details_file = os.path.join(backup_dir, 'backup_details.csv')

# Write backup details to a CSV file
backup_df.to_csv(backup_details_file, index=False)

In this code, we create a dictionary object containing the backup details and create a DataFrame object from it using the pd.DataFrame() method.

We then specify the backup details file using the os.path.join() method and write the backup details to a CSV file using the to_csv() method.

Step 4: How to Automate the Backup Process

Now that we have created a backup and saved backup details, we can automate the backup process using a Python script. We can schedule the script to run at regular intervals using the built-in Windows Task Scheduler or a third-party scheduling tool like CronTab (for Linux) or Task Scheduler (for Mac).

Here is an example Python script that automates SQL database backups:

import pyodbc
import os
import pandas as pd
from datetime import datetime

# Connection parameters
server = 'localhost'
database = 'mydatabase'
username = 'myusername'
password = 'mypassword'

# Backup directory
backup_dir = 'C:/backup'

# Create a connection object
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)

# Create a cursor object
cursor = conn.cursor()

# Backup file name
backup_file = 'mydatabase_backup_' + str(datetime.now().strftime('%Y%m%d_%H%M%S')) + '.bak'

# Backup command
backup_command = 'BACKUP DATABASE mydatabase TO DISK=\'' + os.path.join(backup_dir, backup_file) + '\''

# Execute the backup command
cursor.execute(backup_command)

# Backup details
backup_details = {'database': [database], 'backup_file': [backup_file], 'backup_datetime': [datetime.now()]}

# Create a DataFrame object from the backup details
backup_df = pd.DataFrame(data=backup_details)

# Backup details file
backup_details_file = os.path.join(backup_dir, 'backup_details.csv')

# Write backup details to a CSV file
backup_df.to_csv(backup_details_file, index=False)

In this script, we have combined the previous code snippets into one script, making it easy to automate the backup process.

We first connect to the database, create a backup, save backup details to a CSV file, and then disconnect from the database.

Conclusion

Automating SQL database backups using Python is a great way to save time, reduce the risk of errors, and ensure that data is always protected.

By following the steps outlined in this article, you can easily automate SQL database backups and schedule them to run at regular intervals.

Remember to test your backups regularly to ensure that they are working correctly and that you can restore data when needed.

Let’s connect on Twitter and Linkedin

I also write on Hashnode, but recently I was thinking about whether there is any way to backup my published articles to GitHub. Fortunately, I have found an easy way to do that directly from Hashnode itself.

So, in this article, I will share the process so that you can also create an automatic backup of your articles to your GitHub repository. There's no harm in trying something new, right? 😊

✨ I have also created a full length video showing the process of creating that automatic backup to your GitHub repo. I have attached the video later in this article. You can also check that out.

Firstly, let me show you my Hashnode account. This is just a sample of a typical profile there.

I have published only one article (as of today, 15 May 2022). But I still want to create an automatic backup process for this account so that whenever I write something new, all of the articles get copied into my selected GitHub repository as a backup.

So let's do that, shall we? 😁

How to Backup Your Hashnode Articles to GitHub

Go to your GitHub account. We need to create a special repository first where we will create an automatic backup process.

Now simply click on the + button on the top right of the webpage.

Now click New repository.

Now we have to create a new repository. The process is similar to creating any other repository.

You can make the repository Public or Private as you see fit for yourself. I am making it a Private repository, but you do not have to do that if you don't want to.

Give the repository any name you want.

For the latter, you can select anything you want. For this article, I am keeping it simple as it was.

Then click Create repository.

After that, you actually do not need to do anything in that repository for now.

So, I am keeping it as it is right now.

Now, head over to your Hashnode account.

Click your profile icon on the top right of the profile page.

Select the blog you want to backup. For me, it is https://fahimbinamin.hashnode.dev/.

Click Blog Dashboard.

The blog dashboard will appear before you.

Simply scroll down until you find Backup. You will find that on the lower left side of the webpage. Click on that.

You will find the GitHub Backup page now.

Click on Back up all my posts.

Select where you want to install it.

As I am involved in 5 GitHub organizations right now, it's suggesting all of them to me. As I want to create the backup on my personal GitHub account, I will select my profile FahimFBA.

The install and authorize section will appear.

By default, it will select "All repositories". But keep in mind, that you shouldn't select All repositories, as that will rewrite all of the repositories you currently have in your personal GitHub account.

Select "Only select repositories", and find the repository which you have created for backing up your Hashnode blogs.

Simply click and select the repository.

If everything is okay, then you can click "Install & Authorize", but again keep in mind that you must not select All repositories.

Provide your GitHub account's password if it asks for that.

If will redirect you to Hashnode to continue the installation process.

You've now created your automatic backup. If you want to back up the existing posts, then you have to click "Back up all my posts".

It will backup every post you had before creating the automatic backup.

Now, if you simply refresh the GitHub repo webpage (the repo you created only for backing up this Hashnode blog), you will see that it has been updated as well!

You can even read the article directly from your backed up repo.

That's it!

If you want to watch a video that covers the whole process, then you can check out the following video that I made:

Conclusion

Thanks for reading the entire article. If it helps you then you can also check out other articles of mine at freeCodeCamp.

If you want to get in touch with me, then you can do so using Twitter, LinkedIn, and GitHub.

You can also SUBSCRIBE to my YouTube channel (Code With FahimFBA) if you want to learn various kinds of programming languages with a lot of practical examples regularly.

If you want to check out my highlights, then you can do so at my Polywork timeline.

You can also visit my website to learn more about me and what I'm working on.

Thanks a bunch!

But sometimes when you're trying to back up or retrieve files from an external hard drive, the drive might not show up. There are several possible reasons for this, like lack of power, faulty drivers, or hardware issues.

In this guide, I will show you 5 ways you can fix an external drive not showing up.

How to Fix Your External Hard Drive Not Showing Up by Plugging it In

Your hard drive might not show up because it has little or no power supply to it.

A lot of hard drives are optimized to receive their power supply through USB ports, while some others have separate power sources.

If you're using one that gets power through a USB port, it'll have a light (usually blue) that indicates that it's receiving power. If this light isn't turned on, it means the hard drive isn't receiving power.

If this is the case, you should make sure the USB cord fits in correctly, or you can try another USB port.

How to Fix Your External Hard Drive Not Showing Up by Trying Another USB Port or Computer

Computers usually come with up to 3 USB ports, or at least 2. So, if your external hard drive is not showing up while plugged into a particular USB port, you should try another one.

You can also try using your external hard drive on another computer in case your computer has faulty drivers or damaged USB ports. If the hard drive works on another machine, you can look into fixing your computer.

How to Fix Your External Hard Drive Not Showing Up by Updating Drivers

If your external hard drive fails to show up on your computer, you can try updating or reinstalling your computer's USB drivers.

Follow these steps to update your USB drivers:

Step 1: Right-click on Start (Windows logo) and select "Device Manager". You can also launch Device Manager by searching for it and then clicking on the "Device Manager" search result.

Step 2: Expand the Disk drives option.

Step 3: Right-click on the device and select "Update drivers".

Step 4: Choose "Search automatically for driver software".

Windows will now search online for driver updates and install them on your computer.

How to Fix Your External Hard Drive Not Showing Up by Changing the Drive Letter in Disk Management

On almost all cases, Windows automatically assigns all external drives an appropriate letter.

If the hard drive hasn't been assigned one, it will not show up, so you need to do it yourself. And even if it's already assigned a letter, changing it might resolve the issue for you.

Follow the steps below to assign your hard drive a letter:

Step 1: Right-click on Start (Windows logo) and select "Disk Management".

Step 2: Right-click on the external hard drive and select "Change Drive Letter and Paths…".

Step 3: If the drive has an existing letter, then click "Change".

Step 4: Select your preferred letter from the drop-down menu.

Step 5: Click Ok, then Ok again.

How to Fix Your External Hard Drive Not Showing Up by Formatting the Hard Drive in Disk Management

Formatting is a common way of fixing issues with external disks, so when your hard drive refuses to show up, you should try formatting it.

Be aware that formatting the external drive will erase all the data in it – so this may not be a viable or your first option if your drive is full of data.

The steps below will help you format your external hard drive.

Step 1: Right-click on Start (Windows logo) and click on "Disk Management".

Step 2: Right-click on your drive and select "Format".

Step 4: Click "Ok" to run the format.

Conclusion

In this article you learned 5 different ways you can get your hard drive to show up when you plug it into your computer.

In addition to the fixes suggested in this article, consider updating Windows 10 as well because it fixes a lot of issues.

If you find this article helpful, don't hesitate to share it with your friends and loved ones so they can confidently fix this issue in case they experience it.

Thank you for reading.

This was the Linux workstation that was host to thirty years-worth of data: The original working drafts of all my books. The master versions of my course videos. My tax records, banking information, password vault, and the access keys to my cloud infrastructure.

Was I surprised? Not particularly. The day before I’d been struggling with a broken Python package and I knew there was a chance it wouldn’t end well the next time I fired up the machine.

Was I annoyed? Yup.

Did I break into a cold sweat, thinking about what was lost and whether I’d ever recover? Nope. That was never a concern.

In fact, from the moment I decided that the original installation was no longer worth fighting for, it would only take an hour or so to get everything back up and running. (Not counting the time it took me to remember that a known hardware conflict required I disable a non-free Nvidia driver.)

Let me emphasise that: I erased the corrupted drive, installed a clean copy of Ubuntu Linux, and wrote fresh, reliable copies of around 20GB of data to the new installation in less than a single hour.

Besides having a fast fibre optics internet connection, what’s my secret sauce? I’m constantly backing up all my important data to multiple storage locations. When catastrophe hits, I have a solid, tested recovery protocol in place.

For all intents and purposes, the workstation part of that protocol involves installing my OS and then, with just two or three commands, restoring all my data to its new home. From that moment, I’ll be back to work.

I’m sure this story has left you overcome with relief and warm, sympathetic feelings. But what’s it all got to do with IT security? More than you might imagine.

The fact is that there many reasons backup discussions belong here, but if you had to limit yourself to just one, this common and timely scenario (adapted from my recent “Linux Security Fundamentals” book from Wiley/Sybex) would be it:

Imagine you’re responsible for the IT systems powering the municipal services for your small town. Without those computers and their data, municipal workers won’t get paid next month, the local library won’t know where any of their books are, the 911 emergency service communication system’s phones won’t ring, and the town’s information website will go offline.

Now imagine that one fine morning you log into the main server and you’re greeted by the cheerful news that all the data on your systems has been encrypted by a hacker from Eastern Europe and that they won’t give you the decryption key to restore your access unless you pay them a couple hundred thousand dollars’ worth of cryptocurrency. Don’t think this is realistic? Major hospitals, utilities, and entire small cities have been brought to their knees by just such attacks.

What are your choices?

• You could pay the ransom and hope the attackers keep their promise to decrypt your data. But, historically, they often haven’t. Criminals aren’t known for being honest.
• You could try using decryption tools provided by major security companies and government agencies (like https://noransom.kaspersky.com/ ) and hope that they’ll work on your system. This is certainly a valid option, but it won’t work in all cases.
• You could wipe your systems clean and rebuild everything from scratch. This could be hugely expensive and take months to complete.

But do you know how you can stop the attack cold and walk away virtually untouched? If you had complete, up-to-date backup copies of your systems (both the user data and the application systems themselves), then all you’ll need to do is rebuild from your backups.

Worst case, you’re down for an hour or two, and few people even notice. Even better, you could plan things really well by designing an always-running “hot” backup infrastructure that’s preconfigured to go live the minute the main system goes down. It’s known as failover, and it’s the kind of plan that can make you a big hero and earn you a big raise.

Still not sure what backups have to do with security?

That Linux Security Fundamentals book also describes how to properly assemble all the parts your recovery plan will need. That’ll include a careful assessment of precisely how important your data is to both you and the organization you work for.

Here’s how my book describes RTOs and RPOs:

How “quick” is quick enough and how “complete” is complete enough? That will depend on your organization’s operational needs. It’s common for administrators to measure their needs in terms of a recovery point objective (RPO) and recovery time objective (RTO). An RPO is the system state you need to be able to recover that will be current enough for your organization’s minimum requirements. So, for instance, if your recovered system will have data that includes all but the last hour preceding the crash, you’ll be able to get by. But a loss of two hours of data would be catastrophic; the financial or reputation loss you’d face would be too serious. For such an organization, you’d better make sure you have an RPO of one hour or less.

An RTO, on the other hand, is a measure of how soon you need to get your system back up and running at full speed before really bad things start happening to your organization.

By way of example, suppose your ecommerce site was offline for 12 hours. You’ll lose some business, obviously, but your business analysts tell you that anything up to 48 hours is still livable. More than 48 hours, however, and customers will assume you’re down for good and head over to the competition (which, all things being equal, will be Amazon).

Therefore, when you plan your backup regimen, you’ll take both the RPO and the RTO into account. You’ll need to make sure a new backup is run within the RPO (say, one hour) and also make sure you can access your backup archives and successfully restore the data to the applications in less than the RTO (48 hours, in our example).

Sure, RTOs and RPOs are usually applied to enterprise infrastructure workloads. But, on many levels, the underlying point can also apply to our own beloved workstations and laptops.

If you take some time now — today — to plan, create, and test your own recovery protocol, you can be sure that one day soon, you’ll thank yourself.

You can find much more technology content by David Clinton through his website. In particular, you might enjoy his new book, Keeping Up: Backgrounders to all the big technology trends you can’t afford to ignore.

This guide was created because it was so hard to find a way to play around with a sample database using AWS RDS MSSQL Server. I hope you find this helpful.

If you haven't set up your AWS RDS Microsoft SQL Server and Azure Data Studio, check this guide first: How to Connect your AWS RDS Microsoft SQL Server using Azure Data Studio.

We will be touching the technologies shown below:

• Database: AWS RDS Microsoft SQL Server Express Edition
• Database tool and GUI: Azure Data Studio
• Sample database backup copy: Amazon S3 Bucket

AdventureWorks sample database backup copy

To get the OLTP downloads of AdventureWorks, go to this link and choose any sample database. In my example, I choose AdventureWorks2017.bak. We will upload this to the S3 Bucket.

Amazon S3 Bucket

Creating the S3 Bucket

1. Create a bucket. You can choose any bucket name (example: yourname-sample-dbs).

2. Make sure the region is same as the AWS RDS instance.

3. Tick the following checkboxes:

4. Block public access to buckets and objects granted through new access control lists (ACLs)

5. Block public access and objects granted through any access control lists (ACLs)

4. Access your bucket again by clicking on your created bucket.

Uploading the file to the S3 bucket

1. Click Upload.

2. Choose the database backup file. For example: AdventureWorks2017.bak. Keep choosing Next and choose Upload at the Review section.

3. Update your Bucket Policy to allow access to your S3 Bucket. Note that your ARN will differ to mine. Hit Save afterwards.

{
    "Version": "2012-10-17",
    "Id": "Policy1548223592786",
    "Statement": [
        {
            "Sid": "Stmt1548223591553",
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::changethis/*"
        }
    ]
}

AWS RDS - MSSQL Server Express

Creating an Option Group for your RDS instance

1. Click Option groups,

2. Create an option group. Choose any name and description. For the Engine, it should match your RDS instance. In my example, I used SQL Server Express Edition so I choose sqlserver-ex.

Here are the following Engines and their abbreviations:

• SQL Server Enterprise Edition: sqlserver-ee
• SQL Server Standard Edition: sqlserver-se
• SQL Server Web Edition: sqlserver-web
• SQL Server Express Edition: sqlserver-ex

3. Once you have created the option group, you'll need to Add option.

4. Choose SQLSERVER_BACKUP_RESTORE for your Option name. For the IAM role, it is best to create a new role.

5. Choose the S3 bucket where your database file is hosted. For scheduling, choose Immediately.

6. Go back to your AWS RDS MSSQL Server instance and click Modify.

7. Choose the created option group with sql-server-express-backup, then Click Continue.

8. Choose to Apply immediately for scheduling of modifications.

9. Go back to your AWS RDS MSSQL Server instance page and scroll down and modify Manage IAM Roles. Add the IAM role you have created in S3. For the Feature, choose S3_INTEGRATION.

Azure Data Studio

Importing the sample database in S3 bucket through restore function

1. In your connected AWS RDS MSSQL Server, create a new query and type in the following:

exec msdb.dbo.rds_restore_database 
@restore_db_name='AdventureWorks-test', 
@s3_arn_to_restore_from='arn:aws:s3:::clark-sample-dbs/AdventureWorks2017.bak';

Refresh your Azure Data Studio. Also, try restarting the application if your database did not appear or don't have permission to access it.

Now you are done! Good job! ???

Resources:

• https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/

Connect with me on LinkedIn here