Restoring on prem database to AWS RDS Manually

In this blog post we'll talk about how to restore databases on prem to RDS SQL manually. Why would you want to do this? Well doing a backup and restore manually gives you greater control over the whole process. You can do the relevant checks you feel comfortable with doing before you officially switch over from on prem to AWS RDS!

Prerequisite:

A parameter group will need to be created if not done already in order to grant the backup and restore permission to the specific S3 Bucket.

1.) Browse to Amazon RDS within the services of the AWS Console

2.) On the left hand side you’ll need to navigate to option groups

3.) Select the create group option

4.) Fill out the relevant group details and click create. Please note parameter group family will be the version of SQL. You can find out the relevant engine version of SQL RDS by navigating to the configuration tab within the RDS instance on the console.

5.) Once created you should see your option group now displayed. Tick the relevant option group and click add option

6.) Under option name select “SQLSERVER_BACKUP_RESTORE”

7.) Select the relevant IAM Role or create a new one. If you create a new role you can select the specific bucket you want to grant access to.

Click add option and you’ll get the option to either apply the instance settings immediately or during scheduled maintenance

11.) Now you need to assign your option group to RDS instance. Within your RDS instance select modify.

12.) Under additional configuration set the appropriate option group and apply.

Restore Process

Backups will be backed up to an amazon S3 Bucket. Please note that Multi-AZ environments has some limitations therefore it’s recommended that if your using the below process you migrate to a single AZ and then convert to a Multi-AZ after.

1.) To restore the database full backup connect to the SQL Server using Management Studio. Please note if your VPC is locked down you may need an EC2 instance with management studio installed to act as a jumpbox.

2.) In order to restore the backup we’ll need to navigate to S3 and look at the details on the backup file. You’ll need to make note of the Amazon Resource Name (ARN) as you’ll need this for the restore script

3.) Execute the following script ensuring you make the relevant modifications. Please see comment lines.

exec msdb.dbo.rds_restore_database 	 
@restore_db_name='database1', --The name of the database to restore. @s3_arn_to_restore_from='arn:aws:s3:::backuptest-bucket/Database1bak', --The ARN for the S3 Object
@with_norecovery=1; --Set it to 1 to restore with NORECOVERY, Set it to 0 to restore with RECOVERY.   

4.) When the above is run a task is created to start the restore. To Monitor the status of this task we’ll need to execute.

 exec msdb.dbo.rds_task_status --Checks status of current tasks

This will return a screenshot like the below- you can keep executing the query to monitor the % complete

Once completed you’ll see the “lifecycle” column marked as success

5.) If you refresh your database instances you’ll see this database now exists and is within a restoring state:

6.) To restore a log copy the ARN of the .trn file from the S3 Bucket

7.) In management studio run the following script with the relevant amendments. Please see comment lines.

exec msdb.dbo.rds_restore_log @restore_db_name='database1', --The name of the database to restore. 
@s3_arn_to_restore_from='arn:aws:s3:::backuptest-bucket/Database1log.trn', 
--The ARN for the S3 Object
@with_norecovery=1; --Set it to 1 to restore with NORECOVERY, Set it to 0 to restore with RECOVERY.   

8.) As previously mentioned you can run the following script to check its current status which will show a new entry for RESTORE_DB_LOG_NORECOVERY

exec msdb.dbo.rds_task_status

9.) When your ready to recover and the database is offline on prem ready to cut over you can do a manual check on the LSNs. Run something like the below on source and target these numbers should match showing that data is consistent.

d.name 	, 
max (mf.redo_start_lsn) as [Current LSN] 	
from master.sys.master_files as mf
inner join master.sys.databases as d
on mf.database_id = d.database_id 			
where db_name(d.database_id) = 'Database1' -- enter database name 	group by d.name 

10.) Once you’ve confirmed the LSNs are correct and you are ready to recover the database you will need to run the following amended with the databasename

 exec msdb.dbo.rds_finish_restore @db_name='database_name';

11.) Refresh your database list and you will see your restored database is no longer in a recovery state and is available to query.