There are three database recovery models in SQL Server.
Simple Recovery Model
In the Simple recovery model, database transaction logs are cleared along with the Checkpoint or Backup operation to minimize transaction logs.
All committed transactions are cleared upon completion of the Checkpoint or Backup operation, with only a few logs kept, necessary for recovery when an instance restarts. This model can minimize database transaction logs and storage usage, reduce storage overhead, and eliminate the need for special DBAs to maintain and back up database logs.
And you can set up the simple model in two methods:
Full Recovery Model
The Full model in SQL Server is quite the opposite of the Simple recovery model. This section shows the following four aspects about the Full model: working principles, application scenarios, setting, and example scenarios.
In contrast to Simple, we can consider the Full model as "Checkpoint without truncate log," that is, the SQL Server database engine does not truncate transaction logs. Therefore, compared with databases using the Simple model, databases using the Full model have transaction log files that increase faster and are much larger. These database log files contain all recently committed transactions until a transaction log backup occurs and finishes successfully.
You can set up full model in two methods:
USE [master] GO ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT GO
Bulk-Logged Recovery Model
As a mix of the Simple and Full recovery models, the Bulk-logged model adapts and improves the Bulk Imports operation under the Full model.
In a SQL Server database system, a method called Bulk Imports is available for quickly importing data, such as BCP, Bulk INSERT, and INSERT INTO... SELECT. If these Bulk operations are performed in a database under the Full model, massive amounts of log information are generated, significantly influencing SQL Server performance. The Bulk-logged model is designed to solve this problem. When a Bulk Imports operation is performed in a database running under the Bulk-logged model, very few logs are recorded to prevent the sharp increase in transaction logs and guarantee stable and efficient SQL Server performance. Simply, when no Bulk Imports operations are performed, the Bulk-logged model is equivalent to the Full model; when a Bulk Imports operation is performed, it is equivalent to the Simple model. Therefore, databases using the Bulk-logged model cannot implement point-in-time recovery. This is also a disadvantage in the Simple model.
You can set up the bulk-logged model in two methods:
USE [master] GO ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY Bulk_LOGGED WITH NO_WAIT GO
For detailed application scenarios and typical example application scenarios for the three recovery models in SQL Server, please see SQL Server Best Practices: Database Recovery Models and Backups.
Alibaba Cloud Database Backup Service (DBS) is a safe, dependable, and cost-effective solution that enables you to protect and backup your data. It provides robust protection to your data including, but not limited to, data centers, hybrid clouds, and third-party vendors.
As a highly reliable solution, it enables you to take incremental backup as well as restore data to a very accurate point in time. Case in point, if you need to make any modification to your online data, you can easily do so as DBS also offers real-time backup whenever you change or modify online data.
Database Backup Service (DBS) comes with multiple benefits such as incremental backups, point in time recovery, support databases from multiple environments, and much more.
Alibaba Cloud DBS supports various use cases and scenarios. It is a right fit solution for companies requiring remote backup, cloud backup, table recovery, and long-term data archiving.
In this article, we will show you how to make use of the new Database Backup Service to perform database backup and restore process.
Performing Database Restore
The cache function is enabled by default on a backup client to accelerate the data backup process. This function helps you accelerate the data backup process by caching data entry IDs and metadata. You can disable this function or optimize the configurations of this function on source backup devices.
DBS offers features such as full backup, incremental backup, and data recovery. You need to create and configure a backup plan in order to use these features.
After you have created a backup plan, you need to configure the plan in the DBS console by following these steps:
Database Backup (DBS) is a secure and cost-efficient solution for continuous data protection. The service offers strong protection for data stored in multiple environments, such as enterprise data centers, hybrid clouds, public clouds, and third-party cloud vendors. Database Backup (DBS) offers a Pay-As-You-Go billing method. Users can achieve disaster recovery quickly and avoid big investments in infrastructure.
Alibaba Cloud offers fully managed database services. We monitor, backup, and recover your database automatically so that you can fully focus on your business development. To provide more stable and scalable database services, Alibaba Cloud optimized the source code based on the open-source database engines. Our database services, such as ApsaraDB RDS for MySQL and ApsaraDB RDS for PPAS have lower risk compare to the other services using the community edition.
The security of the cloud databases are critical, they are directly affecting the security and stable operation of the cloud based applications. Only by understanding the principles, methods, and operation methods of backup and recovery of commonly used cloud databases, the cloud database administrators can better protect them. Through this course, you can not only understand the backup and recovery principles, types and methods of databases on the cloud, but also understand the backup and recovery methods related to Alibaba Cloud RDS.
Cherish Wang - January 17, 2019
Alibaba Clouder - July 5, 2019
Alibaba Clouder - November 12, 2018
ApsaraDB - June 18, 2021
Cherish Wang - February 20, 2019
Alibaba Clouder - July 30, 2019
Alibaba Cloud provides products and services to help you properly plan and execute data backup, massive data archiving, and storage-level disaster recovery.Learn More
Provides an easy-to-use BaaS solution that protects your data on premises and on the cloud.Learn More
A reliable, cost-efficient backup service for continuous data protection.Learn More
SDDP automatically discovers sensitive data in a large amount of user-authorized data, and detects, records, and analyzes sensitive data consumption activities.Learn More
More Posts by Alibaba Clouder