Cloning a Microsoft SQL Server database on Compute Engine

This tutorial shows two ways to clone a Microsoft SQL Server database running on Compute Engine. One method uses persistent disk snapshots. The other method uses native SQL Server backup and restore, transferring the backup using Cloud Storage. Cloud Storage is Google Cloud's object storage service. It offers a straightforward, security-enhanced, durable, and highly available way to store files.

Cloning is the process of copying an online database onto another server. The copy is independent of the existing database and is preserved as a point-in-time snapshot. You can use a cloned database for various purposes without putting a load on the production server or risking the integrity of production data. Some of these purposes include the following:

  • Performing analytical queries
  • Load testing or integration testing of your apps
  • Data extraction for populating data warehouses
  • Running experiments on the data

Each cloning method described in this tutorial has advantages and disadvantages. The ideal method for you depends on your situation. The following table highlights some key issues.

Issue Method 1: Disk snapshots Method 2: Backup and restore using Cloud Storage
Additional disk space required on SQL Server instances No additional disk space required Additional space required for storing the backup file when creating and restoring
Additional load on source SQL Server instances during cloning No additional load Additional load on CPU and I/O when creating and uploading backup files
Duration of cloning Relatively fast for large databases Relatively slow for large databases
Can clone from SQL Server instances external to Google Cloud No Yes
Complexity A complex sequence of commands for attaching cloned disks A relatively straightforward set of commands for cloning
Can leverage existing backup systems Yes, if backup system uses Google Cloud disk snapshots Yes, if backup system writes native SQL Server backup files to Cloud Storage
Granularity of cloning Can clone only entire disks Can clone only the specified database
Data consistency Consistent at point of snapshot Consistent at point of backup

This tutorial assumes you're familiar with Microsoft Windows system administration, PowerShell, and Microsoft SQL Server administration using Microsoft SQL Server Management Studio.

Objectives

  • Learn how to run a SQL Server instance on Google Cloud.
  • Learn how to create a demo database on a secondary disk.
  • Learn how to clone a SQL Server database using Compute Engine disk snapshots.
  • Learn how to clone a SQL Server database by transferring a backup using Cloud Storage.

Costs

This tutorial uses the following billable components of Google Cloud:

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.

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

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

    Go to the project selector page

  3. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.

  4. Enable the Compute Engine API.
  5. Enable the API

Ensure that you're meeting these additional prerequisites:

  • You use the Google Chrome browser.
  • The Chrome app Chrome RDP for Google Cloud is installed. This app lets you access your Windows virtual machine (VM) instance. If you use an alternative RDP client, this prerequisite is unnecessary.

Setting up the environment

To complete this tutorial, you need to set up your computing environment with the following:

  • A SQL Server instance on Compute Engine (named sql-server-prod) to represent your production database server.
  • An additional disk (named sql-server-prod-data) that's attached to your production server for storing your production database.
  • A copy of the Wide World Importers SQL Server sample database to simulate the production database that you want to clone.
  • A SQL Server instance on Compute Engine named sql-server-test to represent your testing database server. You clone your database onto this server.

The following diagram illustrates this architecture.

Diagram that shows the setup for cloning a SQL Server database in this tutorial.

Create the production VM instance

To simulate a production environment, you set up a Compute Engine VM instance running SQL Server on Windows Server.

The VM instance for this tutorial uses two disks: a 50 GB disk for the OS and user accounts, and a 100 GB disk for database storage.

In Compute Engine, using separate disks offers no performance benefits. Disk performance is determined by the total storage capacity of all disks attached to an instance and the total number of vCPUs on your VM instance. Therefore, the database and log file can reside on the same disk.

