Log shipping Manual Failover

 

The primary reason(s) you will want to 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

 

1. run the last tail tlog backup of primary

2. run the copy and restore jobs on secondary

3. disable all three jobs

4. run tlog back up on primary with no recovery

5.  

• BACKUP LOG [Test]

• TO DISK = N'C:\s\Tail.trn'

• WITH

• NORECOVERY

• GO

 

 

1. copy paste the tail tlog backup from primary to secondary folder

2. restore secondary database with the tail backup with recovery mode

3.  

4. RESTORE LOG [Test]

5. FROM DISK = N'C:\d\Tail.trn'

• WITH NORECOVERY

1. GO

 

 

1. on secondary database, start the log shipping process, using the secondary share folder

2. set up initial primary as secondary using the primary share folder

3. notice no need to take a full back up as the initial primary has been initialized

4. notice that we have 3 new jobs recreated for the reverse log shipping process

5. delete the old jobs to avoid confusion

6. 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 [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