DocsICP FilingConsole
官方文档
首页

Create application-consistent snapshots for SQL Server on Windows

更新时间：
复制 MD 格式
产品详情
我的收藏

Verify application-consistent snapshot backup for SQL Server 2019 (64-bit) on a Windows Server 2019 ECS instance.

Prerequisites

  • The ECS instance uses ESSDs, and the multi-attach feature is disabled for the disks.

  • The ECS instance is in the Running state. Cloud Assistant is in Normal state on the instance. To view Cloud Assistant status, see Check Cloud Assistant status and troubleshoot exceptions.

  • A Windows SQL Server database is installed. See Deploy an SQL Server database (Windows).

  • A RAM role with a custom policy for application-consistent snapshots is attached to the ECS instance. See Create a RAM role and attach it to an ECS instance.

    Note

    Cloud Assistant requires specific permissions to access the ECS instance and run commands. Grant these permissions through the RAM role.

    • Configure a custom RAM role, such as AppSnapshotRoleName.

    • Attach a custom policy to the RAM role. The following sample policy grants permissions to query snapshots, create snapshots, add tags, and query disk information. 

      {
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ecs:DescribeSnapshot*",
                "ecs:CreateSnapshot*",
                "ecs:TagResources",
                "ecs:DescribeDisks"
            ],
            "Resource": [
                "*"
            ],
            "Condition": {}
        }
    ]
}

Overview

An application-consistent snapshot pauses write operations to capture a consistent database state, reducing the risk of data corruption or loss upon restoration.

image

  1. Step 1: Prepare the database environment for validation

    Create a database table and a stored procedure to simulate continuous data writes for later verification.

  2. Step 2: Create an application-consistent snapshot in the console

    Create an application-consistent snapshot for the Windows instance hosting SQL Server. Write operations are temporarily paused during snapshot creation.

  3. Step 3: Verify that the application-consistent snapshot is created

    Check the Cloud Assistant command result and verify the timestamps when write operations were paused and resumed.

  4. Step 4: Verify the data restoration from the application-consistent snapshot

    Roll back the disk with the application-consistent snapshot, then compare the last write time against the pause time to verify correct restoration.

    • If the last write time is earlier than the pause time: No writes occurred during the pause. The restored database state is consistent with the snapshot.

    • If the last write time is the same as or later than the pause time: Writes occurred during the pause. The application-consistent snapshot did not work as expected.

Procedure

Step 1: Prepare the database environment for validation

  1. Connect to the ECS instance.

  2. Connect to the SQL Server database.

    1. Click the search icon in the lower-left corner and enter ssms.

    2. Click Microsoft SQL Server Management Studio 18.

    3. In the Connect to Server dialog box, configure the connection and click Connect.

  3. In Microsoft SQL Server Management Studio 18, create the validation database (AdventureWorks).

    1. Click New Query.

    2. Enter the following SQL statement in the query window.

      CREATE DATABASE AdventureWorks;

    3. Click Run.

  4. In Microsoft SQL Server Management Studio 18, create the validation table (PointInTime).

    1. Click New Query.

    2. Enter the following SQL statement in the query window.

      USE AdventureWorks
GO
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'PointInTime' AND TYPE ='U')
CREATE TABLE PointInTime (PIT datetime)
WHILE ( 1 = 1 )
BEGIN
    INSERT PointInTime SELECT GETDATE()
    WAITFOR DELAY '00:00:001'
END

    3. Right-click Run.

  5. Verify the created database and table in the left navigation pane.

Step 2: Create an application-consistent snapshot in the console

  1. Go to ECS console - snapshot consistency group.

  2. In the upper-left corner of the page, select a region and resource group.

  3. On the Snapshot-consistent Groups tab, click Create Snapshot-consistent Group.

  4. In the Create Snapshot dialog box, configure the snapshot consistency group.

    1. Resource Types is set to Instance by default.

    2. Select an instance and its ESSD-type disks.

    3. Expand Advanced Settings, select Enable Application-consistent Snapshot, and then select Include Default Writers.

      Note

      Writers are key components of the Volume Shadow Copy Service (VSS) architecture. The Windows operating system includes multiple writers that ensure data consistency during backup. See Volume Shadow Copy Service.

  5. Click OK.

    Cloud Assistant returns an execution ID that you can use to check the result.

Step 3: Verify that the application-consistent snapshot is created

  1. Click the Cloud Assistant command execution ID to view the result.

    
