All Products
Search
Document Center

Back up SQL Server databases

Last Updated: May 30, 2020

Database Backup (DBS) provides features such as full backup, incremental backup, and data restoration. This topic describes how to use the physical backup method to back up data in SQL Server.

Click here to request a free trial of DBS>>.

Features

Feature Description
Support for major versions of SQL Server DBS supports the following versions of SQL Server: 2000, 2005, 2008R2, 2012, 2014, 2016, 2017, and 2019.
Streaming backup DBS backs up data without generating temporary files that occupy local storage space.
Multiple compression methods DBS supports native compression schemes of SQL Server databases and the LZ4 compression algorithm.
Automatic upgrade The latest version of DBS supports automatic upgrade of a backup gateway.
Restoration to ApsaraDB for RDS DBS allows you to restore physical backup data to ApsaraDB RDS for SQL Server, ensuring the data integrity.

Procedure

1. Add a backup gateway

The backup gateway is the client of DBS and is also an agent installed on your database server to back up data from on-premises databases to Object Storage Service (OSS). You must install the backup gateway before you can use the physical backup method.

For more information, see Add a backup gateway.

2. Create a physical backup schedule

Go to the DBS buy page to create a physical backup schedule.

3. Configure the backup schedule

Configure the backup schedule. For more information, see Configure a backup schedule.

  • Backup Mode: The backup method can be selected when you purchase the schedule and cannot be changed.
  • Backup Gateway: Select an existing backup gateway or install a new one for the source database.
  • Database Type: The database type can be selected when you purchase the schedule and cannot be changed.
  • Address: Specify the local IP address of the database instance. Example: 127.0.0.1.
  • Port: Specify the port of the database instance.
  • Instance Region: Specify the region of the schedule. In a local backup operation, the region of the source database instance is the same as that of the schedule. In a remote backup operation, the source database instance and the schedule are in different regions.

4. View the backup schedule

After you have configured a backup schedule, DBS automatically schedules backup tasks. If you have enabled incremental backup, the service will perform incremental backups in real time.

To view the status of a specific backup task, you can log on to the DBS console and go to the details page of the corresponding backup schedule. For example, if you want to view the status of a backup task, log on to the DBS console. In the left-side navigation pane, click Backup Schedules. On the Backup Schedules page that appears, click Manage in the Actions column corresponding to the specified backup schedule to go to the configuration page of the schedule.

For more information, see View backup plans.

5. Restore a database

Log on to the DBS console. In the left-side navigation pane, click Backup Schedules. On the Backup Schedules page that appears, click Manage in the Actions column corresponding to the specified backup schedule. On the configuration page of the schedule, click Restore Database in the upper-right corner.

  • DBS supports restoration to new ApsaraDB for RDS instances.
  • DBS supports restoration to user-created databases on which backup gateways are installed.

For more information, see Restore a database.

FAQ

Copy-only full backup

Recommended setting Description
  • Enable this feature only when DBS is used to back up data.
  • No recommendation is provided when DBS and other backup tools are used together.
When you configure a backup task, you can enable copy-only full backup so that full backup operations will not affect subsequent differential and incremental backup operations.

Copy-only incremental backup

Recommended setting Description
  • Enable this feature only when DBS is used to back up data.
  • No recommendation is provided when DBS and other backup tools are used together.
When you configure a backup task, you can enable copy-only incremental backup so that incremental backup operations will not affect subsequent backup operations on transaction logs.

Backup set compression

Recommended setting Description
Enable this feature. When you configure a backup task, you can enable backup set compression. Then, DBS will compress the full backup data by using computing resources of the source database. This reduces the network traffic and storage space required to upload the data to OSS.

How do I back up a Mirror node or Secondary Replica node in SQL Server?

Configure the backup schedule as follows:

  • Enable copy-only full backup.
  • Disable copy-only incremental transaction log backup.

What do I do if the “BACKUP LOG cannot be performed because there is no current database backup” error message is returned?

Cause:

Before performing the transaction log backup for SQL Server for the first time, you did not perform a successful non-copy-only full backup.

Solution:

  1. Check whether copy-only full backup is enabled when you configure the DBS backup task. If the feature is enabled, disable it and perform a DBS full backup. Then, start a DBS incremental backup.

  2. You can also manually perform a non-copy-only full backup, and then start a DBS incremental backup.

What do I do if the logon is from an untrusted domain and cannot be used together with Windows authentication?

Check the backup schedule configuration, and make sure that the connection IP address is the loopback IP address, which is 127.0.0.1.