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.

Assumptions

• You know what log shipping is. If not go here.
• Database name: TheForce
• Database recovery model: Full (default) or Bulk-logged
• NT domain: StarWars
• NT domain account: Luke
• Primary Server name: Hoth
• Secondary Server name: Degoba
• 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

Be sure that STARWARS\Luke can read/write to these shares.

2) Create backup devices on the primary server

USE master
EXEC sp_addumpdevice 'disk', 'TheForce_backup_device', 'D:\SqlLogShip\TheForce_backup_device.bak'
EXEC sp_addumpdevice 'disk', 'TheForce_log_backup_device', 'D:\SqlLogShip\TheForce_log_backup_device.bak'

3) Manually create a linked server from the primary to the secondary server using EM.

• Type the name of the desired server
• Set server type to "SQL Server"
• On the security tab, add a row to impersonate StarWars\Luke
• On the server options tab, enable RPC, and RPC out

4) Create SP's on secondary server to restore db and logs

These SP's will be on the secondary server(s) and called remotely from the primary server.

The adm_kill_users kills all user processes on the secondary server so that the restore process won't get blocked out. The restore process needs excluseve access when restoring.

CREATE PROCEDURE adm_kill_users
@dbname SYSNAME
AS
-- Close all user connections to specified database.

--DECLARE @dbname SYSNAME                -- Name of database to shut down (Required)
--SELECT @dbname = 'TheForce'
BEGIN
  DECLARE @killcmd nvarchar(128)
  DECLARE @spid int
  DECLARE killcursor CURSOR FOR
      SELECT DISTINCT spid
      FROM master.dbo.sysprocesses
      WHERE dbid IN (SELECT dbid from master.dbo.sysdatabases WHERE name = @dbname) and spid >= 10
      -- Only select SPID > 10 to avoid killing any system processes

  OPEN killcursor
  FETCH killcursor into @spid
  WHILE @@FETCH_STATUS <> -1
    BEGIN
      SET @killcmd = 'kill ' + convert(varchar(10),@spid)
      PRINT @killcmd
      EXECUTE sp_executesql @killcmd
      FETCH killcursor into @spid
    END
  CLOSE killcursor
  DEALLOCATE killcursor
END

This SP does a full restore of the full backup. It will only be called once a day.

CREATE PROCEDURE adm_restore_database_backups

AS

RESTORE DATABASE TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_backup_device.bak'
WITH
RESTRICTED_USER,
REPLACE,
STANDBY = 'D:\SqlLogShip\undo_TheForce.ldf',
MOVE 'TheForce_data' TO 'C:\SqlData\TheForce.mdf',
MOVE 'TheForce_Log' TO 'D:\SqlLog\TheForce_log.ldf'

WAITFOR DELAY '00:00:05'
--EXEC sp_dboption 'TheForce', 'single user', true

This SP restores the logs that are backed up. This will run as often as you want to "log ship" the logs from the primary server.

CREATE PROCEDURE adm_restore_log_backups

AS

RESTORE LOG TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_log_backup_device.bak'
WITH
RESTRICTED_USER,
STANDBY = 'D:\SqlLogShip\undo_TheForce.ldf'

WAITFOR DELAY '00:00:05'

--EXEC sp_dboption 'TheForce', 'single user', true

5) Database backup Job

Step 1

Go ahead and truncate the log since we are about to do a full backup anyway

BACKUP LOG TheForce WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'

Step 2

"WITH INIT" forces restore over previous database.

BACKUP DATABASE TheForce TO TheForce_backup_device WITH INIT
WAITFOR DELAY '00:00:05'

Step 3

Copy from primary to secondary server. The "/Y" forces overwrite.

xcopy D:\SqlLogShip\TheForce_backup_device   \\Hoth\SqlLogShip\ /Y

Step 4

EXEC Hoth.master.dbo.adm_kill_users 'TheForce'
EXEC Hoth.master.dbo.adm_restore_database_backups

Step 5

Schedule the job to run once a day.

6) Log backup job

Step 1

"WITH INIT" forces restore over previous log backups.

BACKUP LOG TheForce TO TheForce_log_backup_device WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'

Step 2

xcopy D:\SqlLogShip\TheForce_log_backup_device.bak   \\Hoth\SqlLogShip\ /Y

Step 3

EXEC Hoth.master.dbo.adm_kill_users 'TheForce'
EXEC Hoth.master.dbo.adm_restore_log_backups

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?

Try to backup the log on the failed primary server.

BACKUP LOG TheForce TO TheForce_log_backup_device WITH INIT, NO_TRUNCATE

If you were able to backup, restore on secondary server.

RESTORE LOG TheForce
FROM DISK = 'D:\SqlLogShip\TheForce_log_backup_device.bak'
WITH
RESTRICTED_USER,
STANDBY = 'D:\SqlLogShip\undo_TheForce_log.ldf'

Finally, recover the secondary db, enabling it to act as primiary.

RESTORE DATABASE TheForce WITH RECOVERY
EXEC SP_DBOPTION 'TheForce', 'read only', 'false'
EXEC SP_DBOPTION 'TheForce', 'dbo use only', 'false'

Repoint your client applications to the secondary SQL server.