Log shipping Manual Failover

The primary reason(s) you will want to Log shipping Manual Failover the primary database to the secondary database is when you need to:

  • Apply to the primary database with service packs
  • Correct a corrupt database
  • Replace hardware failure (disk is damaged)

Steps:

Find the last back up, copy and restore files

On primary:

Run the following script to find last backup made:

On secondary:

Run the following script to find last copy and restore made:

Use msdb
Go

Select secondary_server, secondary_database, last_copied_file, last_restored_date ,last_restored_file,*
From log_shipping_monitor_secondary

From log_shipping_monitor_secondary

  • run the last tail tlog backup of primary
  • run the copy and restore jobs on secondary
  • disable all three jobs
  • run tlog back up on primary with no recovery
BACKUP LOG [Test] 
TO  DISK = N'C:\s\Tail.trn' 
WITH 
NORECOVERY 
GO
  • copy paste the tail tlog backup from primary to secondary folder
  • restore secondary database with the tail backup with recovery mode
RESTORE LOG [Test] 
FROM  DISK = N'C:\d\Tail.trn' 
WITH  NORECOVERY 
GO
  • on secondary database, start the log shipping process, using the secondary share folder
  • set up initial primary as secondary using the primary share folder
  • notice no need to take a full back up as the initial primary has been initialized
  • notice that we have 3 new jobs recreated for the reverse log shipping process
  • delete the old jobs to avoid confusion
  • validate that the new failed over database is accessible
--ON PRIMARY SERVER

Use msdb
Go

Select primary_server, primary_database, last_backup_date, last_backup_file ,*
from log_shipping_monitor_primary 

--1. take a final tail log backup for any transactions not backed up if the transaction log numbers dont match

--2 diable all three jobs

--3. run the following tail log backup with no recovery on primary server

BACKUP LOG [Test] 
TO  DISK = N'C:\s\Tail.trn' 
WITH 
NORECOVERY --<< the norecovery mode will make the database in a restoring state
GO

--4. copy paste this last tail log from primary to secondary.  

--go to secondary server and exectue step 5

RESTORE LOG [Test] 
FROM  DISK = N'C:\d\Tail.trn' 
WITH  RECOVERY

-- ON SERVER SECONDARY

Use msdb
Go

Select secondary_server, secondary_database, last_copied_file, last_restored_date ,last_restored_file,*
From log_shipping_monitor_secondary

--5.
 Restore Log

RESTORE LOG [Test] 
FROM  DISK = N'C:\d\Tail.trn' 
WITH  RECOVERY


--6 start the log shipping process from start making this the primary, and the original primary the secondary

Also See:

SQL Server 2019

Change Roles Between Primary and Secondary