Community Blog MySQL Database and SQL Server Backup and Restore

MySQL Database and SQL Server Backup and Restore

Learn solutions of MySQL database and SQL server backup and restore in the cloud to protect your enterprise data from disaster.

SQL Server Best Practices: Using Filegroups to Implement Cold and Hot Data Isolation Backup Solution

In this article, we'll share how SQL Server can use filegroups to implement the cold and hot data isolation backup solution.

Scenario Description

In our example scenario, we'll assume that a company has a critical large database (over 10 TB) and faces the following scenarios:

  1. The database contains user payment information for nearly 10 years, which is crucial
  2. The annual data is archived and stored in the table for the current year. The data in tables for previous years is read only (the historical payment information no longer needs to be modified), and only the data in the current year's table is both readable and writable
  3. Each full database backup takes too long, more than 20 hours; the database restore operation takes even longer, more than 30 hours

As a Database Administrator, you need to optimize the design of the database and Backup Recovery System to make backup and restore more efficient. But the question is, how can you do this on a SQL Server?

Introduction to Filegroups

This article will not focus on the details of filegroups. However, as it is the core technology involved, it is necessary to briefly introduce filegroups in SQL Server in terms of their advantages, creation, and usage.

Advantages of Using Filegroups

SQL Server supports the storage of table and index data in Non-Primary filegroups, which provides the following benefits when a database has multiple filegroups:

  1. Distributes I/O pressure to different filegroups. If files in different filegroups are on different disks, disk pressure can be distributed.
  2. DBCC CHECKFILEGROUP operations are performed for different filegroups, and the same database can process multiple processes in parallel, reducing the maintenance time for big data.
  3. Backup and Restore operations can be performed at the filegroup level to control backup and restore strategies in a more granular manner.

Creating a Filegroup When Creating a Database

We can create a filegroup directly when we create a database. The code is as follows:

USE master

EXEC sys.xp_create_subdir 'C:\SQLServer\Data\'
EXEC sys.xp_create_subdir 'C:\SQLServer\Logs\'

( NAME = N'TestFG', FILENAME = N'C:\SQLServer\Data\TestFG.mdf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
( NAME = N'FG2010', FILENAME = N'C:\SQLServer\Data\FG2010.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
( NAME = N'FG2011', FILENAME = N'C:\SQLServer\Data\FG2011.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ), 
( NAME = N'FG2012', FILENAME = N'C:\SQLServer\Data\FG2012.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB )
( NAME = N'TestFG_log', FILENAME = N'C:\SQLServer\Logs\TestFG_log.ldf' , SIZE = 5MB , FILEGROWTH = 50MB)

Related Blogs

Database Recovery in GitLab: Implementing Database Disaster Tolerance

How can we restore an accidentally deleted database in GitLab? How can we implement database backup, recovery, disaster tolerance, and high availability? If you are in the database industry, you might be more concerned about these issues recently. Late last year, incidents including Hearthstone data loss and MongoDB hacker extortion highlighted the news. Recently, another incident stole the limelight: data files in GitLab databases were accidentally deleted by the rm -rf command.

Database is a vital for any enterprise. Web SQL injections and accidental deletion of data not only impair businesses but may also lead to user information leaks. Many companies realize the importance of database security and have specialized database administrators (DBA) for data maintenance. The DBA family is like the border warriors in the IT world, and guarding data is one of the most important responsibilities of DBAs. However, despite the many effective measures taken by DBAs, accidents do happen. Sometimes too many operation interfaces and too frequent back-and-forth switches between services may lead to an increased rate of human error.

How can we ensure the safety of data files even after an accidental deletion?

Multiple Copies – High Availability (HA) Guarantee Against Accidental Deletion

In many traditional enterprises, the common means of implementing HA is to mount shared storage among multiple hosts. In fact, only one copy of data is in place, because the availability of storage is much higher than the server. The advantage of this HA approach lies in the simplicity of its structure and thereby easier implementation of data consistency.

The weakness of this HA approach is obvious: there is a single point of failure. Of course, you can use multiple storage devices or physical images to solve single point of failure issues. On the other hand, because there is only one copy of storage, or multiple copies through the image, data deletion is infectious. That is, you can delete either none or all data, which makes it hard to protect data in the case of an rm -rf operation in GitLab.

Multiple copies for HA and disaster tolerance

Beginning with v9.1, PostgreSQL supports the dual-replica synchronous stream replication mode and the multi-replica asynchronous stream replication mode. In v9.6, PostgreSQL adds the new multi-replica synchronization mode.

With this approach, even if the data files in the master database are deleted by an rm -rf operation, because the synchronization is based on REDO, the operating system command will not be infectious, and rm -rf misoperation damages can be kept under control.

Best Practices of Database Disaster Recovery in the DT Era

In the DT era, enterprises are becoming increasingly dependent on data. This article summarizes the Best Practices for Enterprise Database Session at The Computing Conference 2018.

With the arrival of the Data Technology (DT) era, enterprises have become increasingly dependent on data. Data protection has become essential for enterprises, and only those who take preventive measures with sufficient preparations can survive in disasters. In the Best Practices for Enterprise Database Session at The Computing Conference 2018, topics related to disaster recovery attracted much attention. This article introduces the best practices of using Alibaba Cloud database cloud product portfolios to tailor the disaster recovery solutions conforming to the development status of enterprises.

The Value of Data for Enterprises

Data is important resources for production of an enterprise. Once data is lost, the enterprise's customer information, technical documents, and financial accounts may get lost, which may hold back customer relation, transaction, and production. In general, data loss is classified into three levels:

  1. Logical errors, including software bugs, virus attacks, and corruption of data blocks
  2. Physical damages, including server damages and disk damages
  3. Natural disasters, such as fires and earthquakes that may tear down the data centers

To cope with economical loss caused by data loss, enterprises must take disaster recovery measures to protect data. The higher the enterprises' degree of informatization, the more important the disaster recovery measures are.

Enterprise-Class Database Disaster Recovery System

Definition of Disaster Recovery

Disaster recovery involves two elements: disaster tolerance and backup.

  1. Backup is to prepare one or more copies of important data generated by the application systems or original important data.
  2. Disaster tolerance is to deploy two or more IT systems with the same functions at two places that are far away from each other in the same or different cities. These systems monitor the health status of each other and support switchover upon failure. In case that a system stops working due to an accident (a natural or man-made disaster), the entire application system is switched over to another system so that the services are provisioned without interruption.

Comparing Alibaba Cloud ApsaraDB for RDS and MySQL Database Hosted On ECS Instances

MySQL is the one of the most widely used Open-source database management systems. It is secure, flexible and easy to implement on websites and software applications.

MySQL comprehensive set of features makes it a good choice for storing data even for big companies like Youtube, Google and Facebook. Alibaba Cloud has continuously contributed and supported the development of MySQL and they won the 2018 MySQL Community Corporate Contributor Awards.

There are two ways to provision a MySQL database on Alibaba Cloud. The first method involves creating an ApsaraDB instance. This is an on-demand hosted database services.

The other option requires setting up an ECS (Elastic Compute Service) and installing MySQL server on top of an operating system like Linux.

In this guide we will compare Alibaba Cloud ApsaraDB for RDS to a MySQL database server hosted on Alibaba Cloud Elastic Compute Service (ECS). This will help you evaluate the best and suitable MySQL service for deploying your application on the cloud.

Setup and Maintenance

ApsaraDB for RDS is a fully-managed database service that requires a few mouse clicks to provision. This service frees you from administrative tasks for setting up, patching, recovering, detecting and repairing MySQL databases.

ApsaraDB for MySQL is a ready-to-use service that can be setup in less than 5 minutes. After setup, you can connect to the database and start using it as a backend for your application or website. Normally, you will just require the internet address, port, username and password created on the service console to use the database.

A MySQL database deployed on an ECS instance requires knowledge in both system and database administration. Installing and implementing MySQL database in this way takes a steep learning curve. To use the database, you must provision an operating system like Ubuntu 16.04 on Alibaba Cloud and install the software using package management tools like APT.

If a problem occurs on the MySQL database hosted on the ECS instance, you will have to personally troubleshoot the error to restore the database service. Remember the error can range from operating system failures, hardware problems, and memory issues. So unless you have in-depth knowledge in both system and database administration, ApsaraDB is a safer choice.

Backup and Security

A database holds valuable information that must be safeguarded at all times. ApsaraDB for RDS makes your life easier by integrating security in the database solution. One of the worst enemies of MySQL database is SQL injection – a technique used by hackers to steal information from your database through malicious codes.

Alibaba Cloud ApsaraDB can intercept such attacks. On top of this, the service is built to withstand brute-force attacks – a method that tries to gain access to a database system by guessing login credentials.

When it comes to backup ApsaraDB has advanced disaster capabilities that live-copies data using Object Storage Service (OSS) for easy recovery process.

On the other hand, if you provision a MySQL server on Alibaba Cloud ECS, you must install and maintain the appropriate backup tools and applications. There are different approaches that you can take to create backups. For instance, you can use automatic backup scripts or copy data on different servers and this can increase the overall cost of running the service.

To safeguard the data on the ECS instances, you must ensure that both the operating system and the database server are protected. This involves setting up firewalls, anti-brute force tools like Fail2Ban.

If you don't have knowledge in such technologies, ensuring the security of your MySQL server can be overwhelming. ApsaraDB takes the pain away and incorporates security features by default. Hence, you can concentrate on your core business functions while Alibaba Cloud safeguards the database for you.

Related Courses

MySQL Database Concepts and Operations

After learning this MySQL series courses, the trainees will master the MySQL relevant basic concept, deployment procedure, HA solution, commonly used backup and recovery method and daily inspection skills, thus having a comprehensive understanding of MySQL database.

Alibaba Cloud Database Backup Service Technical Essentials

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. Through this course, you will not only learn about Alibaba Cloud DBS technical essentials and applicable scenarios, but also by watching product console demos, you will be familiar with DBS major functions and operation details.

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.

Related Market Products

Mysql Database Concepts and Operations

After learning this MySQL series courses, the trainees will master the MySQL relevant basic concept, deployment procedure, HA solution, commonly used backup and recovery method and daily inspection skills, thus having a comprehensive understanding of MySQL database.

The Backup and Recovery of Common Cloud Databases

Learn about cloud database security concepts, MySQL and SQL Server backup and recovery concepts, types, and methods.

Related Documentation

Manage backup plans - Database Backup

DBS offers features such as full backup, incremental backup, and data recovery. You can manage and configure backup plans at any time.


Log on to the DBS console, go to the Backup Plans list, and click manage to enter the details page of a backup plan. The detailed procedure is as follows:

  1. Configure backup source
  2. Configure backup object
  3. Configure backup schedule
  4. Configure lifecycle
  5. Enable or disable incremental backup

Configure backup source

On the backup plan details page, click Configure backup source to change the database settings such as its location and IP address. The steps are the same as described in Configure a backup plan.

Configure backup object

On the backup plan details page, click Configure backup object to change the database objects for backup. The steps are the same as described in Configure a backup plan.

Configure backup schedule

On the backup plan details page, click Backup Schedule to change the time settings of the backup plan. The steps are the same as described in Configure a backup plan.

Configure lifecycle

On the backup plan details page, click set lifecycle to change the lifecycle settings of the backup plan. The steps are the same as described in Configure a backup plan.

Enable or disable incremental backup

On the backup plan details page, click disable incremental backup to disable incremental backups. The service will only perform full backups and not provide continuous data protection. To enable incremental backups for continuous data protection, click Enable incremental backup.

Back up a SQL Server database - Hybrid Backup Recovery

You can use Hybrid Backup Recovery (HBR) to back up SQL Server databases deployed on Alibaba Cloud Elastic Compute Service (ECS) instances and restore these databases as required. This topic describes how to back up SQL Server databases.


Before you back up SQL Server databases, note the following points:

  1. Make sure that .NET Framework 4.5 or later is installed.
  2. HBR supports the following SQL Server versions: SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017. For more information, see Limits.
  3. SQL Server databases cannot be installed in a file system where compression is enabled. For more information about limits on SQL Server installation, see File Locations for Default and Named Instances of SQL Server.

Create a backup plan

To create a backup plan, follow these steps:

  1. Log on to the HBR console.
  2. In the left-side navigation pane, choose Backup > ECS Application Backup. On the ECS Application Backup page, select SQL Server.
  3. On the page that appears, click the SQL Server Instance tab. On the SQL Server Instance tab, find the target SQL Server instance and click Backup in the Actions column.
  4. In the Create Plan pane that appears, select the databases to be backed up.

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.

ApsaraDB for PolarDB

ApsaraDB for PolarDB is a cloud-native relational database compatible with MySQL, PostgreSQL, and Oracle. ApsaraDB for PolarDB provides the performance and availability of traditional enterprise databases and the flexibility and cost-effectiveness of open-source databases.

0 0 0
Share on

Alibaba Clouder

2,624 posts | 721 followers

You may also like