Prepare the full backup file and transaction log backup files

Preview — Database Migration Service for homogeneous SQL Server migrations

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

This page describes how to create the backup files required for migrating your SQL Server databases to Cloud SQL for SQL Server, including the following sections:

• Requirements and limitations lists important limitations for backup files support.
• Naming conventions explains the requirements around file names for your backup files.
• Take a full backup contains steps for creating full backup files.
• Transaction log backup files contains steps for creating transaction log backup files.

Requirements and limitations

The following requirements and limitations apply to the backup files you use for migration:

• Database Migration Service supports only full backups and transaction log backups. Other backup types aren't supported.

• The full backup files and transaction log backup files must names that adhere to the naming conventions.

• Your backup files can have a maximum size of 5 TB.

• You can stripe your full backup file into smaller files if your database size exceeds 5 TB. Each stripe can have a maximum size of 5 TB.

• Database Migration Service doesn't migrate the SQL Server master database.

• Your source SQL Server instance can't use the simple recovery model. For more information on recovery models, see Recovery Models (SQL Server) in Microsoft documentation.

Naming conventions

This section covers the file name requirements for your full and transaction log backup files. For information on Cloud Storage bucket folder name and structure requirements, see Create and configure Cloud Storage buckets.

Backup file naming conventions for on-premise SQL Server instances

Full backup file names

The naming convention for full backup files depends on whether you use a single backup file, or if you divide it into multiple stripes. In both scenarios, Database Migration Service requires that you include an epoch timestamp (expressed as a Unix timestamp in seconds) that represents the approximate time when you took the full backup.

• If your full backup is a single file, the file name must use the db_name.epoch.bak format, where:

  • db_name is the name of your database
  • epoch is a Unix timestamp in seconds
  • .bak is the file extension

  Valid examples include:

  • my-business-database.1712649600.bak
  • my-other-database.1712649600.bak

• If you divide your full backup into separate stripes, each stripe can use any name you choose, but they must be uploaded to a Cloud Storage folder whose name is a Unix timestamp in seconds:

  Cloud Storage bucket root/
  |- my-business-database/
  |  |- full/
  |  |  |- 1712649600/
  |  |  |  |- mystripe-full_backup_file.bak
  |  |  |  |- otherStripefull-bkp.bak
  |  |- logs/
  |  |  |- ...
  |- my-other-database/
  |  |- full/
  |  |  |- 1715250742/
  |  |  |  |- cool-stripe-file.bak
  |  |  |  |- secondStripeFile.bak
  |  |- logs/
  |  |  |- ...
  

Transaction log file names

Your transaction log file names must use the db_name.epoch.trn format, where:

• db_name is the name of your database
• epoch is a Unix timestamp in seconds
• .bak is the file extension

Valid examples include:

• my-business-database.1712649600.trn
• my-other-database.1712649600.trn

Backup file naming conventions for Amazon RDS

Full backup file names

The naming convention for full backup files depends on whether you use a single backup file, or if you divide it into multiple stripes. In both scenarios, Database Migration Service requires that you include an epoch timestamp (expressed as a Unix timestamp in seconds) that represents the approximate time when you took the full backup.

• If your full backup is a single file, the file name must use the db_id.family_guid.rds_backup_seq_id.epoch.bak format, where:

  • db_id is the Amazon RDS internal database identifier
  • family_guid is the Amazon RDS unique identifier of the original database at creation
  • rds_backup_seq_id is the identifier that Amazon RDS uses internally to maintain a sequence number for each transaction log backup file
  • epoch is a Unix timestamp in seconds
  • .bak is the file extension

  Valid examples include:

  • 3.CX11CB3D-G2E4-46D9-B462-CE40CDA97E89.22.1712649600.bak
  • 1.FD21CD3F-B5E4-27Z9-B462-CE40CDA97E89.21.1712649600.bak

• If you divide your full backup into separate stripes, each stripe can use any name you choose, but they must be uploaded to a Cloud Storage folder whose name is a Unix timestamp in seconds:

  Cloud Storage bucket root/
  |- my-business-database/
  |  |- full/
  |  |  |- 1712649600/
  |  |  |  |- mystripe-full_backup_file.bak
  |  |  |  |- otherStripefull-bkp.bak
  |  |- logs/
  |  |  |- ...
  |- my-other-database/
  |  |- full/
  |  |  |- 1715250742/
  |  |  |  |- cool-stripe-file.bak
  |  |  |  |- secondStripeFile.bak
  |  |- logs/
  |  |  |- ...
  

Transaction log file names

In Amazon RDS, transaction log backup files are generated automatically and they include all the required database identifiers. These default names include the epoch timestamp and are fully supported in Database Migration Service. You can upload these files to your /logs/ folder directly.

Take the full backup of your source instance

The full database backup file is required for the initial load phase of your migration job. If you want to encrypt your backups, make sure you store the encryption key so that you can later upload it to Cloud Storage when you create the migration job. See Use encrypted backups.

Take full backup for on-premise SQL Server instances

You can take the full backup of your on-premise SQL Server instance by using T-SQL or SQL Server Management Studio (SSMS). Follow these steps:

1. Make sure you meet the following requirements:
   • You use the correct naming requirements for your backup files.
   • Your source databases don't use the simple recovery model. See Recovery Models (SQL Server) in Microsoft documentation.
   • If your database size exceeds 5 TB, make sure you divide the full backup into separate stripes. Each stripe can have a maximum size of 5 TB. See Backup devices in a striped media set.
2. Take the full backup of your source instance. See Quickstart: Backup and restore a SQL Server database with SSMS in Microsoft documentation. You can also check the following sample commands:

   Example T-SQL command for a single full backup file

   This sample command takes a full backup of the AdventureWorks2022 database. The resulting backup file uses the required naming convention.

   declare @DATABASE varchar(1000);
   SELECT @DATABASE = 'AdventureWorks2022';
   
   declare @unixTimestamp varchar(1000);
   SELECT @unixTimestamp = CAST(DATEDIFF(s, '1970-01-01', GETUTCDATE()) AS VARCHAR(50));
   
   BACKUP DATABASE @DATABASE
   TO DISK = 'X:\SQLServerBackups\full\' + @DATABASE + '.' + @unixTimestamp + '.bak';
   GO
   

   Example T-SQL command for striped full backup files

   This sample command takes a full backup of the AdventureWorks2022 database. The resulting backup file uses the required naming convention.

   declare @DATABASE varchar(1000);
   SELECT @DATABASE = 'AdventureWorks2022';
   
   declare @unixTimestamp varchar(1000);
   SELECT @unixTimestamp = CAST(DATEDIFF(s, '1970-01-01', GETUTCDATE()) AS VARCHAR(50));
   
   BACKUP DATABASE @DATABASE
   TO DISK = 'X:\SQLServerBackups\full\' + @DATABASE + '.' + @unixTimestamp + '\stripe1.bak',
   DISK = 'X:\SQLServerBackups\full\' + @DATABASE + '.' + @unixTimestamp + '\stripe2.bak',
   DISK = 'X:\SQLServerBackups\full\' + @DATABASE + '.' + @unixTimestamp + '\stripe3.bak'
   WITH FORMAT,
     MEDIANAME = 'AdventureWorksStripedSet0',
     MEDIADESCRIPTION = 'Striped media set for ' + @DATABASE + ' database';
   GO
   

3. Upload your backup file to a Cloud Storage bucket. See Create and configure a Cloud Storage storage bucket the Cloud Storage storage bucket.

Take full backup for Amazon RDS

To take the full backup of your Amazon RDS source, follow these steps:

1. Make sure you meet the following requirements:
   • You use the correct naming requirements for your backup files.
   • Your source databases don't use the simple recovery model. See Determining a recovery model for your Microsoft SQL Server database in the Amazon RDS documentation.
   • If you want to use transaction log backup after you take the full backup, enable automated backups on your Amazon RDS instance.
