AWS Cloud SQL RDS backup and restore .

Sql with rds backup and restoration : Prerequisite:

1. SQL server with Rds
2. S3 bucket.

Point of view:

Open the Amazon RDS console, and then choose Option Groups in the navigation pane. Choose Create Group, and enter the name, description, engine, and engine version of your server. Then, choose Create.

Create option group:

Select the option group that you created, and then choose Add Option. Choose “SQLSERVER_BACKUP_RESTORE”. It’s a best practice to create a new IAM role and then choose Add Option, so that your IAM role has the required privileges. Choose your S3 bucket, or create a new S3 bucket. Then, choose Apply Immediately and Add Option.

After completing the all steps use the backup command with RDS:

Fullbackup:

DECLARE @time Varchar(255), @name VARCHAR(255)
SELECT @time = CONVERT(varchar(10),GETDATE(),110)
SELECT @name =’arn:aws:s3:::ajayschool1/schoolfull’ + ‘_’ +@time+ ‘.bak’/
exec msdb.dbo.rds_backup_database 
 @source_db_name=’distro-plx-db-SMSOne’, 
 @s3_arn_to_backup_to= @name,
 @overwrite_s3_backup_file=1,
 @type=’FULL’;

Check backup completed or not:

exec msdb.dbo.rds_task_status @db_name=’school’;

Restore RDS full backup with No recovery :

exec msdb.dbo.rds_restore_database
@restore_db_name='school_new',
@s3_arn_to_restore_from='arn:aws:s3:::ajayschool1/schoolfull.bak',
@with_norecovery=0,
@type='FULL';

Restore RDS diff backup with recovery :

exec msdb.dbo.rds_restore_database
@restore_db_name=’school_new’,
@s3_arn_to_restore_from=’arn:aws:s3:::ajayschool1/schoolfull.bak’,
@with_norecovery=0,
@type=’DIFFERENTIAL’;

Schedule backup using SQL Agent.

SQL Agent → job → new job → Script.