Console

  1. In the Cloud Console, go to the VM instances page.

    Go to the VM instances page

  2. Click Create.

  3. In the Name field, type sql-server-prod.

  4. For Region, select us-east1.

  5. For Zone, select us-east1-b.

  6. Under Machine configuration, change Machine type to n1-standard-2 (2 vCPU).

  7. Next to the Boot disk description, click Change.

  8. In the Boot disk panel, click the Public images tab.

  9. In the Operating System drop-down list, select SQL Server on Windows Server.

  10. In the Version drop-down list, select SQL Server 2019 Standard on Windows Server 2019 Datacenter.

  11. Ensure that the following values are set:

    • Boot disk type is set to Standard persistent disk.
    • Size (GB) is set to 50.
  12. Click Select.

  13. Under Identity and API access, set Access scopes to Allow full access to all Cloud APIs.

  14. Expand Management, security, disks, networking, sole tenancy.

  15. Click the Disks tab.

  16. Click Add new disk .

  17. In the Name field, type sql-server-prod-data.

  18. In the Size (GB) field, type 100.

  19. Click Done.

  20. Click Create.

Cloud Shell

  1. Open Cloud Shell.

    Open Cloud Shell

  2. Set your preferred zone:

    ZONE=us-east1-b
    REGION=us-east1
    gcloud config set compute/zone "${ZONE}"
    
  3. Create a Compute Engine instance by using the app image for SQL Server 2019 Standard on Windows Server 2019 Datacenter:

    gcloud compute instances create sql-server-prod \
        --machine-type=n1-standard-2 \
        --scopes=cloud-platform \
        --image-family=sql-std-2019-win-2019 \
        --image-project=windows-sql-cloud \
        --boot-disk-size=50GB \
        --boot-disk-device-name=sql-server-prod \
        --create-disk="mode=rw,size=100,type=pd-standard,name=sql-server-prod-data,device-name=sql-server-prod-data"
    

    This command grants the instance full access to Google Cloud APIs, creates a 100 GB secondary disk, and attaches the disk to the instance. Ignore the disk performance warning because you don't need high performance for this tutorial.

Connect to the VM instance

  1. In the Cloud Console, go to the VM instances page.

    Go to the VM instances page

  2. Wait about 5 minutes for the VM instance to be ready.

    To monitor the initialization process of the VM, view its serial port output in Cloud Shell:

    gcloud compute instances tail-serial-port-output sql-server-prod
    

    When you see the following message, the initialization is complete.

    Instance setup finished. sql-server-prod is ready to use.
    

    Press Control+C to stop monitoring the serial port.

  3. Click the instance name sql-server-prod to open the VM instance details page.

  4. Under Remote access, click Set Windows password, and then click Set to create your account on the remote machine.

    This step generates a password for you. Make a note of the password or copy it to a secure, temporary file.

  5. To connect to the remote instance by using the Chrome RDP app, click the RDP button.

    If you prefer to use an alternative RDP client, click the drop-down arrow next to the RDP button, and then select Download the RDP file. You can open the RDP file by using your preferred RDP client.

  6. When you're prompted, enter the password you just generated, and then click OK.

  7. To accept the server certificate and log into your remote Windows instance, click Continue.

  8. When you're prompted whether you want your computer discoverable by other PCs and devices on the network, click No.

Set up the additional disk

The second disk attached to the production instance is for storing your production database. This disk is blank, so you need to partition, format, and mount it.

  1. In an RDP session connected to your sql-server-prod instance, click the Start button on the Windows taskbar, type diskpart, and then click diskpart to open DiskPart.
  2. When you're prompted to let the app make changes, click Yes.
  3. Display a lists of disks attached to your instance:

    list disk
    

    The output is the following:

    Disk ###  Status         Size     Free     Dyn  Gpt
    --------  -------------  -------  -------  ---  ---
    Disk 0    Online           50 GB      0 B
    Disk 1    Online          100 GB   100 GB
    

    Disk 1 (100 GB) is your data disk.

  4. Select the data disk:

    select disk 1
    
  5. Initialize the disk:

    clean
    
  6. Create a GUID partition table:

    convert gpt
    
  7. Create the data partition by using the entire disk:

    create partition primary
    
  8. List the available volumes:

    list volume
    

    The output is the following:

    Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
    ----------  ---  -----------  -----  ----------  -------  ---------  -------
    Volume 0     C                NTFS   Partition     49 GB  Healthy    System
    Volume 1                      RAW    Partition     99 GB  Healthy
    

    Volume 1 (99 GB) is your data disk.

  9. Select the volume:

    select volume 1
    
  10. Format the partition with the NTFS file system and label it data:

    format quick fs=ntfs label=data
    
  11. Mount the disk as drive D:

    assign letter=d
    
  12. Exit DiskPart:

    exit
    

