Failover Logship Primary Database to Secondary Database

Log shipping can be set up between one primary database and multiple secondary databases. The secondary databases can be in recovery mode, which is inaccessible, or in Standby mode, whose data is readonly for reporting purpose. Note that the data latency in the secondary server depends on the job (log backup, copy, and restore) schedules. Data changes that are made on the primary server after the log shipping is setup are invisible from the secondary servers. In standy mode, a standy file (.TUF, i.e.Transaction Undo File) must be specified for undo the rolled back transactions in the secondary server.

A Secondary server (Standy or Recovery mode) can become primary and response to clients if the primary database is failed.

Suppose the current primary database is TestDB On ServerA; The secodnary database is TestDB on ServerB.
The trnasaction log bacups are saved in D:\Logship on each server.
To fail over to the secondary server if SeverA and TestDB is still online

1) Backup the tail log on the ServerA:

To make server A in warm standy mode:
backup log TestDB to disk=‘D:\logship\Tail.trn’ with Standby=N‘d:\logship\ServerAStandby.TUF’

To make server A in recoverying mode:

backup log TestDB to disk=‘D:\logship\Tail.trn’ with NORECOVERY

Now the TestDB in ServerA is in Standy/ReaOnly or Restoring status and can not accept data change requests.
2) Copy all the transaction log and the tail log backup files to the ServerB if they are not in the //ServerB/Logship folder
3) Apply the transaction log backs to TestDB on ServerB
Upto the log shipping copy and restore job schedule setting, the previose log backup file before the tail log backup may or may not be applied. Apply all log back up files with NORECOVERY in sequence.


log TestDB FROM disk=‘d:\Logship\TestDB_20071112183001.trn’ with norecovery

4) Apply the tail log back up:


log TestDB FROM disk=‘d:\Logship\tail.trn’ with RECOVERY

Now the testDB on ServerB is online can accept all client requests
To make the new primary work properly, you may need to create the necessary logins, jobs, maintanance plans etc that are saved out of scope the TestDB and are used by your application.

If you need to do some maintance on ServerA, e.g. apply a service pack, you can do it now.
After you have finished the maintnace work on ServerA, or if you just want to switch the roles between the primay and secondary server, now you can setup the log shipping again on the ServerB and add ServerA as a secondary server.

If you want the fialover because the TestDB on ServerA is corrupted, you need to change the first step as following:


log TestDB to disk=‘D:\logship\Tail.trn’ with NO_TRUNCATE

The with NO_TRUNCATE option can backup log for a database that is damaged.


This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s