Using Microsoft SQL Server backups for point-in-time recovery on Compute Engine

Last reviewed 2023-06-27 UTC

---

Note: This document or section includes references to one or more terms that Google considers disrespectful or offensive. The terms are used because they are keywords in the software that's described in the document.

The terms: master

In this tutorial, you perform backups on a Compute Engine SQL Server instance. The tutorial shows you how to manage these backups and store them in Cloud Storage and how to restore a database to a point in time.

This tutorial is useful if you are a sysadmin, developer, engineer, database admin, or devops engineer who wants to back up SQL Server data.

Note: Although managing backups is crucial for any disaster recovery plan, this tutorial doesn't address the topic of disaster recovery for SQL Server.

The tutorial assumes that you are familiar with the following:

• Microsoft Windows
• Microsoft SQL Server
• SQL Server full, differential, and transaction log backups
• Compute Engine
• Cloud Storage

Objectives

• Launch a SQL Server instance and create a database.
• Perform full, differential, and transaction log backups.
• Upload the backups to Cloud Storage.
• Restore the database from a Cloud Storage backup.

Costs

In this document, you use the following billable components of Google Cloud:

• Compute Engine
• Networking
• Cloud Storage
• SQL Server (premium with compute engine)

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

Before you begin

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the Compute Engine API.

   Enable the API

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the Compute Engine API.

   Enable the API

8. Install a Remote Desktop Protocol (RDP) client of your choice. For more information, see Microsoft Remote Desktop clients. If you already have an RDP client installed, you can skip this task.

When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, see Clean up.

Preparing the SQL Server instance

In this section, you launch the SQL Server instance, prepare the database, and configure an encryption key.

Launch the SQL Server instance

Your first task is to launch a SQL Server instance and create the backup folder.

1. Open Cloud Shell:

   GO TO Cloud Shell

2. Launch a SQL Server instance:

   gcloud compute instances create sqlserver \
       --zone=us-central1-c \
       --machine-type=n1-standard-1 \
       --image-family=sql-std-2019-win-2019 \
       --image-project=windows-sql-cloud \
       --boot-disk-size=50GB \
       --boot-disk-type=pd-standard \
       --tags=sqlserver \
       --scopes=https://www.googleapis.com/auth/cloud-platform
   

3. Go to the VM instances page in the Google Cloud console and find the Windows instance you want to connect to:

   GO TO THE VM INSTANCES PAGE

4. Set the initial password for the instance. Store the password in a safe place.

5. In the Compute Engine section of the Google Cloud console, click the RDP dropdown and select the Download the RDP file option to download the RDP file for your instance. Use this file to connect to the instance using an RDP client. For more information, see Microsoft Remote Desktop clients.

Install SQL Server Management Studio

Install Microsoft SQL Server Management Studio (SSMS) by doing the following:

1. In your RDP session, minimize all windows, and start the Windows PowerShell ISE app.

2. At the PowerShell prompt, download and execute the SSMS installer:

   Start-BitsTransfer `
       -Source "https://aka.ms/ssmsfullsetup" `
       -Destination "$env:Temp\ssms-setup.exe"
   & $env:Temp\ssms-setup.exe
   

3. Accept the prompt to allow changes to be made.

4. In the SSMS installer, click Install.

5. When the installation is finished, click Restart to restart the remote machine. This closes the RDP session.

6. To reconnect, in the RDP window, click Connect. If the remote machine has not finished restarting, wait a few moments and then try connecting again.

7. Enter your username and the password you saved earlier (leave the Domain field blank), and then click OK to reconnect.

Prepare the backup and restore folders

1. In the RDP session, minimize all windows, and then open Google Cloud SDK Shell (not the same as Cloud Shell) on the Windows desktop.

2. Create a backup folder:

   mkdir c:\backup
   

3. Create a restore folder:

   mkdir c:\restore
   

Prepare the database

1. In Cloud Shell, on the instance, create a test database:

   osql -E -Q "create database testdb"
   

   Note: If you see a login failed message, run the Google Cloud SDK Shell as an administrator.

