Backup SQL Server databases to a Cloud Storage bucket

Create a Cloud Storage bucket

You can create a Cloud Storage bucket using the Google Cloud console or using gcloud storage command.

To create Cloud Storage bucket using the gcloud storage command, use the following steps.

1. Select your Google Cloud project.

   gcloud config set project PROJECT_ID
   

2. Create the bucket. To create the bucket run the gcloud storage buckets create command as follows.

     gcloud storage buckets create gs://BUCKET_NAME --location=BUCKET_LOCATION
   

   Replace the following:

• BUCKET_NAME: with a unique name for your bucket.
• BUCKET_LOCATION: with the location of your bucket.

Configure S3 interoperability and create an access key

To allow SQL Server to communicate with Cloud Storage using the S3 protocol, you must enable interoperability and generate an access key using the following steps:

1. Navigate to your Cloud Storage settings in the Google Cloud console.

   Go to Cloud Storage settings

2. Select the Interoperability tab.

3. Under Access keys for your user account, click Create a key.

   

4. Save the generated Access key and Secret securely. You will need them in the next step.

For production environments, we recommend that you use a service account hash-based message authentication code (HMAC) key for better security and management.

Add Credentials to SQL Server

To allow SQL Server to authenticate with your Cloud Storage bucket, you must create a credential object within SQL Server to store the Cloud Storage access key and secret. To do this, run the following T-SQL command in SQL Server Management Studio (SSMS).

CREATE CREDENTIAL CREDENTIAL_NAME
WITH
    IDENTITY = 'S3 Access Key',
    SECRET = 'ACCESS_KEY:SECRET';

Replace the following:

• CREDENTIAL_NAME: with a name for your credentials.
• ACCESS_KEY: with you access key you created in the previous section.
• SECRET: with the secret you created in the previous section.

IDENTITY = 'S3 Access Key' is crucial as it tells SQL Server to use the new S3 connector. The secret should be formatted as the access key, followed by a colon, and then the secret key.

Example:

CREATE CREDENTIAL sql_backup_credentials
WITH
    IDENTITY = 'S3 Access Key',
    SECRET = 'GOOGGE3SVF7OQE5JRMLQ7KWB:b31Pl8Tx1ARJfdGOsbgMFQNbk3nPdlT2UCYzs1iC';

Back up your data to Cloud Storage

With the credential in place, you can now back up your database directly to the Cloud Storage bucket using the BACKUP DATABASE command with the TO URL option. Prefix the URL with s3://storage.googleapis.com to use the S3 connector, as follows.

BACKUP DATABASE DATABASE_NAME
TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/BACKUP_FILE_NAME.bak'
WITH
    CREDENTIAL = 'CREDENTIAL_NAME',
    FORMAT,
    STATS = 10,
    MAXTRANSFERSIZE = 10485760,
    COMPRESSION;

Replace the following:

• CREDENTIAL_NAME The name of the credential you created in step 3. For example — sql_backup_credentials.
• BUCKET_NAME The name of the bucket you created in step 1.
• FOLDER_NAME The name of the folder you want to store the backup file in.
• BACKUP_FILE_NAME The name of the backup file.

The descriptions of the backup parameters used in the command are as follows:

• FORMAT: overwrites the existing backup files and creates a new media set.
• STATS: reports the backup progress.
• COMPRESSION: compresses the backup, which can reduce file size and upload time.
• MAXTRANSFERSIZE: recommended to avoid I/O errors with large backup files.

For more information see SQL Server back up to URL for S3-compatible object storage.

For very large databases, you can split the backup into multiple files as follows.

BACKUP DATABASE DATABASE_NAME
TO
    URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/BACKUP_FILE_NAME_0.bak',
    URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/BACKUP_FILE_NAME_1.bak'
    -- ... more files
WITH
    CREDENTIAL = 'CREDENTIAL_NAME',
    FORMAT,
    STATS = 10,
    MAXTRANSFERSIZE = 10485760,
    COMPRESSION;

Restore your data from Cloud Storage

You can restore your database directly from a backup file stored in Cloud Storage using the RESTORE DATABASE command as follows.

RESTORE DATABASE DATABASE_NAME
FROM URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/BACKUP_FILE_NAME.bak'
WITH
    CREDENTIAL = 'CREDENTIAL_NAME';

Replace the following:

• CREDENTIAL_NAME The name of the credential you created in step 3. For example — sql_backup_credentials.
• BUCKET_NAME The name of the bucket you created in step 1.
• FOLDER_NAME The name of the folder you want to store the backup file in.
• BACKUP_FILE_NAME The name of the backup file.