开启SQL Server简单恢复模式-ApsaraDB RDS(RDS)-阿里云帮助中心

RDS SQL Server supports two recovery models: the full recovery model, which is the default for an instance, and the simple recovery model. Compared to the full recovery model, the simple recovery model does not retain the transaction log or perform log backups, while data backups are not affected. This model helps reduce instance and backup storage usage, which helps resolve issues with insufficient storage and saves on backup fees.

Differences between the simple and full recovery models

A recovery model is a database property that controls transaction logging, backup procedures, and available restore operations. The following table compares the two recovery models supported by RDS SQL Server.

Item	Full recovery model	Simple recovery model
How it works	In the full recovery model, SQL Server records all transactions in the transaction log and periodically backs it up. This allows you to restore the database to a specific point in time or to a state just before a specific transaction.	In the simple recovery model, SQL Server retains only recent data changes instead of recording every transaction. It performs only full data backups and does not perform transaction log backups.
Edition requirements	Supported on instances of all editions.	Only instances of the Basic Edition are supported. For more information, see Product Edition Overview.
Data recovery capabilities	Supports point-in-time recovery (PITR).	Does not support point-in-time recovery (PITR). You can restore the database only to its most recent full backup.
Recovery destination	Supports restoring to an existing instance or a new instance.	Supports restoring to an existing instance or a new instance.

Note

For a detailed description of recovery models, see the official Microsoft documentation.

Prerequisites

The RDS SQL Server instance is a Basic Edition instance.
If this is your first time using the RDS backup service, you must grant permissions to the service-linked role for DBS (AliyunServiceRoleForDBS) by using your Alibaba Cloud account. For instructions, see How to grant permissions to the service-linked role for DBS.

Impacts

Enabling the simple recovery model affects the instance in the following ways:

After you enable this model, you cannot switch back to the full recovery model.
You cannot upgrade the database version, upgrade the minor engine version, change configurations across versions, or migrate the instance across availability zones.
The system no longer performs log backups. However, data backups are unaffected and continue as scheduled.
Because this model does not back up the transaction log, you can only restore data from a backup set and cannot perform a point-in-time recovery.
When you restore data from a backup set, you cannot restore it to a High-availability Edition, Cluster Edition, or shared instance specification instance.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
On the Basic Information page, in the Configuration Information section, turn on the Simple Recovery Model switch.
In the dialog box, read the information carefully and then click OK.

Related operations

You can also switch to the simple recovery model by calling the ModifyDBInstanceConfig API operation.
To modify your instance's backup policy, including its backup cycle, backup time, and retention policy, see Configure a SQL Server backup policy.
If you need to download backup data for backup queries, migration, or auditing, see Download data backups and log backups.
If you accidentally delete data or need to analyze historical data, you can restore a backup to an existing or new instance in the same region. For more information, see Restore SQL Server data.
To learn about potential backup fees for your instance, see Backup fees.
For other solutions to insufficient storage space on RDS SQL Server, see Troubleshoot insufficient storage space for an ApsaraDB RDS for SQL Server instance.