Download the sample database

To set up your environment for this cloning exercise, you need to do the following:

To create the directory and download the backup file, follow these steps:

  1. In your RDP session, click the Start button on the Windows taskbar, type PowerShell, and then select the Windows PowerShell app.

  2. At the PowerShell prompt, create a directory structure for the database storage:

    mkdir D:\sql-server-data\wideworldimporters
    
  3. Download the backup file to drive D:

    bitsadmin /transfer sampledb /dynamic /download /priority FOREGROUND `
      https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak `
      D:\sql-server-data\WideWorldImporters-Full.bak
    

Install SQL Server Management Studio

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

  1. 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
    
  2. Accept the prompt to allow changes to be made.

  3. In the SSMS installer, click Install.

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

  5. 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.

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

Restore the sample database

You need to restore the sample database to drive D (data), either interactively by using the Microsoft SQL Server Management Studio (SSMS) wizards, or directly by running a Transact-SQL command.

SSMS Wizard

  1. In your RDP session, click the Start button on the Windows taskbar, type ssms, and then select Microsoft SQL Server Management Studio.
  2. After the app starts, click Connect to connect to the sql-server-prod database engine using Windows Authentication.
  3. In Object Explorer, right-click Databases, and then select Restore Database.
  4. Under Source, select Device, and then click the [...] button next to the device name.
  5. In the Select backup devices dialog, ensure that File is selected for Backup media type, and then click Add.
  6. In the file selector, browse to D:\sql-server-data, click the WideWorldImporters-Full.bak file, and then click OK.
  7. Click OK to close the Select backup devices dialog.

    The Restore Database dialog is now populated with data about the Wide World Importers database backup.

  8. Under Select a page, click Files.

  9. Select the Relocate all files to folder checkbox.

  10. In both the Data file folder and Log file folder fields, enter D:\sql-server-data\wideworldimporters.

    Screenshot that shows the Restore Database dialog.

  11. Click OK to start the restore operation.

After a couple of minutes you're notified that the database is restored.

Transact-SQL

  1. In your RDP session, click the Start button on the Windows taskbar, type ssms, and then select Microsoft SQL Server Management Studio.
  2. After the app starts, click Connect to connect to the sql-server-prod database engine using Windows Authentication.
  3. Select File > New > Query with Current Connection to open a new query window.
  4. Initiate a restore from the backup file you downloaded:

    USE [master]
    GO
    
    RESTORE DATABASE [WideWorldImporters]
      FROM  DISK = N'D:\SQL-SERVER-DATA\WideWorldImporters-Full.bak'
      WITH  FILE = 1,
      MOVE N'WWI_Primary' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters.mdf',
      MOVE N'WWI_UserData' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters_UserData.ndf',
      MOVE N'WWI_Log' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters.ldf',
      MOVE N'WWI_InMemory_Data_1' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
      NOUNLOAD,
      STATS = 5
    GO
    

    This command restores the database and log file into the D:\sql-server-data\wideworldimporters directory.

  5. Right-click the query code and click Execute.

    Allow a couple of minutes for the database restore to complete. You can click Refresh in Object Explorer to see whether the database is listed in the Databases tree. After the database restore is finished, you can close the query window without saving.

To verify the sample database is functional, you can run a query.

  1. In Microsoft SQL Server Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

    SELECT top(100)
      i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
      i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
    FROM
      WideWorldImporters.Sales.Invoices i
      JOIN WideWorldImporters.Sales.Customers c
      ON i.CustomerID=c.CustomerID
    WHERE i.ConfirmedDeliveryTime IS NOT NULL
    ORDER BY i.InvoiceDate desc;
    

    This query retrieves summary information from the 100 most recently delivered invoices.

  2. Right-click the query window and click Execute.

    The Results pane displays the summary information.

    Screenshot that shows the Results pane from a query to the sample database.

