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 databaseepoch
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 databaseepoch
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 identifierfamily_guid
is the Amazon RDS unique identifier of the original database at creationrds_backup_seq_id
is the identifier that Amazon RDS uses internally to maintain a sequence number for each transaction log backup fileepoch
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:
- 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.
- 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
- 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:
- 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.
- Set up the native backup capability. See Setting up for native backup and restore in the Amazon RDS documentation.
- 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;
- 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:
- 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.
- 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';
- 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:
- 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;
- Copy the transaction log backup files from your S3 bucket to the Cloud Storage storage bucket you created for your migration.