Restore SQL Server databases using T-SQL and Amazon FSx for NetApp ONTAP Snapshots

In modern database environments, the ability to quickly restore databases is just as critical as creating reliable backups. An 11-hour database restore window is no longer acceptable in today’s enterprise workloads. Here’s how to cut that to under 10 minutes.

In our previous blog, we discussed how to create application consistent snapshot backups for Microsoft SQL Server databases using T-SQL and Amazon FSx for NetApp ONTAP (FSxN).

In this post you will learn how to restore SQL Server databases from application-consistent backups using T-SQL commands and FSxN snapshots. This approach reduces database restore times and simplifies the restore process using FSxN snapshots.

Prerequisites

1. Deploy highly available SQL Server 2022 on Amazon Elastic Compute Cloud (Amazon EC2) with FSxN referring to the guidelines on this blog.
2. Create Application consistent T-SQL Snapshot backups using this blog.
3. SSH connect using PowerShell7.

Solution Overview

This solution combines SQL Server’s native restore capabilities with FSxN snapshots to provide fast, consistent database restores. The following architecture in Figure 1 shows the key components and their interactions.

Step-by-Step Workflow

The flowchart below in figure 2 outlines the complete process for restoring T-SQL snapshot backups.

Figure2 Workflow diagram

Walkthrough

Connect to FSxN

In this blog, we use PowerShell 7 to connect to FSxN and execute ONTAP CLI commands.The DNS name or management IP address for accessing FSxN is available in the AWS Console after selecting the relevant file system (see Figure 3).

Figure 3 Connection Info for FSxN

As shown in Figure 4, establish a connection to the file system using PowerShell 7 with ‘fsxadmin’ credentials to execute the necessary ONTAP commands.

Figure 4 Connect to FSxN Filesystem using Powershell 7

Database Restore Scenarios

You can use this solution for several common restore scenarios:

1. Data Corruption Recovery: Restore from the last known good backup when data becomes corrupted.
2. Development and Testing: Create database copies for non-production environments
3. Database Migration: During the process of migrating data from one server to another, restore backup to the new servers.
4. Compliance Requirements: Support audit and compliance verification.
5. Accidental Deletion Recovery: If critical data is accidentally deleted, restoring from a backup can recover the lost information.

In this blog we focus on use cases 1 and 5 where you can use the volume snapshot restore feature to restore the specific database on the same EC2 instance. In other cases, 2,3 and 4 you can create a new database from the snapshot using flexclone feature.

In this blog we focus on two primary restore approaches:

6. Full Database Restore using full metadata backup and FSxN snapshot
7. Point-in-Time Recovery (PITR) using full metadata backup, FSxN snapshot and Transaction log backups

Restore SQL Server Database

Before starting the restore process, you need to identify available backups. Here’s how:

1. Connect to your SQL Server instance using SSMS
2. Run this T-SQL query to list available backups:

SET NOCOUNT ON;
SELECT bs.database_name, bs.backup_finish_date, bm.physical_device_name 
FROM msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
WHERE bs.database_name = 'testdb1';

The query returns list of available backups, as shown in Figure 5.

Figure 5 Query results showing available backups for the database

For this tutorial, we will demonstrate 2 types of restore:

1. Full backup restore to snapshot and metadata backup taken at ‘2025-08-22T13:18:19
2. Point in time restore (PITR) to the point in time ‘2025-08-22T13:32:36’.

You’ll need:
For full backup restore:

• FSxN snapshot: testdb1_20250822131819
• Metadata backup file: D:\MSSQL\Backup\testdb1_20250822131819.bkm

For PITR restore:

• FSxN snapshot: testdb1_20250822131819
• Metadata backup file: D:\MSSQL\Backup\testdb1_20250822131819.bkm
• Transaction log files:
  • D:\mssql\backup\testdb1_tran20250822132310.trn
  • D:\mssql\backup\testdb1_tran20250822132442.trn
  • D:\mssql\backup\testdb1_tran20250822132942.trn
  • D:\mssql\backup\testdb1_tran20250822133442.trn

Database Restore Process

1. Connect to SQL Server EC2 instance using SSMS and execute the following T-SQL to take database offline

ALTER DATABASE [testdb1]SET OFFLINE WITH ROLLBACK IMMEDIATE

2. As the SQL Server is configured in Always on Failover cluster, remove cluster dependencies by running following commands in PowerShell as shown in Figure 6.

Remove-ClusterResourceDependency -Resource "SQL Server" -Provider "testdb1-Data" 
Remove-ClusterResourceDependency -Resource "SQL Server" -Provider "testdb1-Log"

Figure 6 Remove SQL dependency for Cluster disks

3. The next step is to unmap the LUNs by running the following ONTAP commands after connecting to FSxN, as shown in Figure 3.

lun mapping delete -vserver SQL_Cluster -path /vol/testdb1_Data/data -igroup SQL_igroup
lun mapping delete -vserver SQL_Cluster -path /vol/testdb1_Log/log -igroup SQL_igroup

4. After unmapping the LUNs, restore data and log volumes from snapshot. Run the following ONTAP commands after connecting to FSxN. Accept the prompts as shown in Figure 7 and Figure 8.

volume snapshot restore -vserver SQL_Cluster -volume testdb1_Data -snapshot testdb1_20250822131819
volume snapshot restore -vserver SQL_Cluster -volume testdb1_Log -snapshot testdb1_20250822131819

Figure 7 Data Volume snapshot restore

Figure 8 Log Volume snapshot restore

Note 1: For shared database volumes with each database on independent LUNs, use file-level restore commands.

volume snapshot restore-file -vserver SQL_Cluster -volume testdb1_Data -snapshot testdb1_20250822131819 -path /vol/testdb1_Data/data 
volume snapshot restore-file -vserver SQL_Cluster -volume testdb1_Log -snapshot testdb1_20250822131819 -path /vol/testdb1_Log/log