2. Create a test table:

   osql -E -Q "create table testdb.dbo.testtable(status varchar(255))"
   

Configure the encryption key

1. In Cloud Shell, create a primary database key:

   osql -E -Q "USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword!';"
   

   Note: In this tutorial, you use the password MyPassword! for the encryption key, but you can choose whichever password you want.

2. Create a backup certificate:

   osql -E -Q "USE master; CREATE CERTIFICATE testdbcert WITH SUBJECT = 'testdb certificate';"
   

Performing backups

SQL Server 2019 and earlier

In this section, you create full, differential, and transaction log backups while changing the database between each one.

1. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

2. In Cloud Shell, perform a full backup:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb.bak' WITH INIT,
       COMPRESSION,
       ENCRYPTION
       (
           ALGORITHM = AES_256,
           SERVER CERTIFICATE = testdbcert
       )  "
   

   Note: You'll receive a warning that you have not backed up the certificate. Backing up the certificate is beyond the scope of this document. For more information, see BACKUP CERTIFICATE (Transact-SQL).

3. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

4. Perform a differential backup:

   osql  -E -Q "BACKUP DATABASE testdb TO DISK='c:\backup\testdb-diff.bak' WITH DIFFERENTIAL,
   COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

5. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

6. Perform a transaction log backup:

   osql  -E -Q "BACKUP LOG testdb TO DISK='c:\backup\testdb-log.bak' WITH COMPRESSION,
   ENCRYPTION
       (
       ALGORITHM = AES_256,
       SERVER CERTIFICATE = testdbcert
       ) "
   

7. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

8. Verify the rows in the table:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   The output resembles the following:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

SQL Server 2022

In this section, you create full, differential, and transaction log backups while changing the database between each one. You use the built-in backup and restore commands with Cloud Storage.

SQL Server 2022 (16.x) supports extended object storage integration by introducing a new connector that uses a REST API to connect to any provider of S3-compatible object storage. You can use the BACKUP TO URL and RESTORE FROM URL commands in SQL Server 2022 with any S3-compatible storage destination as the URL.

SQL Server uses credentials to connect to resources outside of itself. A credential is a record containing authentication information. To authenticate and authorize your access to the Cloud Storage S3 interface, you must create and use an Access Key and a Secret Key. You then store these keys in your SQL Server credential.

1. Create an Access Key and Secret Key for your user account to your Cloud Storage bucket:

   1. Go to Cloud Storage.
   2. Go to Settings.
   3. Go to INTEROPERABILITY.
   4. Go to Access keys for your user account.
   5. Scroll down and click Create a key to create a new Access key and a Secret key.

2. Create a credential in your SQL Server 2022 instance:

   Run the following code sample to create a credential. Choose a name for your credential. Replace the ACCESS_KEY and SECRET fields with the values you generated in the previous step.

   osql -E -Q "
       CREATE CREDENTIAL [CREDENTIAL_NAME]
       WITH
           IDENTITY = 'S3 Access Key',
           SECRET = 'ACCESS_KEY:SECRET'
       "
   

   Note: The IDENTITY = 'S3 Access Key' identity definition is important because it tells SQL Server to use the S3 Connector.Do not change this field.

3. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Initial')"
   

4. Perform the BACKUP operation to the Cloud Storage bucket:

   Run the backup database command having your Cloud Storage bucket URI set as the URL parameter value and the name of the credential you defined earlier as the value of the WITH CREDENTIAL option. This command will enable SQL Server to create the backup file and upload it at the same time to the Cloud Storage bucket, with no need for extra local disk space.

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           FORMAT,
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

   Note: Make sure you are using the 's3://storage.googleapis.com' prefix because it tells SQL Server to use the S3-compatible connector. This also implies using HTTPS.

The following list explains the parameters of the WITH statement:

