Roll Your Own SQL Log Shipping

Author:

Brian Dill

Created:

2003-12-19

Updated:

2003-12-20

Show Until:

2004-12-31

Parts taken from the excellent article How to Perform Log Shipping at http://www.sql-server-performance.com/sql_server_log_shipping.asp. I have trimmed down the article in order to make it a quicker read, however the other article has more detailed explanations. I have also update some of the syntax to SQL 2000. For example the DBO_ONLY option in the RESTORE statement has been replaced with RESTRICTED_USER in SQL 2000.

Luke is a domain account and is in the local admins group on both servers.

MSSqlServer and SqlAgent services are both running under STARWARS\Luke account.

A database named "TheForce" already exists on Degoba. It can be blank.

Degoba's TheForce.mdb is located in C:\SqlData, and TheForce_Log.ldf is in D:\SqlLog.

An easy way to remember the names is this: "Luke takes TheForce from Hoth to Degoba". Luke (the SQL and SQLAgent account) takes TheForce (the database) from Hoth (primary server) to Degoba (secondary server).

The Setup

1) Create Shares

Create a folder on Hoth. D:\SqlLogShip
Share it out as \\Hoth\SqlLogShip

Create a folder on Degoba. D:\SqlLogShip
Share it out as \\Degoba\SqlLog Ship

Step 2

Step 3

Step 4

Schedule this job to run as often as you want to log ship. The more often you log ship, the more up-to-date your
secondary server is, but it also adds extra burden on your primary server. It is basically a balance between maintaining
previous levels of performance on your primary server and accepting work loss exposure. You could log ship every
minute, but you would probably have users screaming at you because the database is slow all the time. I would suggest
log shipping every 15-30 minutes. You have to make the call whether you need to bump it down to 5 minutes or
up to 60 minutes.

Failover Steps

OK, the primary server failed. What do we do to get the secondary up and running like it is the primary?

Talk Back

Thanks and More - Mike (2004-10-14 09:10:49)Brian great explanation of log shipping.Clearer than the post you referred to .
Question.
How do you put the backup and moving scripts together so that there is no overlap mngong@yahoo.com

additions - MaxM (2004-06-05 17:17:17)I had some trouble with overwriting existing files, so I extended this a bit. I put in a new job step in the log restore to rename the log file after restore.
rename \\machine\share\DatabaseName_Log_Device.BAK DatabaseName_Log_%date:~10,4%-%date:~7,2%-%date:~4,2%.%time:~0,2%%time:~3,2%.BAK
and then in the backup job (which essentially makes the previous logs irrelevant), I move the last backup and all logs to a subdir, (deleting the previous contents of that dir) and start the process over again.
If you don't want to do this, you might just want to append /y to the xcopy command to force overwrite.