Performing SQL Server Native Backup on AWS RDS

Answer: A.

Option A is CORRECT because performing SQL Server native backup using .bak files is a SQL Server specific functionality.

To enable this capability, SQLSERVER_BACK_RESTORE option must be configured.

To add this functionality to an RDS SQL Server instance, you must create a new custom Option Group and configure the SQLSERVER_BACKUP_RESTORE setting, and then associate the new Option Group with the RDS instance.

Option B is incorrect because DB Parameter Groups are used for setting DB system parameters.In this case, we want to enable a specific feature available for a particular RDS engine (SQL Server).To do this, we must use and configure Option Groups.

Option C is incorrect because create-db-snapshot creates a storage volume snapshot of the entire RDS database instance.

It does not perform a SQL Server native backup of a specific database.

Option D is incorrect because the automated backup feature creates automated volume snapshots of the RDS database instance.

It does not perform a SQL Server native backup of a specific database.

Reference:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

To take a SQL Server native backup of a database in an AWS RDS SQL Server instance, the DBA must perform the following action:

Option A: Create a new Option Group and configure SQLSERVER_BACKUP_RESTORE option. Associate the option group with the DB instance.

Explanation:

1. Option Groups in RDS are used to enable specific database engine features that are not available by default in RDS instances.
2. SQLSERVER_BACKUP_RESTORE is an option in the RDS SQL Server engine that enables native backup and restore functionality.
3. To use this feature, the DBA must create a new Option Group and configure the SQLSERVER_BACKUP_RESTORE option in the Option Group.
4. After configuring the Option Group, the DBA must associate the Option Group with the RDS SQL Server instance where the database resides.
5. Once the association is complete, the DBA can take a SQL Server native backup of the database using standard SQL Server backup commands.

Option B: Create a new DB Parameter Group and configure the SQLSERVER_BACKUP_RESTORE system parameter. Associate the DB Parameter Group with the DB instance.

Explanation:

1. DB Parameter Groups in RDS are used to configure database engine settings that are not available by default in RDS instances.
2. SQLSERVER_BACKUP_RESTORE is a system parameter in the RDS SQL Server engine that enables native backup and restore functionality.
3. To use this feature, the DBA must create a new DB Parameter Group and configure the SQLSERVER_BACKUP_RESTORE system parameter in the DB Parameter Group.
4. After configuring the DB Parameter Group, the DBA must associate the DB Parameter Group with the RDS SQL Server instance where the database resides.
5. Once the association is complete, the DBA can take a SQL Server native backup of the database using standard SQL Server backup commands.

Option C: Use the AWS CLI create-db-snapshot command to create a backup of the RDS instance.

Explanation:

1. The AWS CLI create-db-snapshot command is used to create a snapshot of an RDS instance.
2. Snapshots are used to create a point-in-time copy of the entire RDS instance, including all databases.
3. While it is possible to use a snapshot to restore a database, it is not a native SQL Server backup and restore operation.
4. The DBA may need to convert the snapshot to a native SQL Server backup format before restoring it to a SQL Server instance.

Option D: Ensure that RDS automated backup feature is configured on the RDS instance.

Explanation:

1. The RDS automated backup feature creates automated backups of an RDS instance, including all databases, at a specified interval.
2. While it is possible to use an automated backup to restore a database, it is not a native SQL Server backup and restore operation.
3. The DBA may need to convert the automated backup to a native SQL Server backup format before restoring it to a SQL Server instance.
4. Automated backups are not suitable for situations where the DBA needs more control over the backup and restore process, such as backing up individual databases.