Export and import using BAK files

This page describes exporting and importing data into Cloud SQL instances using BAK files.

Before you begin

Exports use database resources, but exports do not interfere with normal database operations unless the instance is under-provisioned.

For best practices, see Best Practices for Importing and Exporting Data.

After completing an import operation, verify the results.

Export data from Cloud SQL for SQL Server

Cloud SQL supports the export of native BAK files.

If you aim to export in order to create a new instance from an exported file, consider restoring from a backup to a different instance or cloning the instance.

Cloud SQL performs a full backup of the selected database during an export operation.

Required roles and permissions for exporting from Cloud SQL for SQL Server

To export data into Cloud Storage, the Cloud SQL instance service account or user must have one of the following sets of roles:

  • The Cloud SQL Editor role and the storage.objectAdmin IAM role.
  • A custom role including the following permissions:
    • cloudsql.instances.get
    • cloudsql.instances.export
    • storage.buckets.create
    • storage.objects.create
    • storage.objects.list (for striped export only)
    • storage.objects.delete (for striped export only)

For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

Export data to a BAK file from Cloud SQL for SQL Server

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Click Export.
  4. In the File format section, click BAK.
  5. In the Data to export section, use the drop-down menu to select the database you want to export from.
  6. In the Destination section, select Browse to search for a Cloud Storage bucket or folder for your export.
  7. Click Export to begin the export.

gcloud

  1. Create a Cloud Storage bucket.
  2. Find the service account for the Cloud SQL instance you're exporting from. You can do this running the gcloud sql instances describe command. Look for the serviceAccountEmailAddress field in the output. 
    gcloud sql instances describe INSTANCE_NAME
  3. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account. For help with setting IAM permissions, see Using IAM permissions.
  4. Export the database: 
    gcloud beta sql export bak INSTANCE_NAME gs://BUCKET_NAME/my-export.bak \
--database=DATABASE_NAME

    For information about using the gcloud beta sql export bak command, see the command reference page.

  5. If you do not need to retain the IAM role you set previously, revoke it now.

