Amazon FSx for ONTAP: SQL Server Snapshot-Based Restore Automation Script

This PowerShell script will automate the T-SQL restore flow from the application-consistent backup created using snapshots on FSx for ONTAP filesystem and TSQL metadata backup. This will also restore transaction logs The script fetches the disks assigned to SQL instance/databases and maps that back to LUN and volume on FSx for ONTAP.

Script Location

Get the script here: TSQL_Restore.ps1

Pre-requisites:

Some of the pre-requisites are as follows:

Store the Amazon FSx credentials as an AWS Systems Manager Parameter Store parameter for secure storage and retrieval a. Create a SecureString parameter named /tsql/filesystem/<FSxN filesystem ID>. Replaced <FSxN filesystem ID> with Amazon FSxN ID.

b. Set its value to a JSON object with your FSx username and password. Example:
```
{
    fsx: {
        username: 'fsxadmin',
        password: 'password'
    }
}
```
Install AWS.Tools.SimpleSystemsManagement PowerShell module on the system where script is running.
The script expects the backups were taken using SQL Server Snapshot-Based Backup Automation Script. Expectation is that ONTAP snapshot and SQL metadata backup have the same naming to map.
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: Check for newer backups – Look for any backups that were created after the snapshot you want to restore. Delete those newer backups – Remove all backups that are more recent than your target snapshot. Why? FSxN won't allow you to restore a snapshot if there are newer snapshots linked to existing backups. Deleting them clears the way for a successful restore.

Usage:

To restore a single database that has failed to previous full state of volume snapshot

TSQL_Restore.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <database name> -isClustered <$True if cluster and $False if standalone> -snapshot <snapshot name>

Example for FCI:

TSQL_Restore.ps1 -FSxID fs-07a22f282fd4f5a20 -FSxRegion eu-south-2 -serverInstanceName 'ENGINEERING' -databaseName 'Payments' -isClustered $True -snapshot 'Payments_20250514111905'

Example for Standalone:

TSQL_Restore.ps1 -FSxID fs-07a22f282fd4f5a20 -FSxRegion eu-south-2 -serverInstanceName 'MSSQLSERVER' -databaseName 'Finance' -isClustered $False -snapshot 'Finance_20250524140920'

To restore database to a snapshot backup and all transaction logs available after that

TSQL_Restore.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <comma-separated database names> -isClustered <$True if cluster and $False if standalone> -snapshot <snapshot name> -transactionRestore <$True to restore transaction logs, $False or skip to exclude>

Example:

TSQL_Restore.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING' -databaseName 'Payments' -isClustered $True -snapshot 'Payments_20250521083504' -transactionRestore $True

To restore database to a previous snapshot backup and upto a specified transaction log backup

TSQL_Restore.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <comma-separated database names> -isClustered <$True if cluster and $False if standalone> -snapshot <snapshot name> -transactionRestore <$True to restore transaction logs, $False or skip to exclude> -tlogbackup_lastfile -transaction_date 

Example:

TSQL_Restore.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING' -databaseName 'Payments' -isClustered $True -snapshot 'Payments_20250521083504' -transactionRestore $True -tlogbackup_lastfile 'Payments_20250521091525.trn' -transaction_date '2025-05-21T09:10:31

To restore backup for a database and restore a point-in-time from transaction log backup

TSQL_Restore.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <comma-separated database names> -isClustered <$True if cluster and $False if standalone> -snapshot <snapshot name> -transactionRestore <$True to restore transaction logs, $False or skip to exclude> -tlogbackup_lastfile <transaction log> -transaction_date <transaction timestamp>

Example:

TSQL_Restore.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING' -databaseName 'Payments' -isClustered $True -snapshot 'Payments_20250521083504' -transactionRestore $True -tlogbackup_lastfile 'Payments_20250521091525.trn' -transaction_date '2025-05-21T09:10:31'