Operation Scenarios

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

• Before migration, please 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, please make sure that each .bak file contains only one database.
• The name of the migrated database cannot 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

• The backup file name cannot 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.
• After migration, the database on the target instance is read-only, and incremental migration needs to be performed.

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 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, enter the relevant information and click OK.
   • 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 "Actions" column.
5. Select the File List page, click Upload Files, and select one or more files for 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 Through a Source File in COS

1. Log in to the TencentDB for SQL Server Console.
2. Select Data Transfer on the left sidebar and click Create Task to create an 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.
   • Link to Source File in COS: You can view the file information to get the COS object address after uploading the source file.
   • Target Database Type and Region: They are automatically generated by the system based on the source database configuration.
   • Instance ID: Select the target instance. You can only select an instance in the same region.
     
3. After completing the configuration, click Next.
4. Currently, type and database settings can be changed. Click Create Task.
5. Return to the task list. At this time, the task status is initializing. Select the task at the top of the list and click Start to sync the task.
6. After the data sync is completed (i.e., the progress bar shows 100%), you need to click Finish at the top of the list to end the sync process. You can check whether the migration is successful on the status.
   • If the task status is *task successful, the data migration is successful.
   • If the status is *task failed, the data migration failed. Please check the failure information, fix it accordingly, and then migrate again.

Incremental Backup Migration

Preparing a Backup File

• 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.

  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

• You shut down the server, perform incremental backup, export the backup file, upload it, and then migrate the data.

  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.