AWS SQL Server Native Backup Restore

It's time to get outdoors and Explore

AWS SQL Server Native Backup Restore

These are the steps I followed to enable Native Backup and Restores on AWS SQL Server RDS

  • Created NativeBackupRestore Role and Enabled S3 Bucket Access
  • Created Custom Options Group and enabled to use the NativeBackupRestore Role
  • Modified existing instance to use custom options Group.
  • Successfully Backed up the Database from RDS Instance to S3 Bucket
  • Successfully Restored Native Backups on S3 on SQL Server RDS.

Backup Database to S3 Bucket

 
EXEC msdb.dbo.rds_backup_database
@source_db_name = 'database_name',
@s3_arn_to_backup_to = 'arn:aws:s3:::bucket_name/file_name_and_extension',
@overwrite_S3_backup_file = 1;
-- S3 Location : arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/
exec msdb.dbo.rds_backup_database
@source_db_name='Reporting',
@s3_arn_to_backup_to='arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/Reporting_07302016.bak',
@overwrite_S3_backup_file=1;
 

Restore Database from S3 Location

 
exec msdb.dbo.rds_restore_database 
@restore_db_name='database_name', 
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';
-- arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/
EXEC msdb.dbo.rds_restore_database
@restore_db_name='db_log_size_FULL_20160722',
@s3_arn_to_restore_from='arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/db_log_size_FULL_20160722.bak'
---------------------------------------------------------------------------------------------------------------------------
DECLARE @param_restore_db_name sysname
DECLARE @param_s3_arn_to_restore_from NVARCHAR(550)
-- Change the following Values
SELECT @param_restore_db_name = 'AdventureWorks'
SELECT @param_s3_arn_to_restore_from = 'arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/AdventureWorks.bak'
EXEC msdb.dbo.rds_restore_database
@[email protected]_restore_db_name,
@[email protected]_s3_arn_to_restore_from
--------------------------------------------------------------------------------------------------------------------------

Tracking the Status of Tasks

To track the status of your backup and restore tasks, you call the rds_task_status stored procedure. If you don't provide any parameters, the stored procedure returns the status of all tasks. The status for tasks is updated approximately every 2 minutes.

  
exec msdb.dbo.rds_task_status
EXEC msdb.dbo.rds_task_status 
    @db_name = NULL, -- sysname
    @task_id = 2 -- int
exec msdb.dbo.rds_task_status @task_id = 2
exec msdb.dbo.rds_task_status @db_name='Reporting'

Cancelling Tasks

 
exec msdb.dbo.rds_cancel_task @task_id=2; 

Limitations

You can't restore a backup file to the same DB instance that was used to create the backup file. Instead, restore the backup file to a new DB instance. Renaming the database is not a workaround for this limitation. You can't restore the same backup file to the same DB instance multiple times. Renaming the database is not a workaround for this limitation. You will encounter error like this. Aborted the task because of a task failure or a concurrent RESTORE_DB request. Database Reporting_07302016 cannot be restored because there is already an existing database with the same family_guid on the instance.

Option Group Properties

Limitations

RDS Options Group

Creating Custom RDS Options Group

Modify RDS Instance

Modify RDS Instance

Option Group Name : sqlserver-web-optionsgroup Option Group Description : SQL Server Web Options Group to enable native backup and Restore Engine Name: sqlserver-web Major Engine: database engine 12.00 Options : SQLSERVER_BACKUP_RESTORE IAM_ROLE_ARN: arn:aws:iam::001234567890:role/service-role/NativeBackupRestore

Instance Configuration Details EngineName : SQL Server Web 12.00.4422.0.v1 License Mode :  License Included Created Time : February 29, 2016 at 11:55:34 AM UTC-6 Option Group : sqlserver-web-optionsgroup ( in-sync )

An Option Group that contains a list of configured Options (e.g. Oracle Enterprise Manager) that are attached to this DB Instance. Parameter Group : default.sqlserver-web-12.0 ( in-sync )

NativeBackupRestore Policy

Policy ARN : arn:aws:iam::001234567890:policy/service-role/NativeBackupRestore Policy JSON :

 
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource": [
"arn:aws:s3:::bakup-restore-demo-bucket"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetObjectMetaData",
"s3:GetObject",
"s3:PutObject",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload"
],
"Resource": [
"arn:aws:s3:::bakup-restore-demo-bucket/RDS-Backups/*"
]
}
]
}

Make sure you have correct Trust Relationship for your Role:

[caption id="attachment_3217" align="alignnone" width="982"]RDS Trust Relationship RDS Trust Relationship[/caption]

Troubleshooting and Errors Encountered

Errors encountered while backing up database with enabling the feature Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 74 [Batch Start Line 0] Database backup/restore option is not enabled or is in the process of being enabled. Please try again later.

Solution: Enabled native backup option through custom options group.

Errors with S3 Access Policy Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. The specified bucket does not exist

