How to setup MS SQL server logshipping and failover

Jephe Wu - http://linuxtechres.blogspot.com

Objective: setup a logshipping between primary and secondary. and failover it.
Environment: sql server 2005/2008.


Steps:


Setup requirments:
1. Installing both primary and standby SQL servers, configure primary database first
2. setup shared folder on primary or another network share(third server)

You must have a shared folder to copy the transaction log backups to.
The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read access to the shared folder for applying transaction log
3. primary server must be in full or bulk-loged mode for generating transaction logs

You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the agent is started.

Note: refer to step-by-step configuring logshipping at
http://www.mssqltips.com/tipprint.asp?tip=2301

How to failover - http://msdn.microsoft.com/en-us/library/ms191233.aspx
1. copy any uncopied backup files from backup share to file copy folder on each secondary server

You might want to generate tail log backup from primary if primary server is still online and running.

a. if primary db is online and you plan to perform transaction log apply for secondary server, before starting that, consider to  backup tail of the primary server transaction log with command below, so that you will have the latest data from primary db:

BACKUP LOG <dbname> TO <backup_device> WITH NORECOVERY;
go

note: This leaves the primary database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the original secondary server.

b. if db is offline and cannot start.
As no transaction can occur this time, using 'with norecovery' is optional, if db is damaged, use 'with continue_after_error'

as follows:

backup log <dbname> to <backup_device> with continue_after_error

Use CONTINUE_AFTER_ERROR only if you are backing up the tail log for a damaged database.

c. if db is damaged and inaccessible, however, transaction log is undamaged and accessbible:

backup log <dbname> to <backup_device> with no_truncate;
go

note:
a.no_truncate=copy_only+continue_after_error
b. How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/dd297499
How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms189606
c. For details about tail-log backup, see http://msdn.microsoft.com/en-us/library/ms179314

2. apply any unapplied transaction log backups in sequence to each secondary database
How to: Recover a Database from a Backup Without Restoring Data (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms176039.aspx
How to: Restore a Transaction Log Backup (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/ms177446.aspx

If you are  restoring the last log backup, you can do the following:
restore log <dbname> from <backup_device> with recovery;  bring database online from Read-only mode.
go

otherwise, do
restore log <dbname> from <backup_device> with norecovery;
restore database <dbname> with recovery; bring database online from Read-only mode.
go

note:
a.always explicitly specify either with norecovery or with recovery.
b. <backup_device> is the name of the device that contains the log backup being restored.

How to recover a database from a backup without restoring data - http://msdn.microsoft.com/en-us/library/ms176039.aspx
restore database <dbname> with recovery

3. after you have recovered secondary db, you can reconfigure it to act as a primary database for other secondary db, and you can redirect client to this server instance.

Before this, you might need to transfer login ids from primary to seconday server:
How to set up and perform a log shipping role change (Transact-SQL) - http://msdn.microsoft.com/en-us/library/aa215392(v=sql.80).aspx



PS: how to restore ms sql server from the previous full backup plus transaction logs, other than the most recent backup:

-----------
# make if offline then online first before restore
use master
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE test SET ONLINE  WITH ROLLBACK IMMEDIATE

# restore full backup and all transaction logs, after x.bak, actually, we have done another full bakcup x2.bak, but we lost that, let's skip it.

restore database test from disk='i:\download\x.bak' with norecovery
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162700_8205296.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162704_9113476.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162737_0047650.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162801_1445920.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162830_7010021.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162900_8320271.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162950_0283644.trn' WITH NORECOVERY

RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_163001_1705924.trn' WITH RECOVERY
---------------------

References and Commands:
a. alter database <dbname> set recovery full
b. how to restore database with norecovery
restore database <dbname> from <device_name> with norecovery;
go
c. how to apply transaction log backup (T-SQL) - http://msdn.microsoft.com/en-us/library/ms187607.aspx
d. High Availability Solutions Overview -  http://msdn.microsoft.com/en-us/library/ms190202%28v=SQL.105%29.aspx 
e.  SQL 2005 logshipping tutorial video - http://www.dbsnaps.com/category/sql-server/high-availability-sql-server/