Note 2: Alternatively if you are using ONTAP consistency groups run the following ONTAP commands to restore the group containing your data and log volumes.

vserver consistency-group snapshot restore -vserver SQL_Cluster -consistency-group testgroup -snapshot testdb1_20250822131819

1. Remap the LUNs back to the initiator group (igroup) by executing the following commands after connecting to FSxN, as shown in Figure 3.

lun mapping create -vserver SQL_Cluster -path /vol/testdb1_Data/data -igroup SQL_igroup
lun mapping create -vserver SQL_Cluster -path /vol/testdb1_Log/log -igroup SQL_igroup

• 1. Check if the LUNs are online after restore

lun show -vserver SQL_Cluster -path /vol/testdb1_Data/data -fields statelun show -vserver SQL_Cluster -path /vol/testdb1_Data/log -fields state

• 1. If the LUNs are not online, bring them online by running

lun modify -vserver SQL_Cluster -path /vol/testdb1_Data/data -state onlinelun modify -vserver SQL_Cluster -path /vol/testdb1_Data/log -state online

1. Rescan the disks on Windows so that it discovers the restored disk. Run below command in PowerShell, as shown in Figure 9.

echo "RESCAN" | diskpart

Figure 9 Rescan the disks for changes

2. Bring the cluster resources online and restore SQL Server dependencies. Run the following PowerShell commands as shown in Figure 10.

Start-ClusterResource -Name 'testdb1-Data'Start-ClusterResource -Name 'testdb1-Log'
Add-ClusterResourceDependency -Resource "SQL Server" -Provider "testdb1-Data'
Add-ClusterResourceDependency -Resource "SQL Server" -Provider "testdb1-Log"

Figure 10 Add Cluster disks to SQL Server dependency

3. Restore database by choosing one of these restore options:

Option 1: Restore Full backup

• 1. For a simple restore without transaction logs

RESTORE DATABASE [testdb1] FROM DISK=’D:\mssql\backup\ testdb1_20250822131819.bkm’ WITH REPLACE,METADATA_ONLY;

Option 2: Restore Full backup and transactional logs to achieve PITR

• • 1. Restore from the last metadata backup with NORECOVERY

RESTORE DATABASE [testdb1] FROM DISK='D:\mssql\backup\ testdb1_20250822131819.bkm' WITH METADATA_ONLY, NORECOVERY

ii.Restore transaction logs in sequence before the last intended transaction log backup with NORECOVERY.

RESTORE LOG [testdb1] FROM  DISK = N'D:\mssql\backup\testdb1_tran20250822132310.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5, NORECOVERY

RESTORE LOG [testdb1] FROM  DISK = N'D:\mssql\backup\testdb1_tran20250822132442.trn’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5, NORECOVERY

RESTORE LOG [testdb1] FROM  DISK = N' D:\mssql\backup\testdb1_tran20250822132942.trn’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5, NORECOVERY

iii.Restore final transactional log backup to the desired timestamp to achieve PITR.

RESTORE LOG [testdb1] FROM  DISK = N' D:\mssql\backup\testdb1_tran20250822133442.trn’ WITH  FILE = 1,  NOUNLOAD,  STATS = 5,  STOPAT = N'2025-08-22T13:32:36'

8. Bring Database online

ALTER DATABASE [testdb1] SET ONLINE;

Note: optionally, you can run integrity check of the database

DBCC CHECKDB(testdb1) WITH NO_INFOMSGS, ALL_ERRORMSGS;

If you would like to automate the restore solution you can use this PowerShell script. In addition, for dependent backup solution you can use this PowerShell script. Both these need to be used in conjunction.

Important considerations

1. Though the steps in this document considers separate data and log volume for database as best practise, it works the same if you have both data and log files shared in the same volume.
2. FSxN snapshot restore process is quick regardless of the volume size or activity of the Amazon FSx for ONTAP filesystem.
3. If multiple databases are sharing same volume without being segregated in to separate LUNs, restore operation will create downtime for all the databases sharing the same LUN. All databases would be restored to old point in time when the volume snapshot was created. It is always recommended to separate the production databases into independent LUNs/disks.
4. If you have automatic backups enabled or have manually created backups from the AWS FSx for ONTAP console, here’s what you need to do first: Look for any backups that were created after the snapshot you want to restore. Remove all backups that are more recent than your target snapshot. FSxN won’t allow you to restore a snapshot if there are newer snapshots linked to existing backups. Removing these later snapshots first ensures the restore completes successfully without conflicts.

Performance Metrics

Test Environment

Our benchmarks were performed on the following configuration:

• SQL Server: 2022 Standard Edition (Windows Server 2019 FCI)
• EC2 Instance: r6in.4xlarge (16 vCPUs, 128 GiB RAM)
• Database: 5 TB TPCC database loaded with HammerDB
• Storage: Amazon FSxN (Multi-AZ) with 15,360 GiB SSD storage
• Performance: 256 MB/s throughput, 46,080 provisioned IOPS

Clean Up

It is a best practice to delete resources that you are no longer using so that you do not incur unintended charges. You can clean up the following resources you created for this tutorial:

• Amazon EC2 Instances
• Amazon FSx for NetApp ONTAP

Conclusion

In this post, we showed how to restore SQL Server databases using Amazon FSx for NetApp ONTAP snapshots reducing restore times compared to traditional methods. For broader protection and backup policy management across SQL Server versions, consider using NetApp SnapCenter with FSx for NetApp ONTAP. Additionally, refer to Transact-SQL snapshot backup for more details and limitations of the T-SQL based backup mechanism.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact AWS to start your migration journey today.