Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Backup and Recover Your SQL Database

How to Backup and Recover Your SQL Database

Data reliability is crucial for business, and here we will discuss how to backup and recover your data in SQL Server.

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:

  1. SSMS IDE: Right-click on the name of the target database to change the recovery model, and then choose Properties > Options > Simple (in the "Recovery model" box on the right) > OK.
  2. A statement: If you think that using SSMS IDE is too complex, you can use the ALTER DATABASE statement to change the recovery model of a database to Simple. The following statement changes the recovery model of the AdventureWorks2008R2 database to Simple.

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:

  1. SSMS IDE: The steps are the same as the first method for setting up the Simple model.
  2. ALTER DATABASE statement:
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:

  1. SSMS IDE: The steps are the same as the first method for setting up the Simple model.
  2. ALTER DATABASE statement:
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.

Related Blog Posts

Protect Your Data with Alibaba Cloud Database Backup Service

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.

Backing Up and Restoring Databases with Alibaba Cloud Database Backup Service

In this article, we will show you how to make use of the new Database Backup Service to perform database backup and restore process.

Backup Process

  1. Locate Database Backup Service on your Alibaba Cloud console.
  2. In the DBS Console, click Create Schedule.
  3. Select your preferred DBS Region, Database Type, Specification and Duration.
  4. After confirming your selections, click Buy Now. Go back to the Database Backup Console and click on Backup Schedules. You will see an un-configured DBS instance listed.
  5. Click Configure Backup and you will see the DBS configuration page. Fill in all the necessary information including database location, database type, address, port, database account, password, and SSL encryption preferences.

Performing Database Restore

  1. Select the backup data to be used and select Restore
  2. We will see the Create Restore Task page. In this page, we need to define which restoration point to roll back to and the destination database information.

Related Documentation

Use a cache to accelerate the data backup process

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.

Configure a backup plan

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:

  1. Configure backup source and target
  2. Configure backup object
  3. Configure backup schedule
  4. Configure lifecycle
  5. Precheck

Related Products

Database Backup

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 Databases

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.

Related Course

The Backup and Recovery of Common Cloud Databases

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.

best data backup service examples of data backup and recovery software sql server backup remote database
0 0 0
Share on

Read previous post:

How to Optimize Data Queries for Time Series Database

Read next post:

How to Install Turtl Server on Alibaba Cloud ECS

Alibaba Clouder

2,605 posts | 747 followers

You may also like

Comments

Alibaba Clouder

2,605 posts | 747 followers

Related Products

More Posts by Alibaba Clouder

See All