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