DR Drill Failover & Failback Tutorial
Overview.
This topic introduces the SQL Server Disaster Recovery
(DR) Plan, In Organization is at exceptional risk of loss of business, hacking,
Cyber-attacks, loss of confidential data and more. Your DR plan can prolong
your business continuity potentials until the disaster has been appropriately
handle, Its doing this, you can keep your customers satisfied,
Failover and Failback in Log Shipping (MSSQL Server )
Database state in Primary server
Database state in Secondary server:
Log_shipping jobs on Primary and Secondary server
Log_shipping jobs on Primary :- LsBackup_AdventureWorks2008
Log Shipping jobs on Secondary Server:
LsCopy_adventureWorks2008
LsRestore_AdventureWorks2008
Check the Log Shipping is in sync by executing following
query
select
secondary_database,last_copied_file,last_restored_file from
msdb..log_shipping_monitor_secondary
You can generate the log shipping sync report on Secondary
Server by
Right click on Instance Name > Reports > Standard
Reports > Transaction Log shipping status
Failover
Before disable Log_shipping jobs please copy any uncopied files from the shared folder
on Primary server to the shared destination folder on secondary server by using
copy jobs
Check all the
transaction log backup has been successfully applied on the secondary server
,if not then please re-run restore jobs to apply unapplied transaction log
backup on secondary server
Disable Log_shipping
jobs
Disable log shipping
jobs on Primary server
Disable Log Shipping jobs on secondary server
T-Log Backup
Kill the session open for the database server on which
you have to take trail log backup
The following example create log backup to maintain
log chain
Select Backup tail log and leave database in restoring state
option.
·
DB on primary Server will go in
restoring state.
Using
SQL server Script take Tail log backup
Backup database Database Name to
disk=”Path ” with norecovery
Restore T-Log Backup:
·
Now restore the tlog backup on Secondary Server.
ss
·
The database on secondary server will
come in online state.
Using
script restore Tail log Backup on secondary server
Restore
database (Database Name) from disk=’path of tail log backup’ with recovery
