All scripts are at E:\SQL\SQLScripts on both servers. Run via SSMS or trigger the corresponding Agent job. Each script prints its progress to the messages window — read it.

Step

Server

Action (Run Agent Jobs)

What Happens

FAILOVER  (primary is down - promote HLA-CONTINGENCY)

1

HLA-CONTINGENCY

Run [LS] 01 - Failover

Drains queued logs, recovers databases, removes LS config. Databases come ONLINE.

This can be initiated by Techs on HLA-CONTINGENCY using the SQLBackupandFTP application.

2

Both

Update connection strings/ODBCs

Point applications to HLA-CONTINGENCY.

REVERSE LOG SHIPPING  (restore DR after failover) – After PCLINIMDB984 is back and stable

3

HLA-CONTINGENCY

Run [LS] 02A - Setup as New Primary

Takes seed + bridge backup. Starts backup jobs. HLA-CONTINGENCY is now primary.

4

PCLINIMDB984

Run [LS] 02B - Setup as New Secondary

Restores seed, applies bridge, starts copy/restore jobs. PCLINIMDB984 is standby.

5

PCLINIMDB984

Verify

min_since_restore should be 0-5 min within a few minutes.

FAILBACK  (return production to PCLINIMDB984 - planned window only ~5-10 minutes)

6

HLA-CONTINGENCY

Run [LS] 03A - Failback Step Down

Disables backup jobs. Sets databases SINGLE_USER to freeze activity.

7

PCLINIMDB984

Run [LS] 03B - Failback Recover immediately after 03A completes

Drains remaining files, restores WITH RECOVERY, sets MULTI_USER.

8

Both

Update connection strings/ODBCs

Point applications back to PCLINIMDB984.

RESTORE DR  (re-establish original log shipping direction)

9

PCLINIMDB984

Run [LS] 04A - Restore DR Primary

Takes seed + bridge backup. PCLINIMDB984 is primary again.

10

HLA-CONTINGENCY

Run [LS] 04B - Restore DR Secondary

Restores seed, applies bridge, starts copy/restore. System back to normal.

11

HLA-CONTINGENCY

Verify

Both databases should show (Restoring...) in SSMS.

Normal 5-minute latency is expected and healthy. Only investigate if min_since_restore exceeds 15 minutes consistently.