3节点AlwaysOn可用性组：能否从单个备份同时恢复两个节点？

Absolutely, this approach is completely feasible—you can absolutely run simultaneous RESTORE DATABASE ... WITH NORECOVERY operations against the same shared backup file from two separate SQL Server instances via SSMS query windows. Here’s a breakdown of why it works and key considerations to avoid issues:

• Backup file concurrency support: SQL Server backup files are designed to handle multiple concurrent read operations. Since restoring a database only reads the backup file (no writes to the backup itself), there’s no lock conflict between the two instances accessing the shared file.
• Critical permission checks: Make sure the SQL Server service accounts for both target instances have:
  • Read permissions on the shared folder (SMB share permissions)
  • Read permissions on the underlying NTFS filesystem where the backup file resides
    Missing permissions will throw access-denied errors mid-restore.
• Verify restore command accuracy: You must include the NORECOVERY parameter, and if the target instances don’t use the exact same file paths as the original database, use the MOVE clause to redirect data/log files to valid paths on each VM. Example command:

  RESTORE DATABASE [YourDatabaseName]
  FROM DISK = '\\SharedServer\BackupShare\Your400GBBackup.bak'
  WITH NORECOVERY,
  MOVE 'OriginalDataFile1' TO 'D:\SQLData\YourDatabaseName_Data1.mdf',
  MOVE 'OriginalDataFile2' TO 'D:\SQLData\YourDatabaseName_Data2.ndf',
  -- Repeat MOVE for all 8 data files
  MOVE 'OriginalLogFile' TO 'E:\SQLLogs\YourDatabaseName_Log.ldf';
  

• IO bandwidth considerations: Concurrent reads from the shared storage might slow down individual restore speeds compared to restoring from local storage, depending on your shared storage’s IO capacity. If you notice significant delays, you could copy the backup file to each VM’s local disk first, but this adds an extra step.
• Post-restore AlwaysOn prep: Once both restores complete with NORECOVERY, the databases will be in a state ready to be added as secondary replicas to your 3-node AlwaysOn Availability Group. Just ensure the database names match across all instances and you’ve restored all necessary subsequent backups (if any) to the secondary replicas with NORECOVERY.

内容的提问来源于stack exchange，提问作者James Jenkins