REST v1

  1. Create a bucket for the export: 
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the legacyBucketWriter IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. Export your database:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_dump_file: The path to the SQL dump fle
    • database_name_1: The name of a database inside the Cloud SQL instance
    • database_name_2: The name of a database inside the Cloud SQL instance
    • offload: Enables serverless export. Set to true to use serverless export.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/export

    Request JSON body: 

    {
 "exportContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_dump_file",
      "databases": ["database_name"]
      "offload": true | false
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  4. If you do not need to retain the IAM role you set previously, remove it now.
For the complete list of parameters for this request, see the instances:export page.

REST v1beta4

  1. Create a bucket for the export: 
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. Export your database:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_dump_file: The path to the SQL dump fle
    • database_name_1: The name of a database inside the Cloud SQL instance
    • database_name_2: The name of a database inside the Cloud SQL instance
    • offload: Enables serverless export. Set to true to use serverless export.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export

    Request JSON body: 

    {
 "exportContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_dump_file",
      "databases": ["database_name"]
      "offload": true | false
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  4. If you do not need to retain the IAM role you set previously, revoke it now.
For the complete list of parameters for this request, see the instances:export page.

Use striped export

The advantages of striped export are the following:

  • Reductions in the time needed for operations to complete
  • Databases larger than 5 TB can be exported

A potential disadvantage of using striped export is that the backup, rather than consisting of one file, is split across a set of files. This set is called a "stripe set"; see Backup devices in a striped media set (a stripe set). In Cloud SQL, you export to an empty folder in Cloud Storage instead of generating a single file. For more information, see How to use striped export.

Planning your operations

Striped export can improve the performance of exports. However, if your use case requires a single output file, or if your database is less than 5 TB in size, and if faster performance is not critical, you may want to use a non-striped export.

If you decide to use striped export, consider the number of stripes. You can specify this value in your gcloud command or REST API call. However, if you want an optimal number of stripes for performance, or if you don't know a number, omit the number. An optimal number of stripes is set automatically.

The maximum number of stripes currently supported by Cloud SQL for SQL Server is 64.

How to use striped export

gcloud

  1. Create a Cloud Storage bucket.
  2. Find the service account for the Cloud SQL instance you're exporting from. You can do this by running the gcloud sql instances describe command. Look for the serviceAccountEmailAddress field in the output. 
    gcloud sql instances describe INSTANCE_NAME
  3. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account. For help with setting IAM permissions, see Using IAM permissions.
  4. To export the database, specify the --striped parameter and/or specify a value for --stripe_count. Setting a value for --stripe_count implies that the --striped parameter is intended. An error occurs if you specify --no-striped but specify a value for --stripe_count: 
    gcloud beta sql export bak INSTANCE_NAME \
gs://BUCKET_NAME/STRIPED_EXPORT_FOLDER \
--database=DATABASE_NAME --striped --stripe_count=NUMBER

    For information about using the gcloud beta sql export bak command, see the command reference page.

  5. If you do not need to retain the IAM role you set previously, revoke it now.

REST v1

  1. Create a bucket for the export: 
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the legacyBucketWriter IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. Export your database:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_folder: The path to the folder (in the Cloud Storage bucket) to which to export the striped set to
    • database_name: The name of a database in your Cloud SQL instance
    • true | false: Set to true to use striped export. If you specify true without specifying a stripe count, an optimal number of stripes is set automatically
    • number_of_stripes: The number of stripes to use. If specified, striped is implied as true

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/export

    Request JSON body: 

    {
 "exportContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_folder",
      "databases": ["database_name"],
      "bakExportOptions": {
        "striped": true | false,
        "stripe_count": ["number_of_stripes"]
      }
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  4. If you do not need to retain the IAM role you set previously, remove it now.
For the complete list of parameters for this request, see the instances:export page.

REST v1beta4

  1. Create a bucket for the export: 
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but is strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the legacyBucketWriter IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. Export your database:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_folder: The path to the folder (in the Cloud Storage bucket) to which to export the striped set
    • database_name: The name of a database in your Cloud SQL instance
    • true | false: Set to true to use striped export. If you specify true without specifying a stripe count, an optimal number of stripes is set automatically
    • number_of_stripes: The number of stripes to use. If specified, striped is implied as true

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export

    Request JSON body: 

    {
 "exportContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_folder",
      "databases": ["database_name"],
      "bakExportOptions": {
        "striped": true | false,
        "stripe_count": ["number_of_stripes"]
      }
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  4. If you do not need to retain the IAM role you set previously, remove it now.
For the complete list of parameters for this request, see the instances:export page.

Import to Cloud SQL for SQL Server

Required roles and permissions for importing to Cloud SQL for SQL Server

This procedure requires you to import a file from Cloud Storage. To import data from Cloud Storage, the Cloud SQL instance service account or user must have one of the following sets of roles:

  • The Cloud SQL Admin role and the roles/storage.legacyObjectReader IAM role
  • A custom role including the following permissions:
    • cloudsql.instances.get
    • cloudsql.instances.import
    • storage.buckets.get
    • storage.objects.get
    • storage.objects.list (for striped export only)

If the service account or user is also performing export operations, it might be more convenient to grant the Storage Object Admin IAM role to have all the required permissions for both import and export. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

Import data from a BAK file to Cloud SQL for SQL Server

To use striped import, see Use striped import.

Various import frameworks are available. For example, Cloud SQL for SQL Server supports change data capture (CDC) for the following database versions:

  • SQL Server 2017 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2019 Standard
  • SQL Server 2019 Enterprise

When importing a CDC-enabled database, the KEEP_CDC flag is retained.

If your instance version is a Microsoft SQL Server Enterprise Edition, you can import encrypted BAK files. The only supported BAK extensions are .bak and .bak.gz. GPG encrypted backups are not currently supported.

For the instructions below, prepare to specify a new database; do not create a database before starting the import of your BAK file.

To import data to a Cloud SQL instance using a BAK file:

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. To open the Overview page of an instance, click the instance name.
  3. Click Import.
  4. In the Choose the file you'd like to import data from field, enter the path to the bucket and BAK file to use for the import.

    You can import a compressed (.gz) or an uncompressed file.

  5. In the Format section, select BAK.
  6. Specify the Database in your Cloud SQL instance where you want to import the BAK file.
  7. Click the Import to start the import.

gcloud

  1. Create a bucket for the import:

    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Make sure you have configured the required roles and permissions.
  3. Upload the data from the BAK file to the bucket.
  4. Describe the instance you are exporting from: 
    gcloud sql instances describe INSTANCE_NAME
  5. Copy the serviceAccountEmailAddress field.
  6. Use gsutil iam to grant the storage.objectViewer IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  7. Import the data from the file: 
    gcloud beta sql import bak INSTANCE_NAME gs://BUCKET_NAME/FILE_NAME \
--database=DATABASE_NAME
  8. If you do not need to retain the IAM permissions you set previously, remove them using gsutil iam.

REST v1

  1. Create a Cloud Storage bucket.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the data from the file:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_bak_file: The path to the BAK file
    • database_name: The name of a database inside the Cloud SQL instance

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/import

    Request JSON body: 

    {
 "importContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_bak_file",
      "database": "database_name"
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    To use a different user for the import, specify the importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

REST v1beta4

  1. Create a Cloud Storage bucket.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the data from the file:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_bak_file: The path to the BAK file
    • database_name: The name of a database inside the Cloud SQL instance

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import

    Request JSON body: 

    {
 "importContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_bak_file",
      "database": "database_name"
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    To use a different user for the import, specify the importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

If you get an error such as ERROR_RDBMS, ensure the BAK file exists in the bucket and you have the correct permissions on the bucket. For help configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

Use striped import

The advantages of striped import are the following:

  • Reductions in the time needed for operations to complete
  • Databases larger than 5 TB can be imported

A potential disadvantage of using striped import is that all of the files in the striped set (rather than a single file) must be uploaded to the same folder in your Cloud Storage bucket, before you perform the import.

Planning your operations

In most use cases, striped import enables better performance with no disadvantages. However, if you cannot back up to a striped set from a given instance, or if your database is less than 5 TB, and if faster performance is not critical, you may want to use a non-striped import.

How to use striped import

gcloud

  1. Create a bucket for the import:

    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Make sure you have configured the required IAM roles and permissions.
  3. Create a new folder in your bucket.
  4. To import the database, upload the files of the striped set (of the database) to the new folder. Ensure that all the files are uploaded to the folder, and that the folder contains no extra files.
  5. Describe the instance you are exporting from: 
    gcloud sql instances describe INSTANCE_NAME
  6. Copy the serviceAccountEmailAddress field.
  7. Use gsutil iam to grant the storage.objectViewer IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  8. Import the data from the folder. The difference from a non-striped import is the following: The URI links to the name of the folder to which the stripe set was uploaded, rather than to a single file, and you specify the --striped parameter: 
    gcloud beta sql import bak INSTANCE_NAME gs://BUCKET_NAME/FOLDER_NAME \
--database=DATABASE_NAME --striped
  9. If you do not need to retain the IAM permissions you set previously, remove them using gsutil iam.

REST v1

  1. Create a Cloud Storage bucket.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the data from the file:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_folder: The path to the folder (in the Cloud Storage bucket) where the stripe set is located
    • database_name: The name of a database to create in your Cloud SQL instance
    • true | false: Set to true to use striped import

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id/import

    Request JSON body: 

    {
 "importContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_folder",
      "database": "database_name",
      "bakImportOptions": {
        "striped": true | false
      }
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    To use a different user for the import, specify the importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

REST v1beta4

  1. Create a Cloud Storage bucket.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  4. Import the data from the file:

    Before using any of the request data, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_folder: The path to the folder (in the Cloud Storage bucket) where the stripe set is located
    • database_name: The name of a database to create in your Cloud SQL instance
    • true | false: Set to true to use striped import

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import

    Request JSON body: 

    {
 "importContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_folder",
      "database": "database_name",
      "bakImportOptions": {
        "striped": true | false
      }
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    To use a different user for the import, specify the importContext.importUser property.

    For the complete list of parameters for this request, see the instances:import page.
  5. If you do not need to retain the IAM permissions you set previously, remove the permissions.

If you get an error such as ERROR_RDBMS, ensure the table exists. If the table exists, confirm that you have the correct permissions on the bucket. For help configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

What's next