Create the test VM instance

In this section you create a SQL Server instance named sql-server-test as the destination for the cloned database. The configuration of this instance is identical to the production instance. However, you don't create a second data disk; instead, you attach the data disk later in this tutorial.

Console

  1. Go to the VM instances page.

    Go to the VM instances page

  2. Click Create.

  3. In the Name field, type sql-server-test.

  4. For Region, select us-east1.

  5. For Zone, select us-east1-b.

  6. Under Machine configuration, change Machine Type to n1-standard-2 (2 vCPU).

  7. Next to the Book disk image, click Change.

  8. In the Boot disk panel, click the Public images tab.

  9. In the Operating System drop-down list, select SQL Server on Windows Server.

  10. In the Version drop-down list, select SQL Server 2019 Standard on Windows Server 2019 Datacenter.

  11. Ensure that the following values are set:

    • Boot disk type is set to Standard persistent disk.
    • Size (GB) is set to 50.
  12. Click Select.

  13. Under Identity and API access, set Access scopes to Allow full access to all Cloud APIs.

  14. Click Create.

Cloud Shell

  1. Open Cloud Shell.

    Open Cloud Shell

  2. Create the test SQL Server instance:

    gcloud compute instances create sql-server-test \
        --machine-type=n1-standard-2 \
        --scopes=cloud-platform \
        --image-family=sql-std-2019-win-2019 \
        --image-project=windows-sql-cloud \
        --boot-disk-size=50GB \
        --boot-disk-device-name=sql-server-test
    

    You can ignore the disk performance warning because you don't need high performance for this tutorial.

Connect to the VM instance

  1. In the Cloud Console, go to the VM instances page.

    Go to the VM instances page

  2. Wait about 5 minutes for the VM instance to be ready.

    To monitor the initialization process of the VM, view its serial port output in Cloud Shell:

    gcloud compute instances tail-serial-port-output sql-server-prod
    

    When you see the following message, the initialization is complete.

    Instance setup finished. sql-server-test is ready to use.
    

    Press Control+C to stop monitoring the serial port.

  3. Click the instance name sql-server-test to display the VM instance details page.

  4. Under Remote access, click Set Windows password, and then click Set to create your account on the remote machine.

    This step generates a password for you. Make a note of the password or copy it to a secure, temporary file.

  5. To connect to the remote instance by using the Chrome RDP app, click the RDP button.

    If you prefer to use an alternative RDP client, click the drop-down arrow next to the RDP button, and then select Download the RDP file. You can open the RDP file by using your preferred RDP client.

  6. When you're prompted, enter the password you just generated, and then click OK.

  7. To accept the server certificate and log into your remote Windows instance, click Continue.

  8. When you're prompted whether you want your PC discoverable, click No.

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.

Cloning the database using Compute Engine disk snapshots

One way to clone a SQL Server database running on Compute Engine is to store the database on a separate data disk and use persistent disk snapshots to create a clone of that disk.

Persistent disk snapshots let you get a point-in-time copy of on-disk data. Scheduling disk snapshots is one way to automatically back up your data.

In this section of the tutorial, you do the following:

  • Take a snapshot of the production server's data disk.
  • Create a new disk from the snapshot.
  • Mount the new disk onto the test server.
  • Attach the database on this disk to SQL Server on the test instance.

The following diagram shows how a database is cloned by using disk snapshots.

Diagram that shows the setup for cloning a SQL Server database using disk snapshots.

Create the disk snapshot

Console

  1. In the Cloud Console, go to the VM instances page.

    Go to the VM instances page

  2. Click the name of the sql-server-prod instance.

  3. On the VM instance details page, click the disk sql-server-prod-data.

  4. Click Create Snapshot.

  5. Name the snapshot sql-server-prod-data-snapshot.

  6. For Location, select Regional.

  7. Verify that the region is set to us-east1 (the same as your VM instances).

  8. Select the Enable VSS option.

    This option uses the Volume Shadow Copy Service in Microsoft Windows to make a consistent snapshot.

  9. Click Create.

    After a few minutes, your snapshot is created.

