tencent cloud

Overview

TencentDB for SQL Server supports data migration using COS files. The migration method described in this document is also applicable to migration from a SQL Server instance purchased at other cloud service provider or built in-house to a TencentDB for SQL Server instance on the same version.

Note：

• Before migration, make sure that the SQL Server version of the target instance is the same as that of the source instance.
• For the bak files used for migration, make sure that each bak file contains only one database.
• The name of the migrated database can’t be the same as that of the TencentDB for SQL Server instance.

Full Backup Migration

Preparing a Backup File

There are two ways to prepare a full backup file:

Full Backup After Shutdown

• You shut down your SQL Server instance purchased at other cloud service provider or your self-built server, back up the entire database, and then export the backup file (which must be in .bak format). The server should be shut down until the migration is completed.
• If you choose full backup migration after shutdown, you do not need to perform incremental backup migration separately.

Full Backup Without Shutdown

Note：

• The backup file name can’t be customized and must follow the naming convention in the script.
• After full backup is performed, you need to perform incremental backup restoration until the data on the source instance is the same as that on the target instance.
• If the filename contains 1full1, it is full backup.
• In the incremental restoration scenario, after backup upload and data migration are completed, the database will be in "Restoring" status before the last incremental backup and restoration is performed. At this time, the database can’t be accessed, which is normal. You need to perform the last incremental backup and restoration to make the database accessible.

You do not need to shut down your SQL Server instance purchased at another cloud service provider or your self-built server. Perform backup of the database and export the backup file.

---If incremental restoration is not required:
backup database db to disk='d:\db.bak' with init
---If incremental restoration is required:
declare @dbname varchar(100)
declare @localtime varchar(20)
declare @str varchar(max)
set @dbname='db'
set @localtime =replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
set @str='BACKUP DATABASE [' + @dbname + '] TO  DISK = N''d:\dbbak\' + @dbname + '_' + @localtime + '_1full1_1noreconvery1.bak'' WITH INIT'
exec(@str)
go

Uploading the Backup File to COS

1. Log in to the COS console.
2. Select Bucket List on the left sidebar, and click Create Bucket
3. In the pop-up dialog box, configure corresponding information, and clickOK.

• The region of the bucket needs to be the same as that of the SQL Server instance to migrate to.
• Cross-region migration with COS is not supported.

4. Return to the bucket list and click the bucket name or Configuration Management in the "Operation" column.
5. In File List, click Upload Files, and you can select one or multiple local files to upload.
6. After the file is uploaded, click the bucket name and obtain the Object Address from the basic information in the basic configuration section.
   

[Migrating Data Via COS File] (id:qianyi_shuju)

1. Log in to the TencentDB for SQL Server Console
2. Select Data Migration (Legacy) in the left sidebar, and clickCreate Task to create new offline migration task.

• Task Name: Custom.
• Source Instance Type: Select SQL Server backup recovery (COS mode).
• Region: The region of the source database must be the same as that of the source file in COS.
• COS File URL: You can view the file information to get the COS object address after uploading the source file to COS.
• Target Database Type and Region: They are automatically generated by the system based on the source database configuration.
• Instance ID: Select the target instances that are located in the same region.
• Rename Database: Select “Enable or not.”, and you need to enter database name.

  Note：

  Once enabled, the original database name in the backup file will be reset and then designated as the new database name after it is restored to the cloud database. (Prerequisite: Only one database can be included in the backed up bak file)

  

3. After completing the configuration, click Next.
4. Currently, type and database settings can be changed. Click Create Task.
5. Return to task list, and the task status is Initializing. Click Start above the list to sync the task.
6. After the data sync is completed (i.e., the progress bar shows 100%), you need to click Complete at the top of the list to end the sync process. You can check whether the migration is successful on the Status column.

• If the task status is *Task succeeded, the data migration is successful.
• If the status is *Task failed, the data migration failed. You need to check the failure information, fix it accordingly, and then migrate again.

Incremental Backup Migration

Preparing a Backup File

Note：

• The backup file name cannot be customized and must follow the naming convention in the script.
• If the filename contains 1diff1, it is incremental backup.

• (Optional) When there are multiple incremental backup files, all of them except the last one can be generated in the following way. They should be uploaded for data migration in sequence; otherwise, the migration will fail.

  Note：

  After "backup generation, backup upload, and data migration" are completed, the database will be in "restoring" status. At this time, it can’t be accessed, which is normal. You can repeat this operation until the last backup upload and migration, and then perform the next step (i.e., "last incremental backup after server shutdown").

  declare @dbname varchar(100)
  declare @localtime varchar(20)
  declare @str varchar(max)
  set @dbname='db'
  set @localtime =replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
  set @str='BACKUP DATABASE [' + @dbname + '] TO  DISK = N''d:\dbbak\' + @dbname + '_' + @localtime + '_1diff1_1noreconvery1.bak'' WITH DIFFERENTIAL, NOFORMAT, INIT'
  exec(@str)
  go
  

• If the server is shut down, you need to perform an incremental backup, export the backup files, perform subsequent file upload and data migration.

  Note：

  Only after this operation is performed can the database be accessed normally.

  declare @dbname varchar(100)
  declare @localtime varchar(20)
  declare @str varchar(max)
  set @dbname='db'
  set @localtime =replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
  set @str='BACKUP DATABASE [' + @dbname + '] TO  DISK = N''d:\dbbak\' + @dbname + '_' + @localtime + '_1diff1_1reconvery1.bak'' WITH DIFFERENTIAL, NOFORMAT, INIT'
  exec(@str)
  go
  

Uploading Backup File and Migrating Data

1. After preparing the backup file, perform subsequent file upload and data migration as instructed in Uploading Backup File to COS and Migrating Data Through a Source File in COS.
2. After importing the final incremental backup file (i.e., the .bak file containing _1diff1_1reconvery1), the status of target instance status will change from read-only to usable, and you can switch your business to the TencentDB for SQL Server instance.