Backup SQL Server using instant snapshots

If your SQL Server database files are spread across multiple disks, you can back up all the disks simultaneously, maintaining application and data consistency. You achieve this exact point-in-time backup with Compute Engine consistency groups of instant snapshots, which let you back up data across a group of disks.

This tutorial describes how to back up SQL Server databases with Compute Engine snapshots and the Transact-SQL (T-SQL) snapshot feature that's available in SQL Server 2022 and later. This solution supports both Windows and Linux deployments and minimizes the performance impact on your live workloads.

How it works

The workflow consists of these main steps, which are managed by a script running on a compute instance:

Freeze the database: the script sends a T-SQL command to SQL Server to suspend all write operations for the target database(s). This ensures the database files are in a consistent state for the backup.
Take instant snapshots: while the database is frozen, create a group of insant snapshots of the disks where the database's data and log files reside. This is the Google Cloud equivalent of using a hardware or service-level snapshot mechanism.
Record and thaw: the script sends another T-SQL command to SQL Server to record the backup metadata. This command creates a small backup file that points to the consistency group of instant snapshots and is recorded in the msdb backup history. Upon completion, SQL Server automatically thaws the database and resumes normal operations.

This entire process is typically completed in under a second, minimizing the duration of the write freeze on your database. While the freeze is in place, data can be read but not written. You can manually cancel the freeze state by setting SUSPEND_FOR_SNAPSHOT_BACKUP=OFF for the database.

Objectives

In this tutorial, you'll learn how to complete the following tasks:

Create a SQL Server instance with two data disks.
Create new database with the data and log files on separate disks.
Create a consistency group of all the disks of the VM running SQL Server.
Create instant snapshots of the group of disks.
Create new disks from the instant snapshots.

Costs

In this document, you use 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 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.

Before you begin

For this tutorial, you need a Google Cloud project. You can create a new one, or select or select an existing project:
1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
  Roles required to select or create a project
  - Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
  - Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.
  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
2. Verify that billing is enabled for your Google Cloud project.
3. In the Google Cloud console, activate Cloud Shell.
  
  Activate Cloud Shell
Ensure that Microsoft SQL Server 2022 or later is installed and running.

Required permissions

In addition to standard read access, ensure your SQL Server administrator grants you the ALTER DATABASE permission for the target database.

To get the permissions that you need to create instance and create snapshots, ask your administrator to grant you the following IAM roles on the project:

Manage instance: compute.instanceAdmin.v1
Create snapshots: compute.storageAdmin

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a Compute Engine SQL instance

Create a SQL Server instance.

In the Google Cloud console, open Cloud Shell by clicking the Activate Cloud Shell button.

Go to the Google Cloud console
Create a SQL Server instance. Paste the following command:

  REGION=REGION
  ZONE=$REGION-a
  VM_NAME=VM_NAME
  gcloud compute instances create $VM_NAME \
    --boot-disk-auto-delete \
    --boot-disk-size 100 \
    --boot-disk-type hyperdisk-balanced \
    --image-family sql-std-2022-win-2025 \
    --image-project windows-sql-cloud \
    --machine-type c4-highmem-4 \
    --zone $ZONE \
    --network-interface subnet=default \
    --tags sql-server-instant-snapshot \
    --scopes=cloud-platform,service-control,service-management,monitoring-write,logging-write,storage-rw \
    --create-disk=device-name=$VM_NAME-data-disk1,mode=rw,name=$VM_NAME-data-disk1,size=100,type=hyperdisk-balanced \
    --create-disk=device-name=$VM_NAME-data-disk2,mode=rw,name=$VM_NAME-data-disk2,size=100,type=hyperdisk-balanced

Replace the following:

Region: The region where your new instance will be deployed.
VM_NAME: The name of your new SQL Server instance.

Create a consistency group of disks

Create a consistency group.

gcloud compute resource-policies create disk-consistency-group $VM_NAME-snap-grp \
    --region=$REGION

Add the VM's disks to the consistency group.

gcloud compute disks add-resource-policies $VM_NAME \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

gcloud compute disks add-resource-policies $VM_NAME-data-disk1 \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

gcloud compute disks add-resource-policies $VM_NAME-data-disk2 \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

Create a new database

Create a username and password for the VM instance.
Connect to the VM by using Remote Desktop and sign in using the username and password created in the previous step.
Right-click the Start button (or press Win+X) and click Terminal (Admin).
Confirm the elevation prompt by clicking Yes.

Run the following PowerShell script in opened Terminal window. This script initializes the data disk, formats the disk with a 64 KB block size, and assigns a drive letter.

$availableDisks = Get-PhysicalDisk -CanPool $True
$diskLetters = [char[]] (68..72) | Where-Object { !(Get-PSDrive $_ -ErrorAction SilentlyContinue) }
$diskCount = 0
foreach ($disk in $availableDisks) {
    $diskLetter = $diskLetters[$diskCount]
    $diskLabel = "$diskLetter-DataDisk"
    Initialize-Disk -Number $disk.DeviceId -PartitionStyle MBR -PassThru

    New-Partition -DiskNumber $disk.DeviceId -UseMaximumSize `
    -DriveLetter $diskLetter | Format-Volume -FileSystem NTFS `
    -NewFileSystemLabel $diskLabel -AllocationUnitSize 65536 -Confirm:$false

    New-Item -ItemType Directory -Path "$($diskLetter):\MSSQL"
    $diskCount = $diskCount +1
}

Open the SQL Server Management Studio (SSMS). Run it as Administrator.
In the Connect to server dialog, verify the server name is set to localhost, and select Connect.
In the file menu, select File > New > Query with the current connection.

Copy following code to the newly opened query window.

USE Master;
GO
CREATE DATABASE SnapBackupDB
ON PRIMARY
(
    NAME = 'SnapBackupDB_Data',
    FILENAME = 'D:\MSSQL\SnapBackupDB.mdf',
    SIZE = 500MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON
(
    NAME = 'SnapBackupDB_Log',
    FILENAME = 'E:\MSSQL\SnapBackupDB_log.ldf',
    SIZE = 250MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 64MB
);

Deploy snapshot script

This script automates the complete process required to take application-consistent snapshots of the disks attached to the VM running SQL Server.

Using the same Remote Desktop session, open Notepad.
Copy the contents of the following script and paste into the opened Notepad window.

Save the file as take-snapshot.ps1 in c:\scripts

# Import Modules
Import-Module -Name SQLPS

# Set variables
$formattedTimestamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$backupLocation = 'D:\MSSQL\Backup'
$dbName = 'SnapBackupDB'
$bkmFile = Join-Path -Path $backupLocation  -ChildPath ("${dbName}_${formattedTimestamp}.bkm")
$sqlServer = 'localhost'

# SQL Commands
$suspendDbCmd = "ALTER DATABASE [$dbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP=ON WITH NO_WAIT;"
$backupMetadataCmd = "BACKUP DATABASE [$dbName] TO DISK='$bkmFile' WITH METADATA_ONLY, FORMAT;"
$unsuspendDbCmd = "ALTER DATABASE [$dbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP=OFF;"

# --- Helper Function for SQL Execution ---
function Invoke-MySqlCommand {
    param(
        [Parameter(Mandatory=$true)]
        [string]$CommandText,
        [Parameter(Mandatory=$true)]
        [System.Data.SqlClient.SqlConnection]$SqlConnection
    )
    Write-Host "Executing SQL Command: $($CommandText)"
    try {
        $Command = New-Object System.Data.SqlClient.SqlCommand($CommandText, $SqlConnection)
        $Result = $Command.ExecuteNonQuery()
        return $true
    }
    catch {
        Write-Host "Error executing SQL Command: $($CommandText)`n$($_.Exception.Message)" -ForegroundColor Red
        return $false
    }
}

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "server='$sqlServer';database='$dbName';Integrated Security=True;"
$databaseSuspended = $false

if (-not(Test-Path $backupLocation)) {
    New-Item -Path $backupLocation -ItemType directory -Force
}

try {
    $instanceName = (Invoke-RestMethod -Headers @{"Metadata-Flavor"="Google"} -Uri "http://metadata.google.internal/computeMetadata/v1/instance/name")
    $vmConfigString = (gcloud compute instances list --filter="name=('$instanceName')" --format=json --quiet)
    if ($LASTEXITCODE -ne 0) {
        Write-Host "Error querying gcloud for VM instances (exit code: $LASTEXITCODE). Exiting." -ForegroundColor Red
        exit 1
    }
    $vmConfig = $vmConfigString | ConvertFrom-Json

    # Open SQL Server connection
    $sqlConn.Open()

    # Suspend Database
    Write-Host "Suspending database '$dbName' for snapshot..."
    if (-not (Invoke-MySqlCommand -CommandText $suspendDbCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to suspend database. Exiting." -ForegroundColor Red
        exit 1
    }
    $databaseSuspended = $true

    # Take a disk snapshot
    try {
        $consistencyGroupListStr = (gcloud compute resource-policies list --filter='name:*-snap-grp' --format=json --quiet)
        if ($LASTEXITCODE -ne 0) { throw "gcloud resource-policies list failed (exit code: $LASTEXITCODE)." }
        $consistencyGroupList = $consistencyGroupListStr | ConvertFrom-Json

        if (@($consistencyGroupList).Count -eq 0) { throw "No consistency group found matching '*-snap-grp'." }
        if (@($consistencyGroupList).Count -gt 1) { Write-Warning "More than one consistency group found, using the first one." }

        $consistencyGroupSelfLink = $consistencyGroupList[0].selfLink
        $zoneName = Split-Path $vmConfig[0].zone -Leaf
        $snapshotName = "$($vmConfig.Name)-${formattedTimestamp}"

        $snapshotCmd = "gcloud compute instant-snapshot-groups create $snapshotName --source-consistency-group=$consistencyGroupSelfLink --zone=$zoneName --quiet"
        Invoke-Expression $snapshotCmd
        if ($LASTEXITCODE -ne 0) { throw "gcloud compute instant-snapshot-groups create failed (exit code: $LASTEXITCODE)." }
        Write-Host "Instant snapshot group created successfully."
    }
    catch {
        Write-Host "Error during snapshot creation: $($_.Exception.Message)" -ForegroundColor Red
        exit 1 # Exit after handling in finally
    }

    # Backup database metadata
    if (-not (Invoke-MySqlCommand -CommandText $backupMetadataCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to backup database metadata." -ForegroundColor Red
        exit 1
    }

    # Unsuspend Database
    Write-Host "Unsuspending database '$dbName'..."
    if (-not (Invoke-MySqlCommand -CommandText $unsuspendDbCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to unsuspend database after successful snapshot and metadata backup. Manual intervention may be required." -ForegroundColor Red
        exit 1
    }
    $databaseSuspended = $false # Successfully unsuspended

}
catch {
    Write-Host "An unhandled error occurred: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}
finally {
    if ($databaseSuspended -and ($sqlConn.State -eq [System.Data.ConnectionState]::Open)) {
        if (-not (Invoke-MySqlCommand -CommandText $unsuspendDbCmd -SqlConnection $sqlConn)) {
            Write-Host "Failed to unsuspend database in finally block. Manual intervention may be required." -ForegroundColor Red
        }
    }

    # Ensure SQL connection is closed
    if ($sqlConn.State -eq [System.Data.ConnectionState]::Open) {
        $sqlConn.Close()
    }
}

Right-click the Start button (or press Win+X) and click Terminal (Admin).
Confirm the elevation prompt by clicking Yes.
Execute the script by running following command.
```
    C:\scripts\take-snapshot.ps1
```

Verify the instant snapshots were created

Run the following commands in the Cloud Shell

Verify the script created the snapshots.

    gcloud compute instant-snapshots list --filter="name:$VM_NAME*"

List the instant snapshot groups.

    gcloud compute instant-snapshot-groups list --zones $ZONE

View details of the instant snapshot group.

    gcloud compute instant-snapshot-groups describe  \
    INSTANT_SNAPSHOT_NAME --zone=$ZONE

Copy the selfLink value to use in the next section.

Restore the instant snapshots to new disks

Run the following commands in the Google Cloud console window.

Create disks from the consistency group of instant snapshots.

    gcloud compute disks bulk create --source-instant-snapshot-group \
    INSTANT_SNAPSHOT_NAME_URL \
    --source-instant-snapshot-group-region $REGION --zone=$ZONE

Confirm your snapshots were restored by listing the disks in the zone.

    gcloud compute disks list --zones=$ZONE --filter="name:$VM_NAME-*"

Create a new VM from the newly created disks.

REGION=REGION
ZONE=$REGION-a
VM_NAME=VM_NAME
gcloud compute instances create $VM_NAME \
    --machine-type c4-highmem-4 \
    --zone $ZONE \
    --network-interface subnet=default \
    --tags sql-server-instant-snapshot \
    --disk=name=BOOT_DISK_NAME,boot=yes,auto-delete=no \
    --disk=name=DATA_DISK_1_NAME,mode=rw,auto-delete=no \
    --disk=name=DATA_DISK_2_NAME,mode=rw,auto-delete=no