• FORMAT: Overwrites any existing backups and creates a new media set.
• STATS: Tells SQL Server to provide information about the progress of the backup.
• COMPRESSION: Tells SQL Server to compress the backup file, making it smaller and faster to upload to Cloud Storage.
• MAXTRANSFERSIZE = 10485760, BLOCKSIZE = 65536 options help avoid I/O device errors with larger backup files.

1. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Full Backup')"
   

2. Perform a differential backup:

   osql -E -Q "
       BACKUP DATABASE testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-diff.bak'
       WITH
           DIFFERENTIAL,
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

3. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('After Diff Backup')"
   

4. Perform a transaction log backup:

   osql -E -Q "
       BACKUP LOG testdb
       TO URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb-log.bak'
       WITH
           CREDENTIAL = 'CREDENTIAL_NAME',
           STATS = 10,
           MAXTRANSFERSIZE = 10485760,
           BLOCKSIZE = 65536,
           COMPRESSION;
       "
   

5. Add a row to the test table:

   osql -E -Q "insert into testdb.dbo.testtable ([status]) VALUES ('Bad Row')"
   

6. Verify the rows in the table:

   osql -E -Q "select * from testdb.dbo.testtable"
   

   The output looks like this:

   Initial
   
   After Full Backup
   
   After Diff Backup
   
   Bad Row
   

Managing your backups

In this section, you store your backups remotely in Cloud Storage, configure your Cloud Storage bucket to prune old backups automatically, and schedule your backups. If you're running multiple database servers, consider creating multiple directories in your Cloud Storage bucket to represent different servers.

Upload your backups to Cloud Storage

SQL Server 2019 and earlier

Now that you have a few backup files, you can upload your backups to Cloud Storage.

1. In Cloud Shell, create a Cloud Storage bucket. Bucket names must be globally unique across Google Cloud. To ensure that your bucket name is unique, consider namespacing it with your project name, as shown here:

   gsutil mb "gs://${DEVSHELL_PROJECT_ID}-sql-backups"
   

2. In the shell window in the RDP session, copy your files to your Cloud Storage bucket. In the following command, replace BUCKET_NAME with the name of the bucket you just created.

   gsutil cp -n c:\backup\testdb*.bak gs://BUCKET_NAME
   

   Note: The -n flag prevents the gsutil tool from overwriting identical files.

   You can use the gsutil cp command to create entire directory structures and to upload multiple files at a time.

SQL Server 2022

The backup files are already on your bucket as SQL Server 2022 supports the backup directly to the Cloud Storage.

Set up automatic file pruning in Cloud Storage

Older backups eventually outlive their usefulness, so you need to remove them. To help automate this process, Cloud Storage has a lifecycle management mechanism that you can use to manage the lifecycle of your backup files.

Warning: The lifecycle mechanism deletes files permanently, with no chance of recovering them. When testing your lifecycle configuration, test it on noncritical data files.

To configure lifecycle management for the objects in your bucket:

1. In Cloud Shell, create a JSON lifecycle configuration file. This file instructs Cloud Storage to delete files after 30 days:

   bash -c 'cat <<EOF >  lifecycle.json
   {
       "lifecycle": {
           "rule": [{
               "action": { "type": "Delete" },
               "condition": { "age": 30 }
           }]
       }
   }
   EOF'
   

2. Set the lifecycle configuration for your Cloud Storage bucket. Replace BUCKET_NAME with the name of your bucket:

   gsutil lifecycle set lifecycle.json gs://BUCKET_NAME
   

Schedule your backups

In general, it's a good practice to take a full backup periodically and perform differential backups until the next full backup. On Windows, one way to schedule backups is by using scheduled tasks.

If you create a backup script that takes a series of backups, make sure to include some logical validation steps at each point to verify successful completion. If validation fails, make sure the script raises a Windows alert. In addition, to avoid filling up the local disk, make sure the script removes the local backup file after successfully uploading to Cloud Storage.

Restoring from backup

In this section, you restore your SQL Server database from backup files that you stored in Cloud Storage.

SQL Server 2019 and earlier

1. In Cloud Shell in your RDP session, download your backup files from Cloud Storage. Replace BUCKET_NAME with the name of your SQL Server backup storage bucket:

   gsutil cp gs://BUCKET_NAME/testdb*.bak c:\restore
   