2. Set up the native backup capability. See Setting up for native backup and restore in the Amazon RDS documentation.
3. Take the full backup of your source instance. If your database size exceeds 5 TB, make sure you divide the full backup into separate stripes by using the @number_of_files parameter. Each stripe can have a maximum size of 5 TB. See Backing up a database in Amazon RDS documentation.

   You can also check the following sample command:

   Example query to take a full backup on Amazon RDS

   This sample command takes a full backup of the AdventureWorks2022 database. The resulting backup file uses the required naming convention.

   declare @DATABASE varchar(1000);
   declare @S3Bucket varchar(1000);
   declare @unixTimestamp varchar(30);
   declare @fileName varchar(1000);
   
   SELECT @S3Bucket = 'dms-sqlserver-exports';
   SELECT @DATABASE = 'AdventureWorks2022';
   
   SELECT @unixTimestamp=CAST(DATEDIFF(s, '1970-01-01', GETUTCDATE()) AS VARCHAR(50));
   SELECT @fileName = ('arn:aws:s3:::' + @S3Bucket + '/' + @DATABASE + '/full/' + @DATABASE '.' + @unixTimestamp + '/' + CAST(database_id AS VARCHAR(10)) + '.' + CAST(family_guid AS VARCHAR(50)) + '.*.' + @unixTimestamp) FROM master.sys.database_recovery_status WHERE DB_NAME(database_id)=@DATABASE;
   
   exec msdb.dbo.rds_backup_database
     @source_db_name=@DATABASE,
     @s3_arn_to_backup_to=@fileName,
     @overwrite_s3_backup_file=1,
     @type='FULL',
     @number_of_files=1;
   

4. Upload your backup file to a Cloud Storage bucket. See Create and configure a Cloud Storage storage bucket the Cloud Storage storage bucket.

Take transaction log backups

Transaction log backup files contain the changes changes that occur in your source database after you take the full backup. Transaction log backups are required for the continuous load phase of your migration job. You can schedule regular transaction log exports and uploads, to ensure data updates are continuously replicated to your destination Cloud SQL for SQL Server instance. See Schedule transaction log backups.

Take a transaction log backup for on-premise SQL Server instances

To take a transaction log backup of your on-premise SQL Server databases, follow these steps:

1. Make sure you meet the following requirements:
   • You use the correct naming requirements for your backup files.
   • Your source databases don't use the simple recovery model. See Recovery Models (SQL Server) in Microsoft documentation.
2. Export the transaction log files from your SQL Server source databases. See Back up a transaction log in Microsoft documentation. You can also check the following sample command:

   Example T-SQL command to export transaction log files to local disk

   This sample command creates a transaction log backup of the AdventureWorks2022 database and saves the file on local disk. The resulting backup file uses the required naming convention.

   declare @DATABASE varchar(1000);
   SELECT @DATABASE = 'AdventureWorks2022';
   declare @unixTimestamp varchar(1000);
   SELECT @unixTimestamp = CAST(DATEDIFF(s, '1970-01-01', GETUTCDATE()) AS VARCHAR(50));
   
   BACKUP LOG @DATABASE
   TO DISK = 'X:\SQLServerBackups\log\' + @DATABASE + '.' + @unixTimestamp + '.trn';
   

3. Upload the transaction log backup files to the Cloud Storage storage bucket you created for your migration.

Take a transaction log backup for Amazon RDS

Transaction log backups are created automatically if automated backups are enabled on your Amazon RDS instance. See Enable automated backups in Amazon RDS documentation.

To use transaction log backup files for your migration, follow these steps:

1. Enable access to transaction log files in your Amazon RDS instance. See Access to transaction log backups with RDS for SQL Server. You can also check the following sample command:

   Example stored procedure call to enable transaction log access in Amazon RDS

   declare @DATABASE varchar(1000);
   declare @S3Bucket varchar(1000);
   declare @targetS3Arn varchar(1000);
   
   
   SELECT @S3Bucket = 'dms-sqlserver-exports';
   SELECT @DATABASE = 'AdventureWorks2022';
   SELECT @targetS3Arn = ('arn:aws:s3:::' + @S3Bucket + '/' + @DATABASE + '/log/');
   
   USE @DATABASE;
   exec msdb.dbo.rds_tlog_copy_setup
   @target_s3_arn=@targetS3Arn;
   

2. Copy the transaction log backup files from your S3 bucket to the Cloud Storage storage bucket you created for your migration.