Access to transaction log backups with RDS for SQL Server
With access to transaction log backups for RDS for SQL Server, you can list the transaction log backup files for a database and copy them to a target Amazon S3 bucket. By copying transaction log backups in an Amazon S3 bucket, you can use them in combination with full and differential database backups to perform point in time database restores. You use RDS stored procedures to set up access to transaction log backups, list available transaction log backups, and copy them to your Amazon S3 bucket.
Access to transaction log backups provides the following capabilities and benefits:
List and view the metadata of available transaction log backups for a database on an RDS for SQL Server DB instance.
Copy available transaction log backups from RDS for SQL Server to a target Amazon S3 bucket.
Perform point-in-time restores of databases without the need to restore an entire DB instance. For more information on restoring a DB instance to a point in time, see Restoring a DB instance to a specified time.
Availability and support
Access to transaction log backups is supported in all Amazon Regions. Access to transaction log backups is available for all editions and versions of Microsoft SQL Server supported on Amazon RDS.
Requirements
The following requirements must be met before enabling access to transaction log backups:
-
Automated backups must be enabled on the DB instance and the backup retention must be set to a value of one or more days. For more information on enabling automated backups and configuring a retention policy, see Enabling automated backups.
-
An Amazon S3 bucket must exist in the same account and Region as the source DB instance. Before enabling access to transaction log backups, choose an existing Amazon S3 bucket or create a new bucket to use for your transaction log backup files.
-
An Amazon S3 bucket permissions policy must be configured as follows to allow Amazon RDS to copy transaction log files into it:
Set the object account ownership property on the bucket to Bucket Owner Preferred.
Add the following policy. There will be no policy by default, so use the bucket Access Control Lists (ACL) to edit the bucket policy and add it.
The following example uses an ARN to specify a resource. We recommend using the
SourceArn
andSourceAccount
global condition context keys in resource-based trust relationships to limit the service's permissions to a specific resource. For more information on working with ARNs, see Amazon resource names (ARNs) and Working with Amazon Resource Names (ARNs) in Amazon RDS.Example of an Amazon S3 permissions policy for access to transaction log backups
{ "Version": "2012-10-17", "Statement": [ { "Sid": "Only allow writes to my bucket with bucket owner full control", "Effect": "Allow", "Principal": { "Service": "backups.rds.amazonaws.com" }, "Action": "s3:PutObject", "Resource": "arn:aws:s3:::
{customer_bucket}/{customer_path}
/*", "Condition": { "StringEquals": { "s3:x-amz-acl": "bucket-owner-full-control", "aws:sourceAccount": "{customer_account}", "aws:sourceArn": "{db_instance_arn}" } } } ] } -
An Amazon Identity and Access Management (IAM) role to access the Amazon S3 bucket. If you already have an IAM role, you can use that. You can choose to have a new IAM role created for you when you add the
SQLSERVER_BACKUP_RESTORE
option by using the Amazon Web Services Management Console. Alternatively, you can create a new one manually. For more information on creating and configuring an IAM role withSQLSERVER_BACKUP_RESTORE
, see Manually creating an IAM role for native backup and restore. -
The
SQLSERVER_BACKUP_RESTORE
option must be added to an option group on your DB instance. For more information on adding theSQLSERVER_BACKUP_RESTORE
option, see Support for native backup and restore in SQL Server.Note
If your DB instance has storage encryption enabled , the Amazon KMS (KMS) actions and key must be provided in the IAM role provided in the native backup and restore option group.
Optionally, if you intend to use the
rds_restore_log
stored procedure to perform point in time database restores, we recommend using the same Amazon S3 path for the native backup and restore option group and access to transaction log backups. This method ensures that when Amazon RDS assumes the role from the option group to perform the restore log functions, it has access to retrieve transaction log backups from the same Amazon S3 path. -
If the DB instance is encrypted, regardless of encryption type (Amazon managed key or Customer managed key), you must provide a Customer managed KMS key in the IAM role and in the
rds_tlog_backup_copy_to_S3
stored procedure.
Limitations and recommendations
Access to transaction log backups has the following limitations and recommendations:
-
You can list and copy up to the last seven days of transaction log backups for any DB instance that has backup retention configured between one to 35 days.
-
The Amazon S3 bucket used for access to transaction log backups must exist in the same account and Region as the source DB instance. Cross-account and cross-region copy is not supported.
-
Only one Amazon S3 bucket can be configured as a target to copy transaction log backups into. You can choose a new target Amazon S3 bucket with the
rds_tlog_copy_setup
stored procedure. For more information on choosing a new target Amazon S3 bucket, see Setting up access to transaction log backups. -
You cannot specify the KMS key when using the
rds_tlog_backup_copy_to_S3
stored procedure if your RDS instance is not enabled for storage encryption. -
Multi-account copying is not supported. The IAM role used for copying will only permit write access to Amazon S3 buckets within the owner account of the DB instance.
-
Only two concurrent tasks of any type may be run on an RDS for SQL Server DB instance.
-
Only one copy task can run for a single database at a given time. If you want to copy transaction log backups for multiple databases on the DB instance, use a separate copy task for each database.
-
If you copy a transaction log backup that already exists with the same name in the Amazon S3 bucket, the existing transaction log backup will be overwritten.
-
You can only run the stored procedures that are provided with access to transaction log backups on the primary DB instance. You can’t run these stored procedures on an RDS for SQL Server read replica or on a secondary instance of a Multi-AZ DB cluster.
-
If the RDS for SQL Server DB instance is rebooted while the
rds_tlog_backup_copy_to_S3
stored procedure is running, the task will automatically restart from the beginning when the DB instance is back online. Any transaction log backups that had been copied to the Amazon S3 bucket while the task was running before the reboot will be overwritten. -
The Microsoft SQL Server system databases and the
RDSAdmin
database cannot be configured for access to transaction log backups. -
Copying to buckets encrypted by SSE-KMS isn't supported.
Setting up access to transaction log backups
To set up access to transaction log backups, complete the list of requirements in the Requirements section,
and then run the rds_tlog_copy_setup
stored procedure. The procedure will enable the access to transaction log backups feature at the DB instance level. You don't need to run it for each individual database on the DB instance.
Important
The database user must be granted the db_owner
role within SQL Server on each
database to configure and use the access to transaction log backups feature.
Example usage:
exec msdb.dbo.rds_tlog_copy_setup @target_s3_arn='arn:aws-cn:s3:::
mybucket
/myfolder
';
The following parameter is required:
-
@target_s3_arn
– The ARN of the target Amazon S3 bucket to copy transaction log backups files to.
Example of setting an Amazon S3 target bucket:
exec msdb.dbo.rds_tlog_copy_setup @target_s3_arn='arn:aws-cn:s3:::
accesstlogs-testbucket
/mytestdb1
';
To validate the configuration, call the rds_show_configuration
stored procedure.
Example of validating the configuration:
exec rdsadmin.dbo.rds_show_configuration @name='target_s3_arn_for_tlog_copy';
To modify access to transaction log backups to point to a different Amazon S3 bucket, you can view the current Amazon S3 bucket value and
re-run the rds_tlog_copy_setup
stored procedure using a new value for the @target_s3_arn
.
Example of viewing the existing Amazon S3 bucket configured for access to transaction log backups
exec rdsadmin.dbo.rds_show_configuration @name='target_s3_arn_for_tlog_copy';
Example of updating to a new target Amazon S3 bucket
exec msdb.dbo.rds_tlog_copy_setup @target_s3_arn='arn:aws-cn:s3:::
mynewbucket
/mynewfolder
';
Listing available transaction log backups
With RDS for SQL Server, databases configured to use the full recovery model and a DB instance backup retention set to one or more days have transaction log backups automatically enabled. By enabling access to transaction log backups, up to seven days of those transaction log backups are made available for you to copy into your Amazon S3 bucket.
After you have enabled access to transaction log backups, you can start using it to list and copy available transaction log backup files.
Listing transaction log backups
To list all transaction log backups available for an individual database, call the rds_fn_list_tlog_backup_metadata
function.
You can use an ORDER BY
or a WHERE
clause when calling the function.
Example of listing and filtering available transaction log backup files
SELECT * from msdb.dbo.rds_fn_list_tlog_backup_metadata('mydatabasename'); SELECT * from msdb.dbo.rds_fn_list_tlog_backup_metadata('mydatabasename') WHERE rds_backup_seq_id = 3507; SELECT * from msdb.dbo.rds_fn_list_tlog_backup_metadata('mydatabasename') WHERE backup_file_time_utc > '2022-09-15 20:44:01' ORDER BY backup_file_time_utc DESC;

The rds_fn_list_tlog_backup_metadata
function returns the following output:
Column name | Data type | Description |
---|---|---|
|
sysname |
The database name provided to list the transaction log backups for. |
|
int |
The internal database identifier for the input parameter |
|
uniqueidentifier |
The unique ID of the original database at creation. This value remains the same when the database is restored, even to a different database name. |
|
int |
The ID that RDS uses internally to maintain a sequence number for each transaction log backup file. |
|
bigint |
The epoch time that a transaction backup file was generated. |
|
datetime |
The UTC time-converted value for the |
|
numeric(25,0) |
The log sequence number of the first or oldest log record of a transaction log backup file. |
|
numeric(25,0) |
The log sequence number of the last or next log record of a transaction log backup file. |
|
bit |
A boolean value indicating if the log chain is broken between the current transaction log backup file and the previous transaction log backup file. |
|
bigint |
The size of the transactional backup set in bytes. |
|
varchar(4000) |
Error message if the |
Copying transaction log backups
To copy a set of available transaction log backups for an individual database to your Amazon S3 bucket, call the rds_tlog_backup_copy_to_S3
stored procedure. The rds_tlog_backup_copy_to_S3
stored procedure will initiate a new task to copy transaction log backups.
Note
The rds_tlog_backup_copy_to_S3
stored procedure will copy the transaction log backups without
validating against is_log_chain_broken
attribute. For this reason, you should manually confirm an unbroken
log chain before running the rds_tlog_backup_copy_to_S3
stored procedure. For further explanation, see
Validating the transaction log backup log chain.
Example usage of the rds_tlog_backup_copy_to_S3
stored procedure
exec msdb.dbo.rds_tlog_backup_copy_to_S3 @db_name='
mydatabasename
', [@kms_key_arn='arn:aws-cn:kms:region
:account-id
:key/key-id
'], [@backup_file_start_time='2022-09-01 01:00:15'
], [@backup_file_end_time='2022-09-01 21:30:45'
], [@starting_lsn=149000000112100001], [@ending_lsn=149000000120400001], [@rds_backup_starting_seq_id=5], [@rds_backup_ending_seq_id=10];
The following input parameters are available:
Parameter | Description |
---|---|
|
The name of the database to copy transaction log backups for |
|
The ARN of the KMS key used to encrypt a storage-encrypted DB instance. |
|
The UTC timestamp as provided from the |
|
The UTC timestamp as provided from the |
|
The log sequence number (LSN) as provided from the |
|
The log sequence number (LSN) as provided from the |
|
The sequence ID as provided from the |
|
The sequence ID as provided from the |
You can specify a set of either the time, LSN, or sequence ID parameters. Only one set of parameters are required.
You can also specify just a single parameter in any of the sets. For example, by providing a value for only the backup_file_end_time
parameter,
all available transaction log backup files prior to that time within the seven-day limit will be copied to your Amazon S3 bucket.
Following are the valid input parameter combinations for the rds_tlog_backup_copy_to_S3
stored procedure.
Parameters provided | Expected result |
---|---|
|
Copies transaction log backups from the last seven days and exist between the provided range
of |
|
Copies transaction log backups from the last seven days and starting from the provided
|
|
Copies transaction log backups from the last seven days up to the provided
|
|
Copies transaction log backups that are available from the last seven days and are between
the provided range of the |
|
Copies transaction log backups that are available from the last seven days, beginning from the provided
|
|
Copies transaction log backups that are available from the last seven days, up to the provided
|
|
Copies transaction log backups that are available from the last seven days, and exist between the provided range
of |
|
Copies transaction log backups that are available from the last seven days, beginning from the provided
|
|
Copies transaction log backups that are available from the last seven days, up to the provided
|
|
Copies a single transaction log backup with the provided |
Validating the transaction log backup log chain
Databases configured for access to transaction log backups must have automated backup retention enabled. Automated backup
retention sets the databases on the DB instance to the FULL
recovery model. To support point in time restore for
a database, avoid changing the database recovery model, which can result in a broken log chain. We recommend keeping the
database set to the FULL
recovery model.
To manually validate the log chain before copying transaction log backups, call the rds_fn_list_tlog_backup_metadata
function
and review the values in the is_log_chain_broken
column. A value of "1" indicates the log chain was broken
between the current log backup and the previous log backup.
The following example shows a broken log chain in the output from the rds_fn_list_tlog_backup_metadata
stored procedure.

In a normal log chain, the log sequence number (LSN) value for first_lsn for given rds_sequence_id should match the value of last_lsn in the preceding rds_sequence_id. In the image, the rds_sequence_id of 45 has a first_lsn value 90987, which does not match the last_lsn value of 90985 for preceeding rds_sequence_id 44.
For more information about SQL Server transaction log architecture and log sequence numbers,
see Transaction Log Logical Architecture
Amazon S3 bucket folder and file structure
Transaction log backups have the following standard structure and naming convention within an Amazon S3 bucket:
A new folder is created under the
target_s3_arn
path for each database with the naming structure as{db_id}.{family_guid}
.Within the folder, transaction log backups have a filename structure as
{db_id}.{family_guid}.{rds_backup_seq_id}.{backup_file_epoch}
.You can view the details of
family_guid,db_id,rds_backup_seq_id and backup_file_epoch
with therds_fn_list_tlog_backup_metadata
function.
The following example shows the folder and file structure of a set of transaction log backups within an Amazon S3 bucket.

Tracking the status of tasks
To track the status of your copy tasks, call the rds_task_status
stored procedure.
If you don't provide any parameters, the stored procedure returns the status of all tasks.
Example usage:
exec msdb.dbo.rds_task_status @db_name='database_name', @task_id=ID_number;
The following parameters are optional:
-
@db_name
– The name of the database to show the task status for. -
@task_id
– The ID of the task to show the task status for.
Example of listing the status for a specific task ID:
exec msdb.dbo.rds_task_status @task_id=5;
Example of listing the status for a specific database and task:
exec msdb.dbo.rds_task_status@db_name='my_database',@task_id=5;
Example of listing all tasks and their status for a specific database:
exec msdb.dbo.rds_task_status @db_name='my_database';
Example of listing all tasks and their status on the current DB instance:
exec msdb.dbo.rds_task_status;
Canceling a task
To cancel a running task, call the rds_cancel_task
stored procedure.
Example usage:
exec msdb.dbo.rds_cancel_task @task_id=ID_number;
The following parameter is required:
-
@task_id
– The ID of the task to cancel. You can view the task ID by calling therds_task_status
stored procedure.
For more information on viewing and canceling running tasks, see Importing and exporting SQL Server databases using native backup and restore.
Troubleshooting access to transaction log backups
The following are issues you might encounter when you use the stored procedures for access to transaction log backups.
Stored Procedure | Error Message | Issue | Troubleshooting suggestions |
---|---|---|---|
rds_tlog_copy_setup |
|
Automated backups are not enabled for the DB instance. |
DB instance backup retention must be enabled with a retention of at least one day. For more information on enabling automated backups and configuring backup retention, see Backup retention period. |
rds_tlog_copy_setup |
|
An internal error occurred. |
Reconnect to the RDS endpoint and run the |
rds_tlog_copy_setup |
|
The stored procedure was attempted within a transaction using |
Avoid using |
rds_tlog_copy_setup |
|
An incorrect value was provided for the input parameter |
Ensure the input parameter |
rds_tlog_copy_setup |
|
The |
Enable the |
rds_tlog_copy_setup |
|
An |
Ensure the input parameter |
rds_tlog_copy_setup |
|
The input parameter |
Ensure the input parameter |
rds_tlog_copy_setup |
|
The |
To modify the Amazon S3 bucket value for access to transaction log backups, provide a different |
rds_tlog_copy_setup |
|
There was an unspecified error while generating credentials to enable access to transaction log backups. |
Review your setup configuration and try again. |
rds_tlog_copy_setup |
|
Only two tasks may run at any time. There are pending tasks awaiting completion. |
View pending tasks and wait for them to complete. For more information on monitoring task status, see Tracking the status of tasks. |
rds_tlog_backup_copy_to_S3 |
|
Only one copy task may run at any time for a given database. There is a pending copy task awaiting completion. |
View pending tasks and wait for them to complete. For more information on monitoring task status, see Tracking the status of tasks. |
rds_tlog_backup_copy_to_S3 |
|
None of the three parameter sets were provided, or a provided parameter set is missing a required parameter. |
You can specify either the time, lsn, or sequence ID parameters. One set from these three sets of parameters are required. For more information on required parameters, see Copying transaction log backups. |
rds_tlog_backup_copy_to_S3 |
|
Automated backups are not enabled for the DB instance. |
For more information on enabling automated backups and configuring backup retention, see Backup retention period. |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
Use the correct database name. To list all databases by name, run |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
The following databases are not allowed to be used with access to transaction log backups: |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
Use the correct database name. To list all databases by name, run |
rds_tlog_backup_copy_to_S3 |
|
Automated backups are not enabled for the DB instance. |
For more information on enabling automated backups and configuring backup retention, see Backup retention period. |
rds_tlog_backup_copy_to_S3 |
|
An internal error occurred. |
Reconnect to the RDS endpoint and run the |
rds_tlog_backup_copy_to_S3 |
|
Multiple parameter sets were provided. |
You can specify either the time, lsn, or sequence ID parameters. One set from these three sets of parameters are required. For more information on required parameters, see Copying transaction log backups. |
rds_tlog_backup_copy_to_S3 |
|
The stored procedure was attempted within a transaction using |
Avoid using |
rds_tlog_backup_copy_to_S3 |
|
There are no available transactional log backups for the provided input parameters that fit in the copy retention window. |
Try again with a valid set of parameters. For more information on required parameters, see Copying transaction log backups. |
rds_tlog_backup_copy_to_S3 |
|
There was an issue detected with the provided S3 bucket or its policy permissions. |
Confirm your setup for access to transaction log backups is correct. For more information on setup requirements for your S3 bucket, see Requirements. |
rds_tlog_backup_copy_to_S3 |
|
The stored procedure was attempted on a RDS read replica instance. |
Connect to the RDS primary DB instance to run the |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
Ensure the value provided for input parameter |
rds_tlog_backup_copy_to_S3 |
|
The |
Ensure the account running the stored procedure is permissioned with the |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
Ensure the value provided for input parameter |
rds_tlog_backup_copy_to_S3 |
|
The |
Enable the |
rds_tlog_backup_copy_to_S3 |
|
The value provided for input parameter |
Ensure the value provided for input parameter |
rds_tlog_backup_copy_to_S3 |
|
There may be an issue with the Amazon S3 bucket permissions, or the Amazon S3 bucket provided is in another account or Region. |
Ensure the Amazon S3 bucket policy permissions are permissioned to allow RDS access. Ensure the Amazon S3 bucket is in the same account and Region as the DB instance. |
rds_tlog_backup_copy_to_S3 |
|
When storage encryption is not enabled on the DB instance, the input parameter |
Do not provide an input parameter for |
rds_tlog_backup_copy_to_S3 |
|
When storage encryption is enabled on the DB instance, the input parameter |
Provide an input parameter for |
rds_tlog_backup_copy_to_S3 |
|
The access to transaction log backups setup procedure was not completed before attempting to run the |
Run the |