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: