LOG Shipping Failover (Disaster Recovery) steps
Try to backup the Tail end of the transaction log in primary server with NORECOVERY option i.e., perform a transaction log backup if the primary is still accessible.
Backup log DBName to disk = “Local path or Network path” with NORECOVERY —> A
Note: Verify the last backup file has been taken, use below T-SQL to get the backups info.
Verify the backups with LSN numbers
SELECT * FROM BACKUPSET
Else execute the below T-SQL in secondary server to bring the secondary online. verify the last and the latest
LOG backup file has been restored. Verify with LSN number which is baced up.
Restore database DBName with Recovery —> C
SELECT * FROM RESTOREHISTORY
If you were able to perform —> A in step 1 then proceed with —> B in step 2 to bring the secondary db
online from read-only state.
If you were able to perform only —> C in step1 then go to step 3
Restore log DBName from disk = “Local path or Network path” with RECOVERY —> B
The syslogins and sysusers table in primary and secondary server should be in sync otherwise
the DB users and other application users from primary will not be able to login into SQL server or
into the DB in secondary server after failover occurs.
Two ways to accomplish the above task:
Fixing Orphaned Users and Logins which are not in sync with production (primary):
Create the highly critical appplication users logins in the secondary server similar to primary
just before configuring log shipping.Use the below sps to resolve orphaned users
Finding Orphaned users:
USE <DB Name>
Fixing Orphaned users:
USE <DB Name>
sp_change_users_login @Action=’update_one’, @UserNamePattern=”, @LoginName=”
Backup the master.. syslogins table to a text file.This file will be used to synchronize syslogins to sysusers
in secondary when failover occurs.This can be done as following in the command prompt,
C:\any path > bcp master . . syslogins out C:\any path\syslogins.dat -N -S servername -T
You can schedule it as a SQL Job to run @specific schedule preferable every 1 hr.
You can make use of the sp_resolve_logins to resolve the old primary server logins on the new primary server.
sp_resolve_logins [ @dest_db = ] ‘dest_db’
, [ @dest_path = ] ‘dest_path’
, [ @filename = ] ‘filename’
Note: Refer BOL for each parameter.
Also Disable the log shipping jobs in the primary and secondary servers,once failover occurs.
Once the failover occurs the original secondary server is configured as primary and log shipping is again newly configured from this new primary server (original secondary) to original primary (now secondary).
When you once again want to revert to the original state of log shipping i.e original primary was primary and original secondary was secondary, you need take a full backup in new primary server (original secondary) and restore it in original primary and reconfigure the log shipping from the original primary to original secondary.
Role Change in Transactional Log Shipping , SQL Server 2005
Copy any uncopied backup files from the backup share of original primary server to the
copy destination folder of original secondary server.
Apply any unapplied transaction log backups which were copied in step1 sequentially in the secondary database.
If the original primary server instance is not damaged and if it is still accessible, back up the tail of the transaction log of the primary database using WITH NORECOVERY.
This leaves the database in the restoring state, and eventually you will be able to roll
this database forward by applying transaction log backups from the replacement
primary database(i.e original secondary server)
After the secondary servers are synchronized, you can fail over to the secondary by recovering its secondary database and redirecting clients to that server instance. Recovering puts the database into a consistent state and brings it online. Execute the below command
–To bring the original secondary db online with the last tran log copied into secondary server
Restore log DBName from disk=’Path of the last tran log copied into secondary’ with Recovery
if Step 3 is not possible i.e if you cannot access the original primary server just execute,
–To bring the original secondary db online if no transactional logs available
Restore database DBName with Recovery
The above command will bring the original secondary online from Read-only mode.
Now you original secondary has become primary server, now you can repoint your applications to this server. After sometime if your original primary server is up and running fine, you can see that the Original primary db will be in restoring state (becoz of Step 3) and hence transactional logs can be applied from the new primary.
Disable the log shipping backup job on the original primary server, and the copy and restore jobs
on the original secondary server.
On your original secondary (the new primary), reconfigure log shipping by including the below steps
Use the same share for creating backups that you created for the original primary server.
When adding the secondary database, in the Secondary Database Settings dialog box, enter the name of the
original primary database in the Secondary database box.
In the Secondary Database Settings dialog box, select No, the secondary database is initialized because
it is already in restoring state and so tran logs can be applied.
Enable the log shipping backup job on the secondary server (the new primary server),
and the copy and restore jobs on the primary server (the new secondary server).