Cloud Shell

  1. Open Cloud Shell.

    Open Cloud Shell

  2. Create a snapshot of your data disk in the same zone as the VM instance:

    gcloud compute disks snapshot sql-server-prod-data \
         --snapshot-names=sql-server-prod-data-snapshot \
         --guest-flush \
         --zone="${ZONE}"
    

    The --guest-flush option uses the Volume Shadow Copy Service in Microsoft Windows to create a consistent snapshot. After a few minutes, your snapshot is created.

Attach the disk snapshot to the test instance

You need to create a new data disk from the snapshot you created and then attach it to the sql-server-test instance.

Console

In the following steps, you create a new persistent disk, use the snapshot of the production disk for its contents, and then attach the disk to the test instance.

  1. In the Cloud Console, go to the VM instances page.

    Go to the VM instances page

  2. Click the instance name sql-server-test.

  3. On the VM instance details page, click Edit.

  4. Click Add new disk .

  5. Name the new disk sql-server-test-data.

  6. For Source Type, select Snapshot.

  7. For the sql-server-prod-data-snapshot instance you created, select the Source snapshot.

  8. Ensure that Mode is set to Read/Write.

  9. Click Done.

Cloud Shell

  1. Open Cloud Shell.

    Open Cloud Shell

  2. Create a new persistent disk by using the snapshot of the production disk for its contents:

    gcloud beta compute disks create sql-server-test-data \
         --size=100GB \
         --source-snapshot=sql-server-prod-data-snapshot \
         --zone="${ZONE}"
    
  3. Attach the new disk to your sql-server-test instance with read-write permissions:

    gcloud compute instances attach-disk sql-server-test \
        --disk=sql-server-test-data --mode=rw
    

Mount the new data disk in Windows

The disk you created is attached to the VM instance but is offline, and the volume is set to read-only. To configure the volume as read-write and mountable, perform the following steps:

  1. In the RDP client window that's connected to your sql-server-test instance, click the Start button on the Windows taskbar, type diskpart, and then click diskpart to open DiskPart.
  2. When you're prompted to let the app make changes, click Yes.
  3. Display a list of the disks attached to your instance:

    list disk
    

    The output is the following:

    
    Disk ###  Status         Size     Free     Dyn  Gpt
    --------  -------------  -------  -------  ---  ---
    Disk 0    Online           50 GB      0 B
    Disk 1    Offline         100 GB      0 B       *
     

    Your data disk (Disk 1, 100 GB) is offline.

  4. Select the data disk:

    select disk 1
    
  5. Bring the disk online:

    online disk
    
  6. List the available volumes:

    list volume
    

    The output is the following:

    
    Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
    ----------  ---  -----------  -----  ----------  -------  ---------  -------
    Volume 0     C                NTFS   Partition     49 GB  Healthy    System
    Volume 1         data         NTFS   Partition     99 GB  Healthy    Hidden
    

    Volume 1 (99 GB) is your data volume. It's listed as Hidden with no drive letter assigned.

  7. Select the volume:

    select volume 1
    
  8. Clear the attributes that were set when you created the volume from the snapshot:

    attr volume clear readonly hidden nodefaultdriveletter shadowcopy
    

    This command makes the volume mountable.

  9. Mount the volume as drive D:

    assign letter=d
    
  10. Exit DiskPart:

    exit
    

Reset file ownership and permissions