2. Open the SQL Server Management console.

3. Click the Start button, and then click Microsoft SQL Server Tools 18 > Microsoft SQL Server Management Studio 18.

4. Leave the Connection fields as is, and then click Connect.

Note: If you see an login failed message, run the Management Studio as an administrator.

1. In the left-hand pane, expand Databases.
2. Right-click testdb, and in pop-up the menu, click Tasks > Restore > Database.
3. Import the backup files to the console:
   1. For Source, select Device.
   2. Click the button.
   3. In the dialog that opens, click Add, select all the files under C:\restore, and then click OK.
   4. Click OK.

4. To view point-in-time restore options:

   1. Click Timeline.
   2. Under Restore to, click Specific date and time.
   3. For Timeline Interval, select hour.
   4. Choose a time in the Transaction Log Backup range.

   5. Click Cancel to leave the timeline screen.

      Note: To restore your database to a specific point in time, you press OK instead of Cancel.

5. In this tutorial, you restore the database to its state before the transaction log backup. In order to do that, in the backups list, clear the transaction log row:

   

6. Start the restore process:

   1. Click OK.
   2. Wait for the database to finish the restore process, and when you see the message, Database 'testdb' restored successfully, click OK.

7. List the rows in the test table:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   The output shows two rows:

   "Initial
   "After Full Backup"
   

   You see all the lines you inserted into the table before taking the transaction log backup.

SQL Server 2022

You can perform the RESTORE operation. Set the Cloud Storage file path as the URL parameter value. For example, this T-SQL script restores the full backup directly from Cloud Storage:

```
osql -E -Q "
RESTORE DATABASE testdb
FROM
    URL = 's3://storage.googleapis.com/BUCKET_NAME/FOLDER_NAME/testdb.bak'
WITH
    CREDENTIAL = 'CREDENTIAL_NAME';
    "
```

1. Open the SQL Server Management console.
2. Click the Start button, and then click Microsoft SQL Server Tools 19 > Microsoft SQL Server Management Studio 19.

Note: You need to install a Microsoft SQL Server Management Studio version 19.x.

1. Leave the Connection fields as is, and then click Connect.

Note: If you see an login failed message, run the Management Studio as an administrator.

1. In the left-hand pane, expand Databases.
2. Right-click testdb, and in pop-up the menu, click Tasks > Restore > Database.

3. Import the backup files to the console:

   1. For Source, select Device.
   2. Click the button.
   3. In the dialog that opens, click Backup media type, and select S3 URL

   4. In the dialog that opens, click Add. Add the three locations of your backup files on the Cloud Storage bucket and the Secret Key and Access Key you provided when you created the credential.

      Note: Use the same format like the one for performing the backup for the S3 URL.

   5. Click OK.

4. To view point-in-time restore options:

   1. Click Timeline.
   2. Under Restore to, click Specific date and time.
   3. For Timeline Interval, select hour.
   4. Choose a time in the Transaction Log Backup range.

   5. Click Cancel to leave the timeline screen.

      Note: To restore your database to a specific point in time, you press OK instead of Cancel.

5. In this tutorial, you restore the database to its state before the transaction log backup. In order to do that, in the backups list, clear the transaction log row:

   

6. Start the restore process:

   1. Click OK.
   2. Wait for the database to finish the restore process, and when you see the message, Database 'testdb' restored successfully, click OK.

7. List the rows in the test table:

   osql -E -Q "select * from testdb.dbo.testtable;"
   

   The output shows two rows:

   "Initial
   "After Full Backup"
   

   You see all the lines you inserted into the table before taking the transaction log backup.

Clean up

Delete the project

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.
1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next

• Read about SQL Server best practices on Google Cloud.
• Read about configuring SQL Server availability groups.
• Read the disaster recovery planning guide for Google Cloud.
• Explore reference architectures, diagrams, and best practices about Google Cloud. Take a look at our Cloud Architecture Center.