Context: I’m basing understanding of the MSSQL world on a solid understanding of MySQL and that ecosystem. I am familiar with Heidi, SQLPro and knowing relatively little about MSSMS (Microsoft SQL Server Management Studio) at this point.
I need to move a .bak file i have locally from a client to an AWS RDS instance.
Reference Tutorials and info
Read this first, This tutorial explains the process nicely:
Restore SQL Server database backup to an AWS RDS Instance of SQL Server
The AWS guides the tutorial is based on, which give more context:
Importing and exporting SQL Server databases
Support for native backup and restore in SQL Server
Basic Process Concept
You’ll need to have and understanding of S3, IAM, RDS
- Upload your .bak to S3
- Create an RDS instance
- Allow your RDS instance to talk to S3
- Connect MSSMS to your RDS
- Execute query in MSSMS which triggers AWS specific stored procedures which tell RDS to restore the data and where to find it
- Go and get a coffee
- Come back, refresh and celebrate
Query to run in MSSMS
Having set up everything on the AWS side and with your MSSMS connected to the RDS you can go wild wild commands. The following restores the database from the .bak
exec msdb.dbo.rds_restore_database @restore_db_name='mydata', @s3_arn_to_restore_from='arn:aws:s3:::mydata-bucker/mydata.bak';
Lessons Learned
Local files stay local
When connected to a remote instance of MSSQL you can not read the local file system. This is a different workflow to Heidi where you may connect to a remote database, run some SQL commands and it executes on the remote database. You cannot run a .bak file as is described here How to restore SQL Server database from backup using T-SQL Commands . Another good article from sqlbackupandftp.com Remote SQL Server backups explained has a bit more background info.
Query Succeeded but no Databases was created?!
After running the restore query, things look positive, but the process is still running in the background.
If your query in MSSMS shows as successful query execution but the database is not there….
You may have been tripped up as I was by:
- IAM permissions are incorrect and the ARN can’t be found
- S3 bucket and RDS are in different regions region
I found this by…
Debugging the active task
Checking the Logs on the DB or on AWS won’t reveal anything going on in the background.
Run the following command to get a status update of the current task, giving the task ID as a param.
exec msdb..rds_task_status @task_id = 15
The output appears in the results tab under task_info
[2021-10-10 01:10:10.100] Aborted the task because of a task failure or a concurrent RESTORE_DB request. [2021-10-10 01:10:10.100] Task has been aborted [2021-10-10 01:10:10.100] The S3 bucket location: ap-xxx-1 does not match the RDS instance region: ap-xxx-2. Please specify a bucket that is in the same region as RDS instance.
Refreshing
The Object Explorer Refresh button doesn’t refresh the databases shown in ‘Databases’ in the folder tree.
Instead right click ‘Databases’ and choose refresh to see your newly imported Database.