Because you created the data disk by taking a snapshot on the sql-server-prod instance, the user IDs for file ownership and permissions are different than those on the sql-server-test instance. You need to change the ownership of the files to a user on the sql-server-test instance and update the permissions to make the files readable for your local user and the MSSQLSERVER user.

  1. In the RDP client window connected to your sql-server-test instance, click the Start button on the Windows taskbar, and then type cmd.
  2. Open the Command Prompt app as an administrator.
  3. Set the owner of the files in the sql-server-data folder to the MSSQLSERVER service user:

    icacls d:\sql-server-data /setowner "nt service\mssqlserver" /t
    
  4. Reset all permissions on all files in the top-level sql-server-data folder:

    icacls d:\sql-server-data /reset /t
    
  5. Update the access control lists (ACLs) for the sql-server-data folder:

    icacls d:\sql-server-data /grant Administrators:(oi)(ci)f "nt service\mssqlserver":(oi)(ci)f "owner rights":(oi)(ci)f  %USERNAME%:(oi)(ci)f
    
  6. Disable the inheritance from the root folder of the drive, so that only the preceding permissions are applied:

    icacls d:\sql-server-data /inheritancelevel:r
    
  7. Exit the Command Prompt app:

    exit
    

The SQL Server instance and the local user on the sql-server-test instance can now access the database files.

Attach the cloned database

You now attach the cloned database on drive D (data) to the test SQL Server instance. You can attach the database either interactively by using the Microsoft SQL Server Management Studio wizards, or directly by running a Transact-SQL command.

SSMS Wizard

  1. In the RDP session connected to your sql-server-test instance, click the Start button on the Windows taskbar, type ssms, and then select Microsoft SQL Server Management Studio.
  2. Click Connect to connect to the sql-server-test database engine using Windows Authentication.
  3. In Object Explorer, right-click Databases, and then select Attach.
  4. In the Attach Databases wizard, click Add.
  5. Browse to the directory D:\sql-server-data\wideworldimporters, click the WideWorldImporters.mdf file, and then click OK.
  6. Click OK to attach the database.

    After a few moments, your cloned database is attached. You can click Refresh in Object Explorer to see whether the database is listed in the Databases tree.

Transact-SQL

  1. In an RDP session connected to your sql-server-test instance, click the Start button on the Windows taskbar, type ssms, and then select Microsoft SQL Server Management Studio.
  2. Click Connect to connect to the sql-server-prod database engine using Windows Authentication.
  3. Select File > New > Query with Current Connection to open a new query window.
  4. Attach the data and log files in the D:\sql-server-data\wideworldimporters directory:

    USE [master]
    GO
    
    CREATE DATABASE [WideWorldImporters] ON
    ( FILENAME = N'D:\sql-server-data\wideworldimporters\WideWorldImporters.mdf' ),
    ( FILENAME = N'D:\sql-server-data\wideworldimporters\WideWorldImporters.ldf' ),
    ( FILENAME = N'D:\sql-server-data\wideworldimporters\WideWorldImporters_UserData.ndf' )
     FOR ATTACH
    GO
    
  5. Right-click the query code and click Execute.

    After a few moments, your cloned database is attached. You can click Refresh in Object Explorer to see whether your database is listed in the Databases tree. After the database is attached, you can close the query window without saving.

To verify that the sample database is functional, you can run a query.

  1. In SQL Server Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

    SELECT top(100)
      i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
      i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
    FROM
      WideWorldImporters.Sales.Invoices i
      JOIN WideWorldImporters.Sales.Customers c
      ON i.CustomerID=c.CustomerID
    WHERE i.ConfirmedDeliveryTime IS NOT NULL
    ORDER BY i.InvoiceDate desc;
    

    This query retrieves summary information from the 100 most recently delivered invoices.

  2. To run the query, right-click the query window, and then select Execute.

    The Results pane displays the summary information.

    Screenshot that shows the Results pane from a query to the sample database.

Now that you have seen how to clone a database using persistent disk snapshots, you might want to try cloning a database by using backup and restore. To complete the tutorial for this second approach, you must delete the database that you cloned from the sql-server-test instance.

Delete the cloned database

To delete the cloned database you created by using disk snapshots, perform the following steps.

SSMS Wizard

  1. In the RDP session connected to your sql-server-test instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-test database engine.
  2. In Object Explorer, expand Databases, right-click the WorldWideImporters database, and then select Delete.
  3. In the Delete Object wizard, ensure that the Close existing connections checkbox is selected.
  4. Click OK.

Transact-SQL

  1. In the RDP session connected to your sql-server-test instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-test database engine.
  2. To close all connections to the WideWorldImporters database and delete it, copy the following script into a new query window, right-click the code, and then click Execute:

    USE [master]
    GO
    ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE [WideWorldImporters]
    GO
    

    After the database is deleted, you can close the query window without saving. You can click Refresh in Object Explorer to confirm the database is deleted.

Cloning using backup and restore

A second method of cloning a SQL Server database running on Compute Engine is to use native SQL Server backup and restore. With this approach, you transfer the backup by using Cloud Storage.

This section of the tutorial uses resources that you created in the Cloning the database using Compute Engine disk snapshots section of this tutorial. If you didn't complete that section, you must do so before continuing.

In this section of the tutorial, you do the following:

  • Create a Cloud Storage bucket.
  • Back up the database on the production server.
  • Copy the backup file from the production server to Cloud Storage.
  • Copy the backup file from Cloud Storage to the test server.
  • Restore the backup on the test instance.

The following diagram shows how a database is cloned by transferring a backup using Cloud Storage.

Diagram that shows the setup for cloning a SQL Server database using Cloud Storage.

Because systems outside of Google Cloud can be given access to Cloud Storage, you can use this approach to clone databases from external SQL Server instances.

Create a Cloud Storage bucket

You need to create a Cloud Storage bucket that stores the backup files while you transfer them from the sql-server-prod instance to the sql-server-test instance.

Console

  1. In the Cloud Console, go to the Cloud Storage Browser page.

    Go to the Cloud Storage Browser page

  2. Click Create bucket.

  3. Name the bucket project-name-bucket.

    Replace the following:

    • project-name: The ID of your Google Cloud project.
  4. Expand Choose a default storage class, and then select Regional.

  5. For Location, select us-east1.

  6. Click Create.

Cloud Shell

  1. Open Cloud Shell.

    Open Cloud Shell

  2. Create a Cloud Storage bucket in the same region as your VM instances:

    gsutil mb -l "${REGION}" "gs://$(gcloud config get-value project)-bucket"
    

Make a full point-in-time backup of the database

In your production environment, you might already make backups. You can use these backups as a base for cloning your database. In this tutorial, you make a copy-only backup so that it doesn't impact any existing full or incremental backup schedules.

SSMS Wizard

  1. In the RDP session connected to your sql-server-prod instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-prod database engine.
  2. In Object Explorer, expand Databases, right-click the WorldWideImporters database, and then select Tasks > Back Up.
  3. In the Back Up Database wizard, ensure that the following values are set:
    • Backup type is set to Full.
    • Copy-only backup is selected.
    • Back up to is set to Disk.
  4. To add a backup file, click Add.
  5. In the Destination field, enter D:\sql-server-data\WideWorldImporters-copy.bak.

    Screenshot that shows the Back Up Database dialog.

  6. Select the Media Options page, and then select Overwrite all existing backup sets.

  7. Select the Backup Options page, and then change Set backup compression to Compress backup.

  8. To create the backup, click OK.

    The backup takes a few minutes to create.

Transact-SQL

  1. In the RDP session connected to your sql-server-prod instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-prod database engine.
  2. To perform a copy-only compressed backup of the WideWorldImportersdatabaseto the file d:\sql-server-data\WideWorldImporters-copy.bak, copy the following script into a new query window, right-click the code, and then click Execute.

    BACKUP DATABASE [WideWorldImporters]
       TO  DISK = N'd:\sql-server-data\WideWorldImporters-copy.bak'
       WITH  COPY_ONLY, NOFORMAT, INIT,
       NAME = N'WideWorldImporters-Full Database Backup',
       SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
    GO
    

    Allow a few minutes for the server to create the backup. After the database backup is complete, you can close the query window without saving.