Modified the access policy to ensure the role has access to S3 Bucket. I simulated S3 Access through policy access simulator.I had trailing / (forward slash) on ARN name which caused this error.

Restoring the RDS Backup on Same RDS Server with Different Database Name Aborted the task because of a task failure or a concurrent RESTORE_DB request. Database Reporting_07302016 cannot be restored because there is already an existing database with the same family_guid on the instance. It seems you can't restore the backup on RDS Server if the back up is from same RDS Server.

On-Premise Encrypted Backup Restore Task execution has started. Cannot find server certificate with thumbprint '0xE82F1BCC5F20D65F31A0F4314828C31F72D161BD'. RESTORE FILELIST is terminating abnormally. Aborted the task because of a task failure or a concurrent RESTORE_DB request. RDS-Backups/Reporting_FULL_20160729_231356.bak: S3 processing has been aborted Invalid attempt to read when no data is present.

Ensure your backup is not encrypted

Restoring SQL Server 2016 Backup Task execution has started. The database was backed up on a server running version 13.00.1601. That version is incompatible with this server, which is running version 12.00.4422. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. RESTORE DATABASE is terminating abnormally.

Make sure your backup is SQL Server 2014 or lower.AWS SQL RDS doesn't support SQL Server 2016 yet.

Multiple Concurrent Restore Commands Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 73 [Batch Start Line 22] A task has already been issued for database: Reporting_07302016 with task Id: 5, please try again later. USAGE: EXECUTE msdb.dbo.rds_restore_database @restore_db_name, @s3_arn_to_restore_from, [@kms_master_key_arn] @restore_db_name : Name of the database being restored. @s3_arn_to_restore_from : S3 ARN of the backup file used to restore database from. @kms_master_key_arn : KMS customer master key ARN to decrypt the backup file with.

Don't issue same command multiple times . duh..

Permission Related Errors

Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1 [Batch Start Line 45] The EXECUTE permission was denied on the object 'rds_restore_database', database 'msdb', schema 'dbo'.

Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 58 [Batch Start Line 0] Database backups can only be performed by members of db_owner or db_backupoperator roles in source DATABASE

Msg 229, Level 14, State 5, Procedure rds_backup_database, Line 1 [Batch Start Line 57] The EXECUTE permission was denied on the object 'rds_backup_database', database 'msdb', schema 'dbo'.

 
-- Create the Login First 
USE master
GO
IF NOT EXISTS (SELECT 1 FROM master.sys.sql_logins WHERE [name] = 'SampleDBLogin')
CREATE LOGIN [SampleDBLogin] WITH PASSWORD='PWD'
USE msdb
GO
CREATE USER [SampleDBUser] FOR LOGIN [SampleDBLogin]
GRANT EXECUTE ON dbo.rds_backup_database to [SampleDBUser]
GRANT EXECUTE ON dbo.rds_restore_database to [SampleDBUser]

Random S3 File Download Error Task execution has started. RESTORE DATABASE is terminating abnormally. RDS-Backups/AdventureWorks.bak: S3 processing has been aborted S3 read stream download failed. Encountered an error while downloading an S3 chunk. Solution: To re-run the restore job again.I didn't find any other alternative through AWS Documentation.

Random Network Related Error Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated BACKUP.: Aborting S3 upload, waiting for S3 workers to clean up and exit .bak: S3 processing has been aborted Write on "FF8C20D4BFAC-4C01-9E8E-7DE6C068CAD2" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.) A nonrecoverable I/O error occurred on file "FF8C20D4-BFAC-4C01-9E8E-7DE6C068CAD2:" 995(The I/O operation has been aborted because of either a thread exit or an application request.). BACKUP DATABASE is terminating abnormally.

Solution: To re-run the restore job again.I didn't find any other alternative through AWS Documentation. Also ensure your backup job is not triggered while you are doing the native backup.

Quiz : http://ramblingsofraju.com/quizzes/

References :

Native backup Restore support Announcement https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

Importing and Exporting SQL Server Databases https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Creating Roles http://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html

Amazon Resource Names (ARNs) and AWS Service Namespaces http://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html

Modifying running SQL Server RDS Instance http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.SQLServer.html

AWS Policy Access Simulator http://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_testing-policies.html#policies_policy-simulator-using

Brent Ozar comment on AWS RDS native backup restore support https://www.brentozar.com/archive/2016/07/holy-cow-amazon-rds-sql-server-just-changed-everything/

AWS Documentation for Options Group  Amazon RDS uses option groups to enable and configure these features. An option group can specify features, called options, that are available for a particular Amazon RDS DB instance. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithOptionGroups.html

AWS Documentation for Parameter Group  You manage your DB engine configuration through the use of parameters in a DB parameter group. DB parameter groups act as a container for engine configuration values that are applied to one or more DB instances. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

Posted in AWS, SQL Server on Jun 30, 2016


Comments

Please sign in to comment!