[level="info"][time="2025-03-03 13:53:03.4228545"][message="Begin to Create Application Consistent Snaps...
[level="info"][time="2025-03-03 13:53:03.6121396"][message="Network connection RequestId= A29F3C87-E45F-...
[level="info"][time="2025-03-03 13:53:03.6121396"][message="Attached DiskId=d-2ze6zrzyc4ampol4sv1, Devi...
[level="info"][time="2025-03-03 13:53:03.6121396"][message="Attached DiskId=d-2zecqmy4929tm5t6haay, Devi...
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Required COM+ components are already install...
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Provider version is already latest"]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Start to Initialize VSS Requestor ..."]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Prepare to Create snapshot set"]
[requestor="
"]
[level="info"][time="2025-03-03 13:53:30.9244374"][message="Create Snapshot Set Successfully"]
[level="info"][time="2025-03-03 13:53:31.7670453"][message="SnapshotGroup, requestId=E365F945-F0E9-57D4-...
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finished Quering SnapshotGroup Progress"]
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finish SnapshotGroup=ssg-2ze9bjzro7...
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Prepare to Thaw FileSystem or Applications"]
[requestor="
"]
[level="info"][time="2025-03-03 13:53:34.5692263"][message="Thaw Write Request Done"]
[level="info"][time="2025-03-03 13:53:34.8605098"][message="Tag snapshots with AppConsistent"]
[level="info"][time="2025-03-03 13:53:34.8620327"][message="Take AppConsistent snapshots successfully"]
[level="info"][time="2025-03-03 13:53:34.8620327"][message="Prepare Freeze=27.0672261s, Thaw cost=2.0040...
[level="info"][time="2025-03-03 13:53:34.8620327"][snapshotgroup="ssg-2ze9bjzro7rp"][message="Fi...
Add VolumePath=\\?\Volume{9a80a1cd-3ca9-4afc-xxx}, VolumeName=C:\
Add VolumePath=\\?\Volume{99583ec9-8979-4842-xxx}, VolumeName=D:\
Add VolumePath=\\?\Volume{02db53f7-a3f7-4be1-xxx}, VolumeName=
All writers included
Finish to GatherWriterMetadata successfully

    The Exit Code is 0, indicating the application-consistent snapshot command ran successfully. The output shows the snapshot and snapshot consistency group IDs.

    Note

    If the Exit Code is not 0, troubleshoot based on the Exit Code. See Error codes.

  2. In the Cloud Assistant output, review the snapshot consistency group creation process to verify data integrity and availability.

    • Check whether the consistency components (COM+ and VSS) are installed.

      The following similar log entries indicate that the application-consistent snapshot was created successfully.

      
[level="info"][time="2025-03-03 13:53:03.6121396"][message="Attached DiskId-d-2ze6z                xxx", Devi
[level="info"][time="2025-03-03 13:53:03.6121396"][message="Attached DiskId-d-2zecqmy4929tm5t6haay, Devi
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Required COM+ components are already install
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Provider version is already latest"]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Start to Initialize VSS Requestor ..."]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Prepare to Create snapshot set"]
[requestor="
        "]

[level="info"][time="2025-03-03 13:53:30.9244374"][message="Create Snapshot Set Successfully"]
[level="info"][time="2025-03-03 13:53:31.7670453"][message="SnapshotGroup, requestId-E365F945-F0E9-57D4-
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finished Quering SnapshotGroup Progress"]
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finish SnapshotGroup-ssg-2ze9bjz
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Prepare to Thaw FileSystem or Applications"]

    • Verify that the system can identify and load all logical volumes for backup.

      The Add VolumePath entries in the log show that C:\, D:\, and an unnamed volume have been added as snapshot volumes.

      
[level="info"][time="2025-03-03 13:53:34.5692263"][message="Thaw Write Request Done"]
[level="info"][time="2025-03-03 13:53:34.8605098"][message="Tag snapshots with AppConsistent"]
[level="info"][time="2025-03-03 13:53:34.8620327"][message="Take AppConsistent snapshots successfully"]
[level="info"][time="2025-03-03 13:53:34.8620327"][message="Prepare Freeze=27.0672261s, Thaw cost=2.0040xxx
[level="info"][time="2025-03-03 13:53:34.8620327"][snapshotgroup="ssg-2ze9bjzro7rxxx"][message="Fixxx
Add VolumePath=\\?\Volume{9a80a1cd-3ca9-4afc-b38d-11d8e7d74606}\, VolumeName=C:\
Add VolumePath=\\?\Volume{99583ec9-8979-4842-a45a-21da7d15dc1d}\, VolumeName=D:\
Add VolumePath=\\?\Volume{02db53f7-a3f7-4be1-92f3-8a7ca9324115}\, VolumeName=
All writers included
Finish to GatherWriterMetadata successfully

    • Check whether SQL Server databases are automatically included for consistency.

      The command output shows that SqlServerWriter has automatically included the master, model, msdb, and AdventureWorks databases under the SQLEXPRESS instance, confirming that the application-consistent snapshot was created successfully.

      
* Writer 'Performance Counters Writer':
    - Add component \PerformanceCounters
* Writer 'System Writer':
    - Add component \System Files
    - Add component \Win32 Services Files
* Writer 'SqlServerWriter':
    - Add component \iZpo1skdv7v082Z\SQLEXPRESS\master
    - Add component \iZpo1skdv7v082Z\SQLEXPRESS\model
    - Add component \iZpo1skdv7v082Z\SQLEXPRESS\msdb
    - Add component \iZpo1skdv7v082Z\SQLEXPRESS\AdventureWorks
* Writer 'ASR Writer':
    - Add component \ASR\ASR
    - Add component \Volumes\Volume{9a80a1cd-3ca9-4afc-xxx}
    - Add component \Volumes\Volume{02db53f7-a3f7-4be1-xxx}
    - Add component \Volumes\Volume{99583ec9-8979-4842-xxx}

    • The snapshot was created at 2025-03-03 13:53:30. Write operations were paused at this time.

      
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Provider version is already latest"]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Start to Initialize VSS Requestor ..."]
[level="info"][time="2025-03-03 13:53:05.1569259"][message="Prepare to Create snapshot set"]
[requestor=
"]
[level="info"][time="2025-03-03 13:53:30.9244374"][message="Create Snapshot Set Successfully"]
[level="info"][time="2025-03-03 13:53:31.7670453"][message="SnapshotGroup, requestId=E365F945-F0E9-57D4-xxx"]
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finished Quering SnapshotGroup Progress"]
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Finish SnapshotGroup=ssg-2ze9bjzro7rplr81xvfxxx"]
[level="info"][time="2025-03-03 13:53:32.5651781"][message="Prepare to Thaw FileSystem or Applications"]

  3. View the information about the snapshot consistency group and the disk snapshots.

    1. Go to ECS console - snapshot consistency group.

    2. On the Snapshot-consistent Groups tab, find the snapshot consistency group and click its ID.

    3. In the Snapshot Information section, check the snapshot tags to verify whether an application-consistent snapshot was created.

      In this example, the disk snapshot has the tag APPConsistent:True, confirming that an application-consistent snapshot was created.

  4. Connect to SQL Server to check when data commits were paused.

    1. Connect to the ECS instance.

      See Log on to a Windows instance using Workbench.

    2. Connect to SQL Server with Microsoft SQL Server Management Studio 18.

    3. Click New Query.

    4. Enter the following SQL statement in the query window.

      USE AdventureWorks
select * from PointInTime
GO

    5. Right-click Run.

    6. Check the pause time in the query results.

      The following query result shows the PIT records. No data was inserted between 2025-03-03 13:53:30 and 2025-03-03 13:53:33, indicating that write operations were paused at 13:53:30 and resumed at 13:53:33.

      
PIT
622  2025-03-03 13:53:23.843
623  2025-03-03 13:53:23.843
624  2025-03-03 13:53:24.860
624  2025-03-03 13:53:25.887
625  2025-03-03 13:53:26.893
626  2025-03-03 13:53:26.903
626  2025-03-03 13:53:27.907
627  2025-03-03 13:53:27.903
628  2025-03-03 13:53:29.913
629  2025-03-03 13:53:30.923
630  2025-03-03 13:53:33.613
631  2025-03-03 13:53:34.693
632  2025-03-03 13:53:35.703
633  2025-03-03 13:53:36.720
634  2025-03-03 13:53:37.733

Step 4: Verify the data restoration from the application-consistent snapshot

  1. Roll back the disk using the snapshot consistency group that you created.

  2. Log on to SQL Server again and query the PointInTime table.

    1. Connect to the ECS instance.

      See Log on to a Windows instance using Workbench.

    2. Connect to SQL Server with Microsoft SQL Server Management Studio 18.

    3. Click New Query.

    4. Enter the following SQL statement in the query window.

      USE AdventureWorks
select * from PointInTime
GO

    5. Click Run.

    6. Check the timestamp of the last record after restoration.

      The last inserted record has a timestamp of 2025-03-03 13:53:29, earlier than the pause timestamp 2025-03-03 13:53:30 from Step 3. This confirms a successful application-consistent snapshot backup.

Previous：Best practices for creating application-consistent snapshots for MySQL (Linux)Next：Roll back disk using group