Copy the backup file to Cloud Storage

  1. In the RDP session connected to your sql-server-prod instance, open a Windows PowerShell window.
  2. Copy the backup file into the Cloud Storage bucket you created earlier:

    gsutil cp -n d:\sql-server-data\WideWorldImporters-copy.bak "gs://$(gcloud config get-value project)-bucket/"
    
  3. Exit PowerShell.

    exit
    

Copy the backup file from Cloud Storage to sql-server-test

  1. In the RDP session connected to your sql-server-test instance, open a Windows PowerShell window.
  2. Copy the backup file into the Cloud Storage bucket you created earlier:

    gsutil cp "gs://$(gcloud config get-value project)-bucket/WideWorldImporters-copy.bak" d:\sql-server-data\
    
  3. Exit PowerShell.

    exit
    

Your test instance now has the full-copy backup of your database on its local disk.

Restore the backup

You can now restore the full-copy backup onto drive D (data) of the sql-server-test instance.

SSMS Wizard

  1. In the RDP session connected to your sql-server-test instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-test database engine.
  2. In Object Explorer, right-click Databases, and then select Restore Database.
  3. For Source, select the Device, and then click the [...] button next to the device name.
  4. In the Select backup devices dialog, select File in the Backup media type list, and then click Add.
  5. In the file selector, browse to D:\sql-server-data, click the WideWorldImporters-copy.bak file, and then click OK.
  6. Click OK to close the Select backup devices dialog.

    The Restore Database dialog is now populated with data about the WideWorldImporters database backup.

  7. Under Select a page, click Files.

  8. Select Relocate all files to folder.

  9. In the Data file folder and Log file folder fields, enter D:\sql-server-data\wideworldimporters.

    Screenshot that shows the Restore Database dialog.

  10. To start the restore operation, click OK.

    When the process is complete, you see the message Database 'WideWorldImporters' restored successfully.

Transact-SQL

  1. In the RDP session connected to your sql-server-test instance, open Microsoft SQL Server Management Studio, and then connect to the sql-server-test database engine.
  2. Select File > New > Query with Current Connection to open a new query window.
  3. Copy the following T-SQL command to initiate a restore from the backup file you copied from Cloud Storage, restoring the database and log file into the D:\sql-server-data\wideworldimportersdirectory:

    USE [master]
    GO
    
    RESTORE DATABASE [WideWorldImporters]
      FROM  DISK = N'D:\SQL-SERVER-DATA\WideWorldImporters-copy.bak'
      WITH  FILE = 1,
      MOVE N'WWI_Primary' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters.mdf',
      MOVE N'WWI_UserData' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters_UserData.ndf',
      MOVE N'WWI_Log' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters.ldf',
      MOVE N'WWI_InMemory_Data_1' TO
        N'D:\SQL-SERVER-DATA\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
      NOUNLOAD,
      STATS = 5
    GO
    
  4. Right-click the code and click Execute.

    After a couple of minutes the database restore completes. You can click Refresh in Object Explorer to see whether the database is listed in the Databases tree. After the database restore is complete, you can close the query window without saving.

To demonstrate that the database is functional, you can run a query.

  1. In Microsoft SQL Management Studio, select File > New > Query with Current Connection to open a new query window, and then copy the following code:

    SELECT top(100)
      i.InvoiceDate, i.InvoiceID, i.CustomerID, c.CustomerName,
      i.ConfirmedDeliveryTime, i.ConfirmedReceivedBy
    FROM
      WideWorldImporters.Sales.Invoices i
      JOIN WideWorldImporters.Sales.Customers c
      ON i.CustomerID=c.CustomerID
    WHERE i.ConfirmedDeliveryTime IS NOT NULL
    ORDER BY i.InvoiceDate desc;
    

    This query retrieves summary information from the 100 most recently delivered invoices.

  2. Right-click on the query window and click Execute.

    The Results pane displays the summary information.

    Screenshot that shows the Results pane from a query to the sample database.

Cleaning up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, you can delete the Google Cloud project that you created for this tutorial.

  1. In the Cloud Console, go to the Manage resources page.

    Go to the